Join table to product/catalog collection to display new custom column in catalog grid

Question

I am trying to add a column to the catalog/product grid. So far I have been able to create the column but have been unable to populate it here is what it looks like now

So far I've tried this

$collection->joinField('volume_id',
            'fpm_volumes_volume_product',
            'volume_id',
            'product_id=entity_id',
            null,
            'left'
        );
$collection->joinField('volume_name',
            'fpm_volumes_volume',
            'volume_name',
            'entity_id=volume_id',
            null,
            'left'
        );

and this

    $volIdsTable = Mage::getSingleton('core/resource')->getTableName('fpm_volumes/volume_product');
    $collection->getSelect()->join(array('volIds' => $volIdsTable), 'e.entity_id = volIds.product_id', array('volume_id'));
    $volsTable = Mage::getSingleton('core/resource')->getTableName('fpm_volumes/volume');
    $collection->getSelect()->join(array('volumes' => $volsTable), 'main_table.volume_id = volumes.entity_id', array('volume_name'));

but none of that works and when I try the second way, using getSelect(), I get this error

Column not found: 1054 Unknown column 'main_table.volume_id' in 'on clause'

my association table looks like this

and the custom table from where I want to retrieve the data(volume_name) looks like this

In the catalog/product table the entity_id(s) are the same as the product_id(s) in the fpm_volumes_volume_product table. However, the volume_name is in the fpm_volumes_volume table.

So can some one guide me through the process of "adding" volume_name to the catalog/product collection by using fpm_voulmes_volume_product as the "bridge" between the catalog/product collection and my fpm_volumes_volume table in order to populate my Volumes column? Thanks everyone!

Answer 1

The error shows that main_table.volume_id does not exist, because main_table is the alias for the product entity table which does not contain a column volume_id.

You have defined volIds as alias for your volume_product association table. So you need to change it

Change from

$collection->getSelect()->join(array('volumes' => $volsTable),
    'main_table.volume_id = volumes.entity_id', array('volume_name'));

to

$collection->getSelect()->join(array('volumes' => $volsTable),
    'volIds.volume_id = volumes.entity_id', array('volume_name'));