I need to change this sql query to doctrine query or dql form. Is this possible?
SELECT count(*) AS listenCount, l.post_id AS postId
FROM (SELECT DISTINCT ll.user_id, ll.post_id FROM listen ll) l
WHERE l.post_id IN (' . $id . ')
GROUP BY l.post_id
My problem is with the text inside the parentheses in front of "FROM", which needs to be the class name in dql. But I can not give it a class name, because of the DISTINCT command.
Equivalent SQL for your query can be rewritten as
SELECT COUNT(DISTINCT user_id) AS listenCount,
l.post_id AS postId
FROM listen l
WHERE l.post_id IN (' . $id . ')
GROUP BY l.post_id
In doctrine i assume you have setup your listen entity which contains mappings for user entity and post entity so you can write equivalent DQL for above query as below
SELECT COUNT(DISTINCT u.id) AS listenCount,
p.id AS postId
FROM YourBundle:listen l
JOIN l.user u
JOIN l.post p
WHERE p.id IN (' . $id . ')
GROUP BY p.id
Note SQL query just gets the result without using joins for user and post table but in DQL you will need join part for both entities
Even better approach would be select post entity and then do the joining part, also note i assume your post entity has a mapping for listen entity named as listen
SELECT p,COUNT(DISTINCT u.id) AS listenCount,
FROM YourBundle:post p
JOIN p.listen l
JOIN l.user u
WHERE p.id IN (' . $id . ')
GROUP BY p.id
