I need to add the most-viewed and best-seller sort options into the rest/v1/products API. I created a plugin to add sort condition for following of below link

How to add custom sort like Most View and Best Selling products in API of Product List in Magento 2?

But in afterGetList plugin how to join a table to the collection. i inject Magento\Catalog\Model\ResourceModel\Product\CollectionFactory but

below my code

<type name="Magento\Catalog\Api\ProductRepositoryInterface">
        <plugin name="custom_sort_plugin" type="Custom\Sort\Plugin\ProductRepository" sortOrder="10" disabled="false" />

ProductRepository plugin file


namespace Custom\Sort\Plugin;
    class ProductRepository
        protected $productCollectionFactory;

        public function __construct(
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
        ) {
        $this->productCollectionFactory = $productCollectionFactory;

        public function afterGetList(
            \Magento\Catalog\Api\ProductRepositoryInterface $subject,
            \Magento\Catalog\Api\Data\ProductSearchResultsInterface $products
        ) {
            $searchCriteria = $products->getSearchCriteria();
            $sortOrders = $searchCriteria->getSortOrders();

            if ($sortOrders) {
                foreach ($sortOrders as $sortOrder) {
                    $field = $sortOrder->getField();
                    $direction = $sortOrder->getDirection();

                    if ($field === 'most_viewed') {
                        $collection = $this->productCollectionFactory->create();


                            ['report_views' => $collection->getTable('report_event')],
                            'e.entity_id = report_views.object_id',
                            ['views_count' => 'COUNT(report_views.event_id)']
                        )->group('e.entity_id')->order('views_count ' . $direction);

                        //$collection->setPageSize(1); // Change the number to your desired limit

                    } elseif ($field === 'best_selling') {
                        $collection = $this->productCollectionFactory->create();

                            ['sales_order_item' => $collection->getTable('sales_order_item')],
                            'e.entity_id = sales_order_item.product_id',
                            ['qty_ordered' => 'SUM(sales_order_item.qty_ordered)']
                        )->group('e.entity_id')->order('qty_ordered ' . $direction);

            return $products;

please help me with this what I am wrong? I am getting records but in response all data not coming.

best seller collection query print like this is the reason all data not coming to the response.

SELECT e.*, SUM(sales_order_item.qty_ordered) AS qty_ordered FROM catalog_product_entity AS e INNER JOIN sales_order_item ON e.entity_id = sales_order_item.product_id GROUP BY e.entity_id ORDER BY qty_ordered DESC

  • please help me on this
    – Devidas
    Commented Nov 8, 2023 at 5:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.