How Can We Order Result Product Collection By Array of SKU

[alainnoutchomwo]

I want to order result of search by specific array of sku

I have a array of skus[sku1,sku2,sku3,...,skuN].

I'd like to be able to order the results of my collection according to this sku order. This would give me a grid with the products displayed in the order sku1,sku2,sku3,...,skuN.

I try something like this on my $collection: `

$skus = [sku1,sku2,sku3,sku4];

 $sortSkus = $collection->getSelect()->getConnection()->quoteInto(
                    'FIELD(e.sku,?)',
                    $skus
  );

  $collection->getSelect()->order(
      new \Zend_Db_Expr($sortSkus . ' ' . 'asc')
  );

This approaches give a Good SQL request who works well in Database and give good result but when we come to the frontend, we have a bad order(sku3,sku1,sku2,sku4 for example).

After some investigation, I realize that smile/elasticsuite overwrites the \Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Fulltext\Collection::_renderFiltersBefore:

protected function _renderFiltersBefore()
    {
        $searchRequest = $this->prepareRequest();

        $this->queryResponse = $this->searchEngine->search($searchRequest);

        // Update the product count.
        $this->_totalRecords = $this->queryResponse->count();

        // Filter search results. The pagination has to be resetted since it is managed by the engine itself.
        $docIds = array_map(
            function (\Magento\Framework\Api\Search\Document $doc) {
                return (int) $doc->getId();
            },
            $this->queryResponse->getIterator()->getArrayCopy()
        );

        if (empty($docIds)) {
            $docIds[] = 0;
        }

        $this->getSelect()->where('e.entity_id IN (?)', ['in' => $docIds]);
        $orderList = join(',', $docIds);
        $this->getSelect()->reset(\Magento\Framework\DB\Select::ORDER);
        $this->getSelect()->order(new \Zend_Db_Expr("FIELD(e.entity_id,$orderList)"));

        $this->originalPageSize = $this->getPageSize();

        $this->isSpellchecked = $searchRequest->isSpellchecked();

        return parent::_renderFiltersBefore();
    }

and performs a search that fetches the information directly from ElasticSearch documents.

I'd like to know how I can order the results based on the skus array?

Smile/ElasticSearch Allow it? if yes, how can I implement it?

Thanks

[romainruaud]

Hi, check how it's made on PageBuilder widgets when using sorting by SKU :

https://github.com/Smile-SA/elasticsuite/blob/2.11.x/src/module-elasticsuite-virtual-category/Plugin/Widget/ProductsListPlugin.php#L145

[alainnoutchomwo]

Thank You, We will check it and let you know if works.

[alainnoutchomwo]

Hi @romainruaud ,

I see that you use this logic to order products in a pagebuilder widget, which is not the case for us. I want to order the results of a search in the frontend on a specific page by sku. To do this, I've created a category custom layout and when my results are displayed, they're out of order even though I'm doing order by SKU because Smile/ElastcSuite overwrites the product collection load method or because Elasticsearch manages them in indexes and documents.

When I test my SQL query in MariaDB, it works fine. When smile/Elasticsuite loads the collection, the data is disordered. I want to be able to tell smile/Elasticsuite to order the products according to the order of the skus I give it.

Thanks for your help

[romainruaud]

I don't get it.

You should be able to reproduce what I've given to you.

1. Inject \Smile\ElasticsuiteVirtualCategory\Model\Widget\SortOrder\SkuPosition\Builder
2. Create your $skus array as in your example

3. $sortOrder = $this->skuPositionSortOrderBuilder->buildSortOrder($skus);
   $attribute = key($sortOrder);
   $dir       = current($sortOrder);
   $collection->setOrder($attribute, $dir);

And that's all, this can be used in any context, not necessary in a widget.

Regards

[github-actions[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.