Doctrine DQL - how to select only unique records from the dataset

I was tasked with selecting a brand and a customer with the highest order value for that brand (together with the order value) from the database.

I have come up with this query:

    $ordersQuery = $em->createQuery(
            "SELECT b.name AS brand, c.name, MAX(DISTINCT o.value) AS total
            FROM AppBundle:VOrder o
            LEFT JOIN o.brand b
            LEFT JOIN o.customer c
            GROUP BY b.id, c.id"
    );

The result of the query are records like these:

Brand #1 ( Customer #5 - 7.00 )

Brand #1 ( Customer #27 - 35.00 )

Brand #1 ( Customer #32 - 169.00 )

Brand #1 ( Customer #38 - 101.00 )

Brand #2 ( Customer #334 - 21.00 )

Brand #2 ( Customer #344 - 61.00 )

Brand #2 ( Customer #364 - 159.00 )

Brand #2 ( Customer #427 - 170.00 )

As you can see, there are many record for each brand.

How can I modify my query so that only one record with the highest order value for each brand (along with a customer) is shown?

Solution

I don't think it's possible to perform it with DQL but you can do raw query like this :

SELECT t1.maxvalue, b.name AS brand, c.name
FROM VOrder o
JOIN  (
    SELECT MAX(DISTINCT value) as maxvalue, brandId 
    FROM VOrder
    GROUP BY brandId
) as t1 ON (o.value = maxvalue AND o.brandId = t1.brandId)
LEFT JOIN brand b ON (...)
LEFT JOIN customer c ON (...)

You can build it using new Doctrine_RawSql().

An exemple of use : Doctrine - subquery in from