Magento2 sorting collection order

Collections in Magento2 can be a real pain. Was having problems with sorting a category getProductCollection collection into position order!

The products were just displaying in random order(or rather ID number order).

$categoryId=891;
$category = $this->_categoryFactory->create();
$category->load($categoryId);
$products = $category->getProductCollection();
$products->addAttributeToSelect('*');
$products->setOrder("cat_index_position",'ASC'); }
var_dump($products->getSelect()->__toString());
SQL:string(281) "SELECT `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store4` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=4 AND cat_index.category_id=891 AND cat_index.is_parent=1"

Due to this I discovered the solution was 2 things:

  1. to change the parameter to "position"(as in SQL it's given an alias) and then re-load the collection.
  2. re-load the collection ie. $products->load(); and you should do this after updating the parts to any collection almost!
$products->setOrder("position",'ASC'); }
$products->load();
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index_store4` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=4 AND cat_index.category_id=891 AND cat_index.is_parent=1
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id ORDER BY `cat_index`.`position` ASC, `e`.`entity_id` DESC
"

There we go, all working now!

Note: if there is already an ORDER BY part in the SQL and the above didn't work - you can use unshiftOrder which reduces it to a single term:

$postCollection->unshiftOrder("category.position",'ASC'); // category.position, publish_date was the default
$postCollection->load();

Thanks

Leave a Reply