Joining a flat table on EAV

How to use the Mage_Eav_Model_Entity_Collection_Abstract::joinTable() method to join a flat table on a magento EAV collection

Joining a "normal" (aka flat table) to a magento EAV table is easy - if you know how to do.

As always there are a lot resources with tipps I don't like about joining Tables, because they all use getSelect()->join() and fall back to Zend_Db to join the tables. This can (but don't have to) lead to a few problems. In my past I got for example problems with getSize(), because the changes are only made to the Select-Statement not the Count-Statement.

joinTable() TL;DR

But magento collections have a method joinTable() it took me 45min to fizzle out how it is used. To avoid this for you, I share it.

$productCollection->joinTable(
    array('bonus' => 'mycompany/bonus'), 'product_id=entity_id',
    array('bonus_id' => 'bonus_id')
);

The parameters are:

public function joinTable($table, $bind, $fields = null, $cond = null, $joinType = 'inner')
  1. Table is easy, it is the magento namespace/entity format, which you use in your configuration, resource models and the collection. You can use an array of the format array('alias' => 'namespace/entity')
  2. Bind means, the ON statement in your SQL. This was the hardest part and I will explain it in details later. It is important to have your field of the flat table BEFORE and your attribute of the EAV table AFTER the equal sign. Don't use main_table. before the attribute. Magento will do this for you. More on this later.
  3. Fields is an array. If you use a string instead, you get this: Warning: Invalid argument supplied for foreach() in /var/www/magento-1.6.2.0/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php on line 775. You can use an array of the format array('field1', 'field2', '...') or array('alias' => 'field1', '...')
  4. Condition is a * WHERE ON condition* in the SQL.
  5. joinType. I hope you know what it is. But you have only the choice between LEFT and INNER. app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:793

more details

The method can be found here:

app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:754

check the tablename and alias

First, the array of the tablename is splitted into alias and tablename, the tablename is resolved and the alias is checked for existance.

$tableAlias = null;
if (is_array($table)) {
    list($tableAlias, $tableName) = each($table);
} else {
    $tableName = $table;
}

// validate table
if (strpos($tableName, '/') !== false) {
    $tableName = Mage::getSingleton('core/resource')->getTableName($tableName);
}
if (empty($tableAlias)) {
    $tableAlias = $tableName;
}

check the fields

Then the fields are checked. Are they already defined? If not, add them.

// validate fields and aliases
if (!$fields) {
    throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Invalid joint fields'));
}
foreach ($fields as $alias=>$field) {
    if (isset($this->_joinFields[$alias])) {
        throw Mage::exception(
            'Mage_Eav',
            Mage::helper('eav')->__('A joint field with this alias (%s) is already declared', $alias)
        );
    }
    $this->_joinFields[$alias] = array(
        'table' => $tableAlias,
        'field' => $field,
    );
}

the hard part, check the bind

The bind is exploded at the =, and the foreign key is expected after the equality sign. It took me 25min to find this. I didn't think about this. I thought it will be took directly into the SQL, but no, magento processes it a lot. If the column of your flat table is the second "argument", magento can't find the attribute and throws an exception: Invalid attribute name: product_id

I started with 'main_table.entity_id=bonus.product_id' and ended with 'product_id=entity_id' and here is the code:

// validate bind
list($pk, $fk) = explode('=', $bind);
$bindCond = $tableAlias . '.' . $pk . '=' . $this->_getAttributeFieldName($fk);

Then the join method is choosed and the conditions are added. Interessting to see here is, that the whole condition is put into the ON statement. To be honest, I don't know the difference between ON and WHERE, but this looks like ON is processed on the table and WHERE is processed on the result?

And the second gem, I found here is str_replace('{{table}}', $tableAlias, $cond)., so you can use {{table}} if your condition is a string instead of the alias.

// process join type
switch ($joinType) {
    case 'left':
        $joinMethod = 'joinLeft';
        break;

    default:
        $joinMethod = 'join';
}
$condArr = array($bindCond);

// add where condition if needed
if ($cond !== null) {
    if (is_array($cond)) {
        foreach ($cond as $k => $v) {
            $condArr[] = $this->_getConditionSql($tableAlias.'.'.$k, $v);
        }
    } else {
        $condArr[] = str_replace('{{table}}', $tableAlias, $cond);
    }
}
$cond = '('.implode(') AND (', $condArr).')';

// join table
$this->getSelect()->$joinMethod(array($tableAlias => $tableName), $cond, $fields);

return $this;

The end

And in the end, magento uses getSelect->join() wonderful :-) BUT magento makes a lot of checks for you and using this method feels better.

Remove Values From Attribute

Today I had to change the values of a magento product attribute.

Attribute before

I had an attribute color with the values:

  • aqua
  • black
  • blue
  • fuchsia
  • ... (all other CSS2.1 color names)

And I had to change it to german values.

So I had a look into $installer->addAttribute() the method checks wether the attribute exists or not and calles $this->updateAttribute().

Great, let's just update the Attribute

I updated the attribute, the result was:

  • aqua
  • black
  • braun (german: brown)
  • blau (german: blue)
  • blue
  • ...

That is wrong.

Just remove the wrong values

I didn't find a way to remove the values from an attribute. If you have one, tell me!

Solution

The easiest and nicest way I found was:

$installer->removeAttribute(Mage_Catalog_Model_Product::ENTITY, 'color');
// [...]
$installer->addAttribute(Mage_Catalog_Model_Product::ENTITY, 'color', $data);

Remove the attribute and create it with the new values.

Bad thing about it? All values will be deleted for all products!

EAV attributes, default values and source models

EAV Attribute

I tried to add an EAV attribut, set a default value and that without using a source model, set this options:

$this->addAttribute('customer', 'customer_type', array(
    'type' => 'int'     
    'option' => array('values' => array('Customer', 'Artist'))
    'default' => 'Customer',
    [...]
);

Default value

Magento adds the values into the eav_attribute_option_value table and shows them in the backend. The problem is, if we save them as int, we can't set 'Customer' as default. And we can't set the eav_attribute_option_value.value_id, because we don't know it.

Solution

I removed the options from the attribute and added a soure model.

$this->addAttribute('customer', 'customer_type', array(
    'type' => 'int'     
    'source' => 'company_extension/entity_attribute_source_modelName',
    'default' => 1,
       [...]
);

A source model needs to implement the Mage_Eav_Model_Entity_Attribute_Source_Interface. To make life easier there is an abstract class Mage_Eav_Model_Entity_Attribute_Source_Abstract we can extend.

So just implement the source model, then you know the int value of your options and you can set it.