使用Symfony Doctrine查询2个表的平均值

Question

First of all, excuse me if my english isn't too good.

I'm using Symfony 2.6 with Doctrine as ORM. The project is running on Apache + MySQL.

I have 2 entities in my DB: book and rating. I'm building a system where the user can rate books from 0 to 100.

My entities:

• Book: id, other unrelated fields
• Rating: id, book_id, score, other unrelated fields

Every book can have many ratings but a rating can only be related to a single book (one to many mapping).

My goal is to get the top 5 books ordered by the average score of their ratings.

Example:

• Book 1: 94.5
• Book 2: 93.3
• Book 3: 89.2
• Book 4: 85.2
• Book 5: 79.6

I already tried to get the result using a double for loop with the two entities, but according to the Symfony profiler, it took 3.7 seconds and required 1001 queries, wich is higly inefficient. I'm very sure it could be done in a better way using the DQL, so...

¿Which may be the most efficient way to achieve my goal (if possible in a single query)?

Thanks in advance.

EDIT

Thanks to MikO for the push in the right direction. Is required to write the complete entity (like AcmeBundle:Book) in the FROM and JOIN sentences for it to work. Also, you can't use LIMIT in a querybuilder, so after the query is built and before calling results, I had to use setMaxResults(5) in order to retrieve only the top 5 books.

Thanks very much.

Answer 1

I haven't actually tried this, but I'd create a custom repository class for your Book entity, and then I'd write something like this in it:

public function getTopRatedBooks()
{
    $em = $this->getEntityManager();

    $query = $em->createQuery('
      SELECT book.id, book.name, AVG(rating.score) as avg_rating
      FROM book 
      JOIN rating
      WHERE book.id = rating.book_id
      GROUP BY book.id
      ORDER BY avg_rating DESC
    ');

    $result = $query->getResult();

    return $result;
}