Search code examples
phpsymfonydoctrine-ormdoctrinedql

Doctrine: retrieving an ordered single entity from a one to many relationship

Suppose the following structure database structure - whereby a category has many articles, and an article has many comments:

Category:
- id

Article:
- id
- category_id

Comment:
- id
- created_at
- article_id

Using Doctrine, what is the best/standard way to:

  1. Fetch 10 Articles and eager load the most recent Comment for each article
  2. Fetch 10 Categories and eager load the most recent Comment in each category

By comparison in Laravel, I could've set up a relation on the Category entity to have one Comment through the Article, and ordered it by the created date. However, I do not know how to achieve this via Doctrine or DQL.

Solution

  • I'm afraid there is no "standard way" for it with eager load. I mean no way to setup it with some config/annotations.

    I'd do it in following way:

    • Create CommentRepository class
    • Create getMostRecentCommentsForCategories(array $categories) and getMostRecentCommentsForArticles(array $articles) methods in it
    • They can return arrays of comments grouped by category/article ID