Doctrine get Product with most sales

Tags: , , ,



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.

Answer

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();
    }


Source: stackoverflow