CraftCMS Commerce order by most sold products

Question

I am currently working on a project where I have to sort products based on their times being sold. Unfortunately I could not find anything related to this functionality.

Where do I start to implement this feature/functionality?

Answer 1

Here is a simple start. It builds a query, which goes into the commerce lineitems table, groups the rows by purchasableId and gives the top 20 counts. Be aware, that the result consists of Variants-IDs.

        $result = (new Query())->select([
            'purchasableId',
            'COUNT(purchasableId) as times_bought',
        ])
            ->from(['{{%commerce_lineitems}}'])
            ->orderBy(['times_bought' => SORT_DESC])
            ->groupBy('purchasableId')
            ->limit(20)
            ->all();