DQL OneToMany query and performing average

Question

In a OneToMany relationship between the entities:

• Composer > Soundtrack
• Soundtrack > SoundtrackRating

i want to select each Soundtrack by a given Composer and avergage the SoundtrackRatings for each Soundtrack.

In DQL:

SELECT s.name, (SUM(r.rating)/COUNT(r.rating)) AS rating 
FROM Soundtrack AS s 
LEFT JOIN SoundtrackRating AS r 
WHERE s.composer = :composer AND r.soundtrackId = s.id 
GROUP BY s.id 
ORDER BY rating DESC

Then i'm passing the :composer instance of type Composer:

$em->createQuery($dql)
  ->setParameter('composer', $composer)
  ->getResult();

However in the query result i am getting all Soundtrack entries, no matter what $composer i pass as a parameter. The only difference is that only averages the ratings of the given $composer, the rest averages 0.

Where is the problem in this query?

Answer 1

Figured it out. Removing r.soundtrackId = s.id from the WHERE clause and using WITH in the LEFT JOIN instead:

SELECT s.name, (SUM(r.rating)/COUNT(r.rating)) AS rating 
FROM Soundtrack AS s 
LEFT JOIN SoundtrackRating AS r WITH r.soundtrackId = s.id
WHERE s.composer = :composer 
GROUP BY s.id 
ORDER BY rating DESC