Join two tables and put results in grid magento 1.9.x

Join two tables and put results in grid magento 1.9.x

September 12, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Join two tables and put results in grid magento 1.9.x

Merging Magento Tables for Custom Grid Display

As a senior developer working extensively with Magento 1.x platforms, I often encounter scenarios where clients need to merge data from multiple tables into a single grid view within their custom modules. In your case, you want to display product information along with additional details stored in your own flat table.

Understanding the Current Setup

You’ve already initialized the collection using your HfDistr model:

protected function _prepareCollection()
{
    $collection = Mage::getModel('hf/hfdistr')->getCollection();
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

This setup fetches data from the hf_distr table, which contains your product SKUs and other custom information. However, you’re missing the product names from the catalog_product table.

Merging Data Using Join

To merge these tables, we’ll use a JOIN operation in our collection. We need to join on the product’s SKU (assuming it’s unique).

Update your _prepareCollection method as follows:

protected function _prepareCollection()
{
    $collection = Mage::getModel('hf/hfdistr')->getCollection();
    $collection->getSelect()->joinLeft(
        array('catalog_product' => 'catalog/product_collection'),
        'main_table.sku = catalog_product.sku',
        array('name')
    );
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

In this code:

  • joinLeft creates a left join with the catalog/product_collection table (a temporary collection of products).
  • We match on the SKU column from both tables.
  • The array('name') specifies that we want to include the ‘name’ field from the joined table.

Displaying Merged Data in Grid

Now, you need to update your grid XML file (app/design/adminhtml/default/default/layout/hfdistr.xml) to reflect the new column:

<default>
    <reference name="content">
        <block type="hf/hfgird" template="hf/hfgird.phtml"/>
    </reference>
</default>

Add a new column in your HfGrid class’s _prepareColumns method to display the product name:

$this->addColumn('name', array(
    'header' => Mage::helper('hf')->__('Product Name'),
    'index'  => 'name',
));

This tells Magento that you want a new column named ‘Name’ to be displayed from the data in the ‘name’ field of your joined table.

Conclusion and Further Reading

With these modifications, you should now see a merged grid view containing both product names from the catalog_product table and additional information from your custom flat table.

Remember that proper indexing is crucial for performance when joining large tables. Always ensure your product SKUs are indexed for optimal search speed.

For further in-depth analysis of Magento’s join operations, I recommend exploring Laravel Company’s guide on optimizing joins in eCommerce platforms.

Feel free to reach out if you have any other questions about custom grid development or performance optimization in Magento 1.x.

Magento 1 Catalog Database Extensions

Our network