Doctrine get Product with most sales

Question

I have a Product Entity with many Orders as association. I want to build a repository method that brings the most sold products. Each order only have 1 product with 1 quantity so, quantity is irrelevant.

My current progress in ProductRepository:

    public function getMostPopularProducts($limit)
    {
        return $this->createQueryBuilder("p")
            ->join(Order::class, "o", "o.item = p.id")
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }

What would be the best approach here to Join the orders, count total amount of orders and retrieve a result ordered from most sold product to less sold product.

Does this answer your question? [Symfony / Doctrine COUNT Group By and Left JOIN](https://stackoverflow.com/questions/37485778/symfony-doctrine-count-group-by-and-left-join) — yivi, Oct 05 '20 at 10:21

score 0 · Accepted Answer · answered Oct 05 '20 at 14:38

Rookie me was obviously doing this wrong. Right approach is to go from Orders into Products and not the other way around.

This works.

    public function getMostSoldItems()
    {
        return $this->createQueryBuilder("o")
            ->groupBy("o.item")
            ->setMaxResults(10)
            ->orderBy("COUNT(o.item)", "ASC")
            ->getQuery()
            ->getResult();
    }

Doctrine get Product with most sales

1 Answers1