I'm trying to get a list of orders using the Magento REST API.
The REST request we use is pretty basic: http://www.example.com/api/rest/orders
The response shows the next error:
{
"messages": {
"error": [
{
"code": 0,
"message": "Item (Mage_Sales_Model_Order) with the same id \"54\" already exist"
}
]
}
}
Checking my exception log to see what's going on and got the next backtrace of the error:
2015-09-10T21:54:59+00:00 ERR (3):
exception 'Exception' with message 'Item (Mage_Sales_Model_Order) with the same id "54" already exist' in /path/to/site/lib/Varien/Data/Collection.php:373
Stack trace:
#0 /path/to/site/lib/Varien/Data/Collection/Db.php(576): Varien_Data_Collection->addItem(Object(Mage_Sales_Model_Order))
#1 /path/to/site/lib/Varien/Data/Collection.php(301): Varien_Data_Collection_Db->load()
#2 /path/to/site/app/code/core/Mage/Sales/Model/Api2/Order.php(302): Varien_Data_Collection->getItems()
#3 /path/to/site/app/code/core/Mage/Api2/Model/Resource.php(245): Mage_Sales_Model_Api2_Order->_retrieveCollection()
#4 /path/to/site/app/code/core/Mage/Api2/Model/Dispatcher.php(74): Mage_Api2_Model_Resource->dispatch()
#5 /path/to/site/app/code/core/Mage/Api2/Model/Server.php(239): Mage_Api2_Model_Dispatcher->dispatch(Object(Mage_Api2_Model_Request), Object(Mage_Api2_Model_Response))
#6 /path/to/site/app/code/core/Mage/Api2/Model/Server.php(107): Mage_Api2_Model_Server->_dispatch(Object(Mage_Api2_Model_Request), Object(Mage_Api2_Model_Response), Object(Mage_Api2_Model_Auth_User_Admin))
#7 /path/to/site/api.php(67): Mage_Api2_Model_Server->run()
#8 {main}
I modified the file app/code/core/Mage/Sales/Model/Api2/Order.php (function _retrieveCollection) and added a line to print some info on the logs:
Mage::log($collection->getSelect(),null,'mylog.log');
That's part of the output:
[_parts:protected] => Array
(
[straightjoin] =>
[distinct] =>
[columns] => Array
(
[0] => Array
(
[0] => main_table
[1] => *
[2] =>
)
[1] => Array
(
[0] => payment_method
[1] => method
[2] => payment_method
)
[2] => Array
(
[0] => gift_message
[1] => sender
[2] => gift_message_from
)
[3] => Array
(
[0] => gift_message
[1] => recipient
[2] => gift_message_to
)
[4] => Array
(
[0] => gift_message
[1] => message
[2] => gift_message_body
)
[5] => Array
(
[0] => order_tax
[1] => title
[2] => tax_name
)
[6] => Array
(
[0] => order_tax
[1] => percent
[2] => tax_rate
)
)
[union] => Array
(
)
[from] => Array
(
[main_table] => Array
(
[joinType] => from
[schema] =>
[tableName] => sales_flat_order
[joinCondition] =>
)
[payment_method] => Array
(
[joinType] => left join
[schema] =>
[tableName] => sales_flat_order_payment
[joinCondition] => main_table.entity_id = payment_method.parent_id
)
[gift_message] => Array
(
[joinType] => left join
[schema] =>
[tableName] => gift_message
[joinCondition] => main_table.gift_message_id = gift_message.gift_message_id
)
[order_tax] => Array
(
[joinType] => left join
[schema] =>
[tableName] => sales_order_tax
[joinCondition] => main_table.entity_id = order_tax.order_id
)
)
[where] => Array
(
)
[group] => Array
(
)
[having] => Array
(
)
[order] => Array
(
)
[limitcount] =>
[limitoffset] =>
[forupdate] =>
)
[_tableCols:protected] => Array
(
)
)
If I understood correctly that means that the SQL statement was something like:
SELECT
main_table.*,
payment_method.method AS method,
gift_message.sender AS gift_message_from,
gift_message.recipient AS gift_message_to,
gift_message.message AS gift_message_body,
order_tax.title AS tax_name,
order_tax.percent AS tax_rate
FROM
sales_flat_order AS main_table LEFT JOIN
sales_flat_order_payment AS payment_method ON main_table.entity_id = payment_method.parent_id LEFT JOIN
gift_message ON main_table.gift_message_id = gift_message.gift_message_id LEFT JOIN
sales_order_tax AS order_tax ON main_table.entity_id = order_tax.order_id
After manually running the previous query, it came up with more than one row with the same entity_id (sales_flat_order). These 'duplicated' entity_id rows seem to be the problem later on when using Varien_Data_Collection->addItem
The part of the query that is making the two rows with same entity_id to be on the resultset is the LEFT JOIN sales_order_tax. That table contains can contain N rows per each order placed, since every row contains a different tax rule applied.
For example in Canada we collect two different Tax Rules combined for some areas. In British Columbia we collect GST 5% (country specific) plus PST 7% (province specific).
Am I missing something obvious here, or did I run into a bug?
Any help is much appreciated, thanks for reading!
P.S. My issue is very close to the one described in here: Magento API V2 Sales Orders List Not Working
After a while playing around I think I found a solution, so I'm posting it here for future references.
Solution
We need to modify the query of the collection, to group by sales_flat_order.entity_id before it starts iterating the items in Mage_Sales_Model_Api2_Order::_retrieveCollection.
Since modifying core files it's not a good practice we can make a copy of the core class to the local code pool and modify it as desired. Magento will use the class under the local folder to override the core class.
$this->_addTaxInfo($collection); you should add:
$collection->getSelect()->group('main_table.entity_id');Cons
If we are updating Magento we want to compare our current version of the Mage_Sales_Model_Api2_Order class with the new version, and if we find differences we shall repeat the process of making a copy of the core file under the local code pool folder and perform the edition again.