CODEWITHSUNDEEP
symfonydoctrine-orm
Leogout
Leogout

Reputation: 1247

Order by innerJoin table with doctrine

I have a OneToMany relationship between two entities: Shop and Comment. I would like to display a list of Shops ordered by the date of the last review they received. This is what I tried so far:

return $this->createQueryBuilder('s')
            ->innerJoin('s.reviews', 'r', 'WITH', 'r.shop = s.id')
            ->orderBy('r.createdAt') // <- that does nothing on the order of the shops
            ->getQuery()
            ->getResult();

This returns all the shops, but they aren't ordered at all...

Upvotes: 1

Views: 1066

Answers (2)

Taylan
Taylan

Reputation: 3157

If you want to use it inside your ShopRepository you should try it this way

return $this->getEntityManager()
            ->createQueryBuilder()
            ->select('s, MAX(r.createdAt) AS maxCreatedAt')
            ->from('AppBundle:Comment', 'r')
            ->join('AppBundle:Shop', 's', 'WITH', 'r.shop = s.id')
            ->groupBy('s')
            ->orderBy('maxCreatedAt', 'DESC')
            ->getQuery()
            ->getResult();

Don't forget to modify the bundle namespaces (AppBundle) accordingly.

Upvotes: 2

Fabian Schmick
Fabian Schmick

Reputation: 1654

Try to set OrderBy directly on your @ORM\OneToMany mappedBy relation like this:

/**
 * @ORM\OneToMany...
 * @ORM\OrderBy({"createdAt" = "DESC"})
 */
private $reviews;

Upvotes: 2

Related Questions