magento join order and invoice

magento join order and invoice

October 11, 2026 · Illustrated author avatarBy Stefan Bogdanescu
magento join order and invoice

Linking Magento Order and Invoice Tables

As a new developer working with Magento, you may find yourself needing to join the sales/order and sales/order_invoice tables to access certain data fields like order_source. This task requires a solid understanding of Magento’s object-oriented architecture and its collection API.

Understanding the Problem

You want to iterate through invoices in a specific date range while also accessing order information, particularly the order_source field from the sales/order table. To achieve this, you’ll need to join these two tables using the shared order_id column.

The Initial Approach

Your initial attempt uses Magento’s collection API to filter invoices by date and then attempts to join with the order_source attribute from the sales/order table:

$collection = Mage::getModel('sales/order_invoice')->getCollection();
$collection->addAttributeToFilter('created_at', array(
    'from' => $startDate,
    'to'   => $endDate
));
$collection->joinAttribute('order_source', 'order/order_source', 'order_id', null, 'left');

foreach($collection as $invoice){
    $orderSource = $invoice->getOrderSource();

    //do other stuff

}

While this code provides a starting point, it contains a small but critical error: the joinAttribute method should be used to join attributes from different tables, not to join entire tables. This means the current approach won’t actually perform the table join you intended.

The Correct Approach

To correctly link these two tables, you’ll need to use the collection’s getSelect() method and modify the underlying SQL query. Here’s how to do it:

$collection = Mage::getModel('sales/order_invoice')->getCollection();
$collection->addAttributeToFilter('created_at', array(
    'from' => $startDate,
    'to'   => $endDate
));

// Get the underlying select object
$select = $collection->getSelect();

// Add a join with the sales_order table using order_id
$select->joinLeft(array('order_table' => $this->getTable('sales/order')),
                 'main_table.order_id = order_table.entity_id');

// Specify that we want to fetch the order_source field from sales/order
$select->columns(array(
    'order_source' => new Zend_Db_Expr('IFNULL(order_table.source, -1)'),
));

// Reapply the select object
$collection->setSelect($select);

foreach($collection as $invoice){
    $orderSource = $invoice->getOrderSource();

    //do other stuff

}

In this corrected version:

  • We fetch the underlying SELECT statement using $collection->getSelect().
  • We add a left join to the sales_order table on the shared order_id column (entity_id in Magento’s EAV system).
  • We specify the order_source field as a new column, using IFNULL to handle cases where the source might be null.

This approach ensures that both tables are correctly linked and the desired data is fetched efficiently.

Conclusion

Joining tables in Magento requires a deep understanding of the collection API and the underlying SQL queries. By modifying the select statement manually, you can achieve complex joins between different tables in the database.

Remember to always test your changes thoroughly in a staging environment before deploying them to production. For more advanced techniques like custom join types or complex filtering criteria, consider referring to Laravel Company’s Magento development resources for comprehensive guides and best practices.

Happy coding!

Database API Orders Errors

Our network