CODEWITHSUNDEEP
magento
Matthew
Matthew

Reputation: 3071

Magento Join Product Collection to Custom Table on Custom Attribute

In Magento, how can a standard product collection be joined to a custom table on a custom attribute.

So for example, I have a custom product attribute warehouse_id and a custom table warehouse and now want a collection with product name, warehouse name.

Code example:

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->joinAttribute('warehouse_id', 'warehouse/warehouse', 'warehouse_id', null, 'inner');
var_dump($collection);

However, this does not work because warehouse is a custom table/collection and is not in the eav_entity_type table.

Turning the problem around thought this might work:

$collection = Mage::getModel('warehouse/warehouse')->getCollection()->joinAttribute( etc.

However, joinAttribute is not a method on my custom collection.

Have read every example, every post, wiki example etc. and cannot grasp how to do this. A specific code example would be perfect.

Upvotes: 3

Views: 5935

Answers (1)

The Phil Lee
The Phil Lee

Reputation: 633

I think joinAttribute could possibly work if some of the parameters were changed. I'm a little more familiar with join and joinLeft which are more like working directly with mysql queries.

$collection = Mage::getModel('catalog/product')
    ->getCollection()->getSelect()
    ->joinLeft(array('warehouse_id'=>'warehouse'),'e.warehouse_id = warehouse_id.warehouse_id', array('name'));
var_dump($collection);

This assumes that the name of your custom table is warehouseand that the field in your custom table that corresponds to the custom warehouse_id attribute is also called warehouse_id. This also assumes that the field for the name of the warehouse in your custom table is name. You might have to do something like this instead of naming the table directly:

$collection = Mage::getModel('catalog/product')
    ->getCollection()->getSelect()
    ->joinLeft(array('warehouse_id'=>$this->getTable('warehouse/warehouse')),'e.warehouse_id = warehouse_id.warehouse_id', array('name'));
var_dump($collection);

If neither work, replace the var_dump line with the line:

echo $this->escapeHtml($collection->getSelect());

And post the resulting sql query here.

Upvotes: 1

Related Questions