How to use product sorting on custom product collections?

[OvalMedia]

I have created a category from which I would like to load it's containing products in a custom block using a product collection. Unfortunately my collection does not use the sorting position configured in the backend.

$collection = $this->_productCollectionFactory->create();
$collection->addAttributeToSelect('*')->addCategoryFilter($category);
$collection->getSelect()->order('cat_index_position', 'ASC');

The resulting query looks like this:

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`created_at`, `e`.`updated_at`, `e`.`sku`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`bullet_points`, `e`.`color`, `e`.`color_value`, `e`.`cost`, `e`.`created_at`, `e`.`delivery_time`, `e`.`delivery_time_value`, `e`.`description`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image`, `e`.`image_label`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`required_options`, `e`.`resistance`, `e`.`resistance_value`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`swatch_image`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `cat_index`.`position` AS `cat_index_position`
FROM `catalog_product_flat_1` AS `e`
INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id=300
ORDER BY `cat_index_position` ASC

The collection automatically joins catalog_category_product_index_store1 where (by magento default) the positions are stored. I already figured that your module stores the positions in the table smile_virtualcategory_catalog_category_product_position instead.

So what is the best practice here to have the proper sorting in any product collection. Do I have to rebuild the query from scratch? Or am I missing something?

Thank you

[romainruaud]

Please find a detailed explanation in this comment : https://github.com/Smile-SA/elasticsuite/issues/1579#issuecomment-545964118

And also, keep in mind that your productCollectionFactory should be a Magento\CatalogSearch\Model\ResourceModel\Fulltext\CollectionFactory

[no-response[bot]]

This issue was waiting update from the author for too long. Without any update, we are unfortunately not sure how to resolve this issue. We are therefore reluctantly going to close this bug for now. Please don't hesitate to comment on the bug if you have any more information for us; we will reopen it right away! Thanks for your contribution.

[OvalMedia]

Sorry, but I don't understand. First: For some reason Magento\CatalogSearch\Model\ResourceModel\Fulltext\CollectionFactory does not exist. I cannot add it to a cunstructor of my classes.

Instead I added Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\CollectionFactory to my cunstructor. This works and the collection works. But there is still no sorting available.

This is what I did:

$coll = $this->_collectionFactory->create();
$coll->addAttributeToSelect('*');
$coll->addAttributeToFilter('status', '1');
$sortFilter = ['category.category_id' => $cat_id];
$coll->addSortFilterParameters('position', 'category.position', 'category', $sortFilter);

But there is nothing sorted in the collection at all. What am I missing?

[patricksteenks]

I am running into the same issue as well. Perhaps an update of ElasticSuite has disabled this functionality?

For me the sort is not working in this category. Also the correct products are not loaded. It seems it is using an old index or something? I have updated the magento indexer, but this did not resolve the issue.

[romainruaud]

We did not remove/disable anything.

@OvalMedia the code you've written is enough to "prepare" sorting on position to work : it's indicating to the search engine where exactly the "position" attribute should be picked (in the category.position nested field of the index).

You just need to add this to your code :

$col->resetOrder(); // Perhaps this is not needed in your case.
$col->setOrder('position', 'ASC');

[Nolwennig-PH2M]

In my case, to really reset order, I need to use : $col->getSelect()->reset(\Magento\Framework\DB\Select::ORDER); before apply $col->setOrder('position', 'ASC');