CODEWITHSUNDEEP
sqlsymfonydoctrine-orm
shinji
shinji

Reputation: 55

Translate SQL query to Doctrine 2

I have two tables:

author (id, first_name, last_name)
books (id, title, rate, author_id)

and i need to get authors with highest rated book (one per author).

In sql:

SELECT a.*, highest_rated_book.*
      FROM authors a
      LEFT JOIN (SELECT * FROM books b ORDER BY b.rate DESC) AS highest_rated_book
      ON a.id = highest_rated_book.author_id
      GROUP BY highest_rated_book.author_id
      ORDER BY a.id;

But i need this in Doctrine 2. The biggest problem i have is to combine left join and subselect.

Is this possible?

Upvotes: 0

Views: 612

Answers (1)

Guillaume Flandre
Guillaume Flandre

Reputation: 8980

Using DQL, it seems that you can only join entities that are associated (cf. http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/dql-doctrine-query-language.html#from-join-and-index-by).

What you can do is use a native query, that allows you to use raw sql.

More info on Native SQL with Doctrine 2 can be found here: http://docs.doctrine-project.org/en/latest/reference/native-sql.html

Upvotes: 1

Related Questions