MySQL Ambiguity Error on Custom Collection Multiple Attribute Join

Question

I have a custom collection which pretty much only consists of product entity ids. I'd like to display a lot of other attributes from EAV tables, but these are all stored in the value column, which results in an error

Error Code: 1052 Column 'value' in field list is ambiguous

What kind of join method can I use in my _prepareCollection(); method?

I've tried:

$collection->getSelect()->join('catalog_product_entity_varchar', '`catalog_product_entity_varchar`.attribute_id=56 AND `catalog_product_entity_varchar`.entity_id = `main_table`.`product_id`', array('name'  => new Zend_Db_Expr((`catalog_product_entity_varchar`.value)));

Which works for one attribute, but if I try more than one I get the ambiguity issue even though I am specifying the parent table.

Would appreciate any assistance

Answer 1

You should use an alias when you join tables like that, e.g.

$collection->getSelect()->join(
    array('at_name' => 'catalog_product_entity_varchar'),
    'at_name.attribute_id = 56 AND at_name.entity_id = main_table.product_id',
    array('name' => 'at_name.name')
);

Now you can join catalog_product_entity_varchar more than once. There are probably better ways to access EAV data though, use direct SQL only if you know what you are doing.