2

I want to join three tables on every time when model load (for example on edit from page, on grid page in admin and detail page in front-end etc... ) in my custom module. can you please let me know where is best place to code for this (in model class, in resource class or collection class) and what function I should use for joining?

please help me to fix this.

Edited:

<?xml version="1.0"?>
<config>
    <modules>
        <Mynamespace_Mymodule>
            <version>1.0.4</version>
        </Mynamespace_Mymodule>
    </modules>
    <global>
        <resources>
            <mynamespace_mymodule_setup>
                <setup>
                    <module>Mynamespace_Mymodule</module>
                    <class>Mynamespace_Mymodule_Model_Resource_Setup</class>
                </setup>
            </mynamespace_mymodule_setup>
        </resources>
        <blocks>
            <mynamespace_mymodule>
                <class>Mynamespace_Mymodule_Block</class>
            </mynamespace_mymodule>
        </blocks>
        <helpers>
            <mynamespace_mymodule>
                <class>Mynamespace_Mymodule_Helper</class>
            </mynamespace_mymodule>
        </helpers>
        <models>
            <mynamespace_mymodule>
                <class>Mynamespace_Mymodule_Model</class>
                <resourceModel>mynamespace_mymodule_resource</resourceModel>
            </mynamespace_mymodule>
            <mynamespace_mymodule_resource>
                <class>Mynamespace_Mymodule_Model_Resource</class>
                <entities>
                    <table1>
                        <table>mynamespace_mymodule_table1</table>
                    </table1>
                    <table2>
                        <table>mynamespace_mymodule_table2</table>
                    </table2>                   
                    <table3>
                        <table>mynamespace_mymodule_table3</table>
                    </table3>                   
                </entities>
            </mynamespace_mymodule_resource>
        </models>
    </global>
    <adminhtml>
        <layout>
            <updates>
                <mynamespace_mymodule>
                    <file>mynamespace_mymodule.xml</file>
                </mynamespace_mymodule>
            </updates>
        </layout>
    </adminhtml>
    <admin>
        <routers>
            <adminhtml>
                <args>
                    <modules>
                        <Mynamespace_Mymodule before="Mage_Adminhtml">Mynamespace_Mymodule_Adminhtml</Mynamespace_Mymodule>
                    </modules>
                </args>
            </adminhtml>
        </routers>
    </admin>
</config>

Thanks in advance!

11

techguy4web,

In this case,you need make one table primary table and using join function[left join,Inner join] on Resource Model and Resource Model Collection class relation rest of two classes.

Add Tables into Model Collection :

You need to first work on collection class.Here,you need do add function _afterLoad() on collection class,w using this function you can mapping relation between there table.

On Resource Class : Mynamespace_Mymodule_Model_Resource_Mymodel_Collection

protected function _afterLoad()
{
$select= $this->getSelect();
$select->joinLeft(
    array('SecondTable'=>$this->getTable('yourmodel/secondtable')),
    'Maintable.key=SecondTable.key',
    array('*')
);

$select->joinLeft(
    array('3rdTable'=>$this->getTable('yourmodel/thirdatble')),
    'Maintable.key=3rdTable.key',
    array('*')
);
return parent::_afterLoad();
}

afterLoad() trigger a perform a operations after collection load.

Add Three tables into model

On resource class you need add multiple table using function _getLoadSelect() on Mynamespace_Mymodule_Model_Resource_Mymodel

 protected function _getLoadSelect($field, $value, $object)
    {
        $select = parent::_getLoadSelect($field, $value, $object);

        $select->join(
                array('SecondTable' => $this->getTable('yourmodel/secondtable')),
               'Maintable.key=SecondTable.key',
                array());

        $select->join(
                array('3rdTable' => $this->getTable('yourmodel/thirdatble')),
               'Maintable.key=3rdTable.key',
                array());

    return $select;
    }

If resource collection is not work then try this code at collection.php

Create two individual function.which is include rest of function to collection.

public function addSecondTable(){

$select= $this->getSelect();
$select->joinLeft(
        array('SecondTable'=>$this->getTable('yourmodel/SecondTable')),
        'Maintable.key=SecondTable.key',
        array('*')
    return $this;   
}

public function addThirdTable(){

    $this->getSelect()->joinLeft(
    array('3rdTable'=>$this->getTable('yourmodel/Thirdatble')),
    'Maintable.key=3rdTable.key',
    array('*'));
    return $this;   
}

You can use:

$collection = Mage::getResourceModel('mynamespace_mymodule/mymodule_collection');
$collection->addSecondTable()->addThirdTable();
  • Many thanks @amit-bera for quick & detailed response. it's really appreciated. But still I have an issue with this. I am not able get this joined collection on grid in _prepareCollection() function. I am calling collection like this $collection = Mage::getResourceModel('mynamespace_mymodule/mymodule_collection'); can you please help me on this? Thanks! – Lalit Kaushik Feb 8 '15 at 16:43
  • Show config XML code – Amit Bera Feb 8 '15 at 16:46
  • I have edited my question, please check – Lalit Kaushik Feb 8 '15 at 16:53
  • please remove afterload function.. check collection model is woring or not – Amit Bera Feb 8 '15 at 17:05
  • I have tried to put die() in _afterLoad function. it's working, i mean that function called. but in grid query it's not showing joined query statement. – Lalit Kaushik Feb 8 '15 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.