Skip to content
Advertisement

Doctrine get Product with most sales

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.

Advertisement

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement