Doctrine 2 translate joined count SQL query to DQL

Question

I have two entities, Video and Vote. Vote has a many-to-one relationship with Video, but Video has no relationship with Vote. I'm trying to retrieve a list of Videos sorted by vote count.

The following SQL works to get me what I want:

SELECT video.*, COUNT(video_id) AS vote_count
    FROM video
    LEFT JOIN vote ON vote.video_id = video.id
    GROUP BY video.id
    ORDER BY vote_count DESC;

I'm trying to achieve something similar with DQL, but no luck so far:

SELECT vid.name, COUNT(vote.video_id) as vote_count
    FROM VideoVote\Video\Video vid
    JOIN vote.video vid
    GROUP BY video.id
    ORDER BY vote_count DESC

Answer 1

It can't be done because ... video does not have a relation to vote. So your options are

1. Make the relationship bidirectional (i would go for this one). No penalty on the database, little extra overhead in PHP. (Test performance to see if it works for you).
2. Use your native SQL query and use result set mapping.