How to sort products by bestsellers (e.g. by sales) in Magento 2

Question

these days I've faced a problem: I needed to to sort products by sales. So, first of all I decided to add this field to collection of products and the order it using method $collection->setOrder().

So, here is my solution:

$collection = $this->_productCollection->create()
    ->joinField(
        'qty_ordered',
        'sales_bestsellers_aggregated_yearly',
        'qty_ordered',
        'product_id=entity_id',
        array('store_id' => $this->_storeManager->getStore()->getId()),
        'left'
    )
    ->addAttributeToSelect('*')
    ->setOrder('qty_ordered');

I've posted it cause I think it would be helpful for you. And also I am not sure that it is the best solution.

Maybe someone know better solutions?

Answer 1

In my case I have adapted it a little bit:

$collection->joinField(
    'qty_ordered',
    'sales_bestsellers_aggregated_yearly',
    'qty_ordered',
    'product_id=entity_id', [
        'store_id' => $storeId,
        'period' => date('Y')."-01-01"
    ],
    'left'
);
$collection->setOrder('qty_ordered',SortOrder::SORT_DESC);

Removed addAttributeToSelect as I don't see if it is required.

Added 'period' => date('Y')."-01-01" because that table will duplicate the products every year and if you don't specify the current year, you'll get duplicated rows and Magento will throw an error such as:

Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "20" already exists.

This is not great neither, as you'll start from scratch with the stats every year.

Probably a better solution would be to create a new table based on sales_bestsellers_aggregated_yearly, like, sales_bestsellers_all_time and have a cron job to update that table regularly.