Magento sorting collection only sorting first 20 products

Question

I'm passing a collection to a method that will conditionally apply sorting to it. However, it seems that only 20 of the total products are being sorted, the rest, are unsorted and lay at the end of the bulk sorted section. Why is this? Below is the the conditional:

protected function sortProducts($collection)
{
    switch ($this->getOrder()) {
        case 'product group':
            return $collection->addAttributeToSort('price', 'asc');
    ...
    ...
    }
}

Below is the resultant log of the product collection (note this is a new products collection containing 25 products in total).

2015-01-29T21:53:38+00:00 DEBUG (7): SELECT 1 AS status, e.entity_id, e.type_id, e.attribute_set_id, cat_index.position AS cat_index_position, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, e.name, e.short_description, e.price, e.special_price, e.special_from_date, e.special_to_date, e.small_image, e.thumbnail, e.news_from_date, e.news_to_date, e.url_key, e.required_options, e.image_label, e.small_image_label, e.thumbnail_label, e.msrp_enabled, e.msrp_display_actual_price_type, e.msrp, e.tax_class_id, e.price_type, e.weight_type, e.price_view, e.shipment_type, e.links_purchased_separately, e.links_exist, e.giftcard_amounts, e.allow_open_amount, e.open_amount_min, e.open_amount_max FROM catalog_product_flat_1 AS e INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2' INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 WHERE (((((e.news_from_date <= '2015-01-29 23:59:59') OR (e.news_from_date IS null))))) AND (((((e.news_to_date >= '2015-01-29 00:00:00') OR (e.news_to_date IS null))))) AND ((e.news_from_date IS not null) OR (e.news_to_date IS not null)) ORDER BY e.news_from_date desc, price_index.min_price asc

Answer 1

It looks like your collection is being sorted as expected however there is another SORT also being applied news_from_date that gives the appearance of incorrect results.

Steps to debug are:

Output the SQL query using $collection->getSelect()->__toString() or via MyTop / alternatives.

Running the SQL query in a MySQL client we can inspect what could be causing the issues. In this case it was the final SORT condition where the collection is being sorted on news from date desc and then price asc.

Fix

Now we know that there is another sort being applied to the collection we can update the block method creating this collection to only apply the sorting we require.