Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Sorting a collection does not work: getCollection()->addAttributeToSort, ->addOrder, ->setOrder
 
ottom
Member
 
Total Posts:  54
Joined:  2011-02-25
 

Code:

$collection Mage::getModel('sales/order')->getCollection()->addAttributeToSort('weight''ASC');
print 
$collection->getSelect();

Output:

SELECT `main_table`.* FROM `sales_flat_order` AS `main_table`

Where is ORDER BY?

I have also tried addOrder and setOrder:

$collection Mage::getModel('sales/order')->getCollection()->addOrder('weight''ASC');
$collection Mage::getModel('sales/order')->getCollection()->setOrder('weight''ASC');

The output is still the same: the sorting does not work. Certainly the actual data is not sorted as desired.
What is the problem?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Benoit Xylo
Member
 
Avatar
Total Posts:  46
Joined:  2009-10-11
Warsaw, Poland
 

Magento collections are using “lazy loading” mechanism, i.e. data is loaded when you’re trying to access it or when load() method is used explicitly. It affects pre-processing WHERE, ORDER and LIMIT clauses as well.

When you modify your code as follows:

$collection Mage::getModel('sales/order')->getCollection()->addAttributeToSort('weight''ASC')->load();
print 
$collection->getSelect();

you should get proper SQL statement including ORDER BY clause:

SELECT `main_table`.* FROM `sales_flat_order` AS `main_tableORDER BY weight ASC

Moreover I’ve tested collection that is returned this way and it seems that sorting is done properly with this code.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Benoit Xylo
Member
 
Avatar
Total Posts:  46
Joined:  2009-10-11
Warsaw, Poland
 

Well, that’s not exactly what I meant. The clue is that none of ORDER, LIMIT and WHERE clauses are added to SQL stored in collection object as long as collection is not loaded. Of course, the easiest way to load collection is simple call load() method, but iterating through the collection will do it as well, but behind the scene. Try to run this code (I do not call load() method on collection object):

$collection Mage::getModel('sales/order')->getCollection()->addAttributeToSort('weight''ASC');

echo 
'SQL before iterating: ' $collection->getSelect();

foreach (
$collection as $order{
    
echo $order->getIncrementId() . ': ' $order->getWeight() . ' kg';
}

echo 'SQL after iterating: ' $collection->getSelect();

Output:

SQL before iteratingSELECT `main_table`.* FROM `sales_flat_order` AS `main_table`

2000000210.4400 kg
200000023
0.4400 kg
200000017
0.5000 kg
...
20000002750.0000 kg
200000032
54.2000 kg
200000030
68.4000 kg
200000029
79.8000 kg
200000031
115.3000 kg

SQL after iterating
SELECT `main_table`.* FROM `sales_flat_order` AS `main_tableORDER BY weight ASC

What I meant is that you will never get proper SQL statement until you load your collection, explicitly or in any other way.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ottom
Member
 
Total Posts:  54
Joined:  2011-02-25
 

Thank you guys for your responses. It was helpful, but I am still stuck with sorting of collections. I want to sort items within an order.

$collection Mage::getModel('sales/order')->getCollection();

foreach(
$collection as $order{

        $items 
$order->getItemsCollection()->addOrder('price''desc')->load();
// $items = $order->getItemsCollection()->addAttributeToSort('price', 'desc')->load();
        
print $items->getSelect();

}

Even after explicitly calling the load-method as you suggested, the result is still the same:

SELECT `main_table`.* FROM `sales_flat_order_item` AS `main_tableWHERE (order_id '12')

What is the detail I am still missing?

 
Magento Community Magento Community
Magento Community
Magento Community
 
GregC
Sr. Member
 
Avatar
Total Posts:  76
Joined:  2007-11-23
A^2, MI
 

Just in case someone runs across this and is confused by the answers, the getItemsCollection method is specified in
in Mage/Sales/Model/Order.php as

public function getItemsCollection($filterByTypes = array(), $nonChildrenOnly false)

This will not be modified by the ->addAttributeToSort(’base_price’, ‘asc’) call.

So

$items $order->getItemsCollection()->addAttributeToSort('base_price''asc')->load();
echo 
$items->getSelect()

Generates

SELECT `main_table`.* FROM `sales_flat_order_item` AS `main_tableWHERE (order_id '12757')

Whereas

$items Mage::getModel('sales/order_item')->
                            
getCollection()->
                            
addFieldToFilter("order_id",$order->getId())->
                            
addAttributeToSort('base_price''asc')>
                            
load();
echo 
$items->getSelect()

Generates

SELECT `main_table`.* FROM `sales_flat_order_item` AS `main_tableWHERE (order_id '12757'ORDER BY base_price ASC

When you run into problems like this, check the model source code and remember you can always get the collection directly from the database.

Greg

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top