1

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

  • 2
    One way to debug this is to look at the returned SQL to confirm that the sorting is as you describe. Try logging out the query using $collection->getSelect()->__toString() If you can then paste that output with any other collection manipulation that might be done before the return ( passed into the method ) or after in the calling method I can try and help more. – James Cowie Jan 29 '15 at 21:48
  • attached is the resultant tostring of the collection – easymoden00b Jan 29 '15 at 21:58
  • Try $collection->getSelect()->limit(100); to see if it changes. If it does ill check how to remove the limit. – mbalparda Jan 29 '15 at 22:00
  • 1
    To remove the limit $collection->setPageSize(false); Try running the query in a MySQL client and try updating the sort ORDER BY as currently its sorting on news_from_date desc and the nprice asc perhaps the data you have is what makes this appear to be an issue and not the collection query that is being generated. – James Cowie Jan 29 '15 at 22:02
  • @JamesCowie Removing the news_from_date sort and only using the price sort seems to have cleared things up, thanks. Now time to reconfigure this collection sort. If you create an answer I'll gladly accept. – easymoden00b Jan 30 '15 at 14:12
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.