Filtering Magento Sales Order Collections
Introduction
When working with Magento collections, especially for filtering sales orders, you might encounter challenges when trying to apply complex conditions like COND1 AND (COND2 OR COND3). This post will guide you through the process of applying this filter in a Magento Enterprise Edition collection using an example of retrieving orders based on date range and status.
The Challenge
The user encountered an issue while trying to retrieve sales orders with specific criteria:
- Orders created or updated within a defined date range
- Status set to ‘processing’
The code that worked previously (in version 1.3.2) failed in the Enterprise Edition 1.11.1 environment:
$orderIds = Mage::getModel('sales/order')->getCollection()
->addFieldToFilter('status', 'processing')
->addFieldToFilter(array(
array(
'attribute' => 'created_at',
'from' => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
'to' => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
),
array(
'attribute' => 'updated_at',
'from' => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
'to' => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
),
));
The SQL Generation
The generated SQL revealed the issue:
SELECT `main_table`.* FROM `sales_flat_order` AS `main_table`
WHERE (status = 'processing') AND (Array = '')
The resulting error message was:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'where clause'
Digging Deeper into the Code
Upon examining the lib/Varien/Data/Collection/Db.php file, we noticed a significant change in the addFieldToFilter function between versions:
Version 1.3.2
public function addFieldToFilter($field, $condition = null)
{
// ...
}
Enterprise Edition 1.11.1
public function addFieldToFilter($field, $condition = null)
{
// ...
}
In the newer version, the first parameter must be a string instead of an array, which caused the issue.
The Workaround
While exploring the Mage_Sales_Model_Resource_Order_Collection class, we found two promising functions: addFieldToSearchFilter and addAttributeToSearchFilter. These functions allow adding field or attribute filters as OR conditions.
$orderIds = Mage::getModel('sales/order')->getCollection()
->addFieldToFilter('status', 'processing')
->addAttributeToSearchFilter(array(
array(
'attribute' => 'created_at',
'from' => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
'to' => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
),
array(
'attribute' => 'updated_at',
'from' => $fromDate->toString('yyyy-MM-dd HH:mm:ss'),
'to' => $toDate->toString('yyyy-MM-dd HH:mm:ss'),
),
));
The generated SQL came very close to the desired result:
SELECT `main_table`.*,
`billing_o_a`.`firstname`,
`billing_o_a`.`lastname`,
`billing_o_a`.`telephone`,
`billing_o_a`.`postcode`,
`shipping_o_a`.`firstname`,
`shipping_o_a`.`lastname`,
`shipping_o_a`.`telephone`,
`shipping_o_a`.`postcode`
FROM `sales_flat_order` AS `main_table`
LEFT JOIN `sales_flat_order_address` AS `billing_o_a`
ON (main_table.entity_id = billing_o_a.parent_id AND billing_o_a.address_type = 'billing')
LEFT JOIN `sales_flat_order_address` AS `shipping_o_a`
ON (main_table.entity_id = shipping_o_a.parent_id AND shipping_o_a.address_type = 'shipping')
WHERE (status = 'processing')
OR (created_at >= '2012-06-16 16:43:38' AND created_at <= '2012-06-18 16:43:38')
OR (updated_at >= '2012-06-16 16:43:38' AND updated_at <= '2012-06-18 16:43:38')
The Desired Result
As you can see, the generated SQL almost meets our requirement of CONDITION1 AND (CONDITION2 OR CONDITION3). However, it applies the conditions as OR conditions instead of combining them with an AND condition.
To achieve this behavior, we need to manipulate the SQL directly or use Magento’s built-in functions in a different way. Unfortunately, there is no straightforward method available within the Mage_Sales_Model_Resource_Order_Collection class to apply complex filters with mixed AND and OR conditions.
Conclusion
While working with Magento collections, especially when dealing with sales order filters, you might encounter limitations in applying complex conditions like CONDITION1 AND (CONDITION2 OR CONDITION3). In this case, the built-in functions provided by Magento do not offer a direct solution for combining AND and OR conditions within a single filter.
To resolve this issue, you can either:
- Manipulate the SQL directly to achieve the desired result
- Create your own custom collection class that extends
Mage_Eav_Model_Entity_Collection_AbstractorMage_Sales_Model_Resource_Order_Collection, and implement the required filtering logic
For more information on creating custom Magento collections, you can refer to the official documentation here.
By following these best practices and understanding the limitations of the built-in functions, you can effectively work with Magento collections and retrieve sales orders based on your specific requirements.