Magento 1.9 – Join Table to Product Collection to Display Custom Column in Catalog Grid

adminhtmlgridjoin-tablemagento-1.9product-collection

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 updated product grid

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

association table

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

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!

Best Answer

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'));

Related Solutions

How to Join a Calculated Column from a Table to Collection in Magento

Have a look at the methods Mage_Core_Model_Resource_Db_Collection_Abstract::addExpressionFieldToSelect() for standard models:

/**
 * Add attribute expression (SUM, COUNT, etc)
 * Example: ('sub_total', 'SUM({{attribute}})', 'revenue')
 * Example: ('sub_total', 'SUM({{revenue}})', 'revenue')
 * For some functions like SUM use groupByAttribute.
 *
 * @param string $alias
 * @param string $expression
 * @param array $fields
 * @return Mage_Core_Model_Resource_Db_Collection_Abstract
 */
public function addExpressionFieldToSelect($alias, $expression, $fields)

And Mage_Eav_Model_Entity_Collection_Abstract::addExpressionAttributeToSelect() for EAV models:

/**
 * Add attribute expression (SUM, COUNT, etc)
 *
 * Example: ('sub_total', 'SUM({{attribute}})', 'revenue')
 * Example: ('sub_total', 'SUM({{revenue}})', 'revenue')
 *
 * For some functions like SUM use groupByAttribute.
 *
 * @param string $alias
 * @param string $expression
 * @param string $attribute
 * @return Mage_Eav_Model_Entity_Collection_Abstract
 */
public function addExpressionAttributeToSelect($alias, $expression, $attribute)

Usage

$collection->addExpressionFieldToSelect("page_view", "sum({{page_view}})", ["page_view"]);
//                                      ^ alias      ^ expression          ^ used fields

$collection->getSelect()->group("main_table.entity_id");

$collection->addExpressionAttributeToSelect("page_view", "sum({{page_view}})", "page_view");
//                                          ^ alias      ^ expression          ^ used attribute

$collection->getSelect()->group("main_table.entity_id");

Note that the method suggested in the doc block, groupByAttribute() is only used to group by EAV attributes, not static columns like the entity id.

Magento – `getLastPageNumber()` returning wrong number of pages

You are directly operating on Zend_Db_Select which is always a bad idea.

The thing here is, that getLastPageNumber is using getSize, which is \Varien_Data_Collection_Db::getSize and uses \Varien_Data_Collection_Db::getSelectCountSql which is overwritten here \Mage_Catalog_Model_Resource_Product_Collection::getSelectCountSql and implemented with \Mage_Catalog_Model_Resource_Product_Collection::_getSelectCountSql.

Digging deeper in this methods shows:

protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
{
    $this->_renderFilters();
    $countSelect = (is_null($select)) ?
        $this->_getClearSelect() :
        $this->_buildClearSelect($select);
    $countSelect->columns('COUNT(DISTINCT e.entity_id)');
    if ($resetLeftJoins) {
        $countSelect->resetJoinLeft();
    }
    return $countSelect;
}

And I assume your problem is:

    if ($resetLeftJoins) { // true by default
        $countSelect->resetJoinLeft();
    }