SQL – How to Load Model Data Joining 2 Tables

modelsql

Suppose I have a simple Model A that target table A now I have to add some extra info so I decide to create a Model B and save those extra info in a Table B with a foreign key pointing to Table A index key.

Usually Joins are done in collections but if I load a model the collection is not called, instead only the resource model is used.

How can I modify Model A to load also data from Table B on load()?

Best Answer

Inside resource model class add following function

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

    $select->joinLeft(
        array('t_b' => 'table_b'),
        $this->getMainTable() . '.id = t_b.id',
        array('columnA'));
    return $select;
}

When you called Mage::getModel('modulename/TableA')->load(1), this method will join with table_b.

Related Solutions

Magento – Joining to a Resource Model that doesn’t have a Select

The reporting collections work a bit differently that the Mage_Core_Model_Abstract/CRUD collections. The CRUD collections will have a SQL query assigned immediately at instantiation. The reporting collection, however, initializes its SQL statement during its load operation. You can see this in the load method down in the abstract base class for all reporting collections.

#File: app/code/core/Mage/Reports/Model/Resource/Report/Collection/Abstract.php
public function load($printQuery = false, $logQuery = false)
{
    if ($this->isLoaded()) {
        return $this;
    }
    $this->_initSelect();
    if ($this->_applyFilters) {
        $this->_applyDateRangeFilter();
        $this->_applyStoresFilter();
        $this->_applyCustomFilter();
    }
    return parent::load($printQuery, $logQuery);
}

There's no select until _initSelect is called.

The general approach you'll want to take here is

Create a new resource model class which extends the Mage_Reports_Model_Resource_Report_Collection_Abstract class
Define a _applyCustomFilter method in your class

and then use your new resource model. Your _applyCustomFilter method would look something like this.

protected function _applyCustomFilter()
{
    $return = parent::_applyCustomFilter();
    //manipulate the select all you want here (`$this->getSelect()`)
    return $return;
}

Magento – Magento – custom (non eav) model, load by multiple fields

I think this is a good approach. Maybe you need to create a wrapper in the model class so you will avoid writing the same thing over and over again.
Something like:

public function loadByMultiple($tag, $customName, $group){
    $collection = $this->getCollection()
            ->addFieldToFilter('tag_name', $tag)
            ->addFieldToFilter('custom_name', $customName)
            ->addFieldToFilter('group_name', $group);
    return $collection->getFirstItem();
}

And you can load the item like this in any other place:

$model = Mage::getModel('model/class_here')->loadByMultiple($tag, $customName, $group);
if ($model->getId()){
   //the instance exists
}
else{
    //not found
}

Best Answer

Related Solutions

Magento – Joining to a Resource Model that doesn’t have a Select

Magento – Magento – custom (non eav) model, load by multiple fields

Related Topic