How to transform RAW SQL to DQL with query on LEFT JOIN

Question

i'm with an small problem... I created an query on SQL that works perfectly.

I need an column on query result, that tells me if this row, exists in another row, and group by user_id.

this is my query at moment:

select u.*,
  case when m.usuario_id = u.id
    then 1
    else 0
  end as posicionado
from usuario u
left join ( select usuario_id from matriz where matriz = 1 ) as m
on u.id = m.usuario_id group by u.id

But when i try convert do DQL, it gives a problem on left join ( select.

Here is my actual DQL:

SELECT u, 
    CASE WHEN IDENTITY(m.usuario) = u.id 
        THEN 1
        ELSE 0
    END AS posicionado
    FROM AppBundle:Usuario u
    LEFT JOIN (SELECT IDENTITY (ma.usuario_id) FROM AppBundle:Matriz ma WHERE ma.matriz = 1) as m ON u.id = m.usuario_id group by u.id

It seens like if Doctrine cant understand that query. This is the error:

[Semantical Error] line 0, col 206 near '(SELECT IDENTITY(ma.usuario_id)': Error: Class '(' is not defined.

Can someone help me understand how to use Querys on JOIN with Doctrine? I need it to use with KNPPaginationBundle. And it does not work with raw SQL.

Answer 1

Try not using the Identity function. You don't need it since you're selecting from Matriz. But still I would remove inner select and use on like below:

SELECT u, 
CASE WHEN IDENTITY(m.usuario) = u.id 
    THEN 1
    ELSE 0
END AS posicionado
FROM AppBundle:Usuario u
LEFT JOIN  AppBundle:Matriz ma ON ma.matriz = 1 AND m.usario_id = u.id
group by u.id

Also if you'll show your entities I could improve the join.