Joining Custom Table with EAV Table in Magento 1.8

magento-1.8

I am New to the Magento, I am using the Magento 1.8.
I am stucked while prepare collection function in custom admin module while adding values to the grid.

I have custom table called foundation [not a EAV table] their are 3 fields

|foundation_id | store_id | member_email|
I want to join it with inbuilt EAV table core_store_group

My prepareCollection Function is as follows

$collection = Mage::getModel('core/store_group')->getCollection()
->addFilter('website_id',2)
->addFilter('root_category_id',3);
$collection->getSelect()->join(Mage::getConfig()
->getTablePrefix().'foundation', 'core_store_group.group_id ='.Mage::getConfig()
->getTablePrefix().'foundation.store_id',array('member_email'));
$this->setCollection($collection);
parent::_prepareCollection();
return $this;

I am getting the following Error
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'core_store_group.group_id' in 'on clause'

Best Answer

First of all the core_store_group table is not EAV.
But that's not important Try this:

$collection = Mage::getModel('core/store_group')->getCollection()
    ->addFilter('website_id',2)
    ->addFilter('root_category_id',3);
$collection->getSelect()->join(
            array('foundation' => $collection->getTable('[module]/foundation')),
            'main_table.group_id = foundation.store_id',
            array('member_email')
);
$this->setCollection($collection);
parent::_prepareCollection();
return $this;

Just replace [module] with your modulename in lowercase.

Related Solutions

Magento – Save custom data to custom table error

It should be

$collectionSet = Mage::getModel('npm_commission/commission');

EDIT:

There should be npm too. The problem for me is that Magento don't have proper model config.

        <npm_commission_mysql4>
            <class>Npm_Commission_Model_Mysql4</class>
            <entities>
                <commission>
                    <table>commission</table>
                </commission>
            </entities>
        </npm_commission_mysql4>

Some links: http://www.pixafy.com/blog/2013/04/creating-a-magento-custom-model/ http://www.magentocommerce.com/knowledge-base/entry/magento-for-dev-part-5-magento-models-and-orm-basics

Using Different Table Alias with getCollection() in Magento 1.8

So this main_table section is added in the abstract collection Mage_Core_Model_Resource_Db_Collection_Abstract.

protected function _initSelect()
{
    $this->getSelect()->from(array('main_table' => $this->getMainTable()));
    return $this;
}

Sadly throughout magento it is assumed that the main table with have the alias main_table so I would say when dealing with a magento collection it would be tough/fruitless to change this. If you want to see how bad it gets simply grep for main_table :(

If you change this function to set the alias to your_alias then you will get and error as there is a filter added on store_id with the main_table alias so you do not get very far.

But if you are building your own sql then you can simply pass in the alais and table to the from function of the select.

$select = $adapter->select()->from(array('your_alias' => 'your_table'))

Best Answer

Related Solutions

Magento – Save custom data to custom table error

Using Different Table Alias with getCollection() in Magento 1.8

Related Topic