Magento join tables

Magento join tables

September 22, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento join tables

Solving Complex Queries: Joining Custom Tables in Magento

As an experienced Magento developer, I often encounter complex queries that require joining custom tables to retrieve specific data. Recently, a fellow developer reached out with a challenge involving joining two custom tables based on certain conditions.

The Challenge at Hand

The developer needed to create a query that would:

  1. Select all products ordered by categories using their entity_id.
  2. Join this select statement if the result is valid.
  3. Use specific conditions such as pps_last_unit_price > 0 and pps_last_unit_price != ''.

They provided the following code snippet as a starting point:

SELECT *,
    pps_product_id,
    MIN(pps_last_unit_price) AS pps_last_unit_price,
    pps_quantity_product
FROM
    ".Mage::getSingleton('core/resource')->getTableName('purchase_product_supplier')."
WHERE
    pps_last_unit_price > '0'
    AND pps_last_unit_price != '' AND pps_product_id = ".$data['entity_id']."
GROUP BY
    pps_product_id

and wanted to join it with another select statement:

SELECT *, COUNT(entity_id) AS conta
FROM
    ".Mage::getSingleton('core/resource')->getTableName('catalog_category_entity_varchar')."
WHERE attribute_id = 192 AND entity_id = ".$IDProduto." AND value != '' AND value != '0'

A Step-by-Step Solution

To solve this challenge, we need to construct a single query that combines both select statements while satisfying the given conditions. Here’s how I would approach it:

  1. First, let’s create separate subqueries for each table.

    For the first table:

    $purchaseQuery = "
        SELECT *
            pps_product_id,
            MIN(pps_last_unit_price) AS pps_last_unit_price,
            pps_quantity_product
        FROM
            ".Mage::getSingleton('core/resource')->getTableName('purchase_product_supplier')."
        WHERE
            pps_last_unit_price > '0'
            AND pps_last_unit_price != ''
            AND pps_product_id = :product_id
    GROUP BY
        pps_product_id";
    

    For the second table:

    $categoryQuery = "
        SELECT *, COUNT(entity_id) AS conta
        FROM
            ".Mage::getSingleton('core/resource')->getTableName('catalog_category_entity_varchar')."
        WHERE attribute_id = 192 AND entity_id = :product_id AND value != '' AND value != '0'";
    
  2. Next, we can use Magento’s Zend_Db_Select class to combine these subqueries with a join condition.

    $select = Mage::getSingleton('core/resource')->getConnection('core_read');
    $subQuery1 = new Zend_Db_Expr($purchaseQuery);
    $subQuery2 = new Zend_Db_Expr($categoryQuery);
    
    $mainSelect = $select->from(array('p' => $subQuery1))
                         ->joinLeft(array('c' => $subQuery2), 'c.entity_id = p.pps_product_id');
    
  3. Finally, we can execute the query and retrieve the results.

    $bindParams = array(':product_id' => $data['entity_id']);
    $results = $select->query($mainSelect, $bindParams);
    

Best Practices for Magento Queries

When dealing with complex queries in Magento, it’s essential to:

  • Use parameterized queries ($bindParams) to prevent SQL injection attacks.
  • Leverage Magento’s built-in Zend_Db_Select class for constructing and executing queries.
  • Avoid using semicolons inside the query string itself; use Zend_Db_Expr instead.

Conclusion

In this blog post, we explored a common challenge faced by Magento developers: joining custom tables based on specific conditions. By following the step-by-step solution provided here, you can create efficient and secure queries to retrieve the desired data from your Magento database.

Remember to visit LaravelCompany for more in-depth tutorials and resources on Magento development best practices.

Happy coding!

Catalog Database Orders Pricing

Our network