Symfony2 Doctrine Query with Left Join and Objects as the result

Question

i am new to doctrine and now i want to do the following.

table "user_friend":

id | user_id | friend_user_id

This table contains entries for a contact list.

Now i want to get a result of entries, something like:

SELECT * FROM user_friend a 
    LEFT JOIN user_friend b
    ON (b.user_id = 10 AND b.friend_user_id = a.user_id)
WHERE b IS NULL

This query will be there to get entries from the contact list which are not confirmed by the friend on the other side (WHERE b IS NULL, he has not yet added "me" to his contact list). I hope you understand what i am looking for.

I am not sure if WHERE b IS NULL is correct or how to check in one query if there is already another entry (confirmed friend in the table), what is the right way to to this? My idea was something like a left join, which you can see above.

Second, i don't know how to do this with doctrine in Symfony2. Right now i know the EntityManager and the ->findBy method. This method returns objects, that's nice.

I tried the $em->createQueryBuilder() a little bit but i can't reach my target with the leftJoin there. Do i have to use the createQueryBuilder in the case of the Left Join or is it possible to do it with the EntityManager? Let ask too: Which way is better? Third, i don't know how to get Entity Objects back from the createQueryBuilder. ->getQuery()->getResult(). Do i have to hydrate the result or something?

A little example code for this would be very nice!

Thanks. Good evening.

FuzzyTree FuzzyTree · Accepted Answer · 2014-06-01T20:41:33

In SQL you would have to define the name of your column in the WHERE statement so instead of b IS NULL it needs to be b.user_id IS NULL

SELECT * FROM user_friend a 
    LEFT JOIN user_friend b
    ON (b.user_id = 10 AND b.friend_user_id = a.user_id)
WHERE b.user_id IS NULL

Another way to write the same query using NOT EXISTS

SELECT * FROM user_friend a 
WHERE NOT EXISTS (
    SELECT * FROM user_friend b
    WHERE b.user_id = 10
    AND b.friend_user_id = a.user_id
)

Same query in DQL using NOT EXISTS

$qb->select("a") 
   ->from("MyBundle\Entity\UserFriend a")
   ->where('NOT EXISTS (
        SELECT b
        FROM MyBundle\Entity\UserFriend b 
        WHERE b.user_id = :user_id
        AND b.friend_user_id = a.user_id   
   )')
   ->setParameter('user_id', 10);

In your DQL make sure to use the entity field names, which may not necessarily be the same as your database column names (i.e. the user id might be stored in a column called 'user_id' but the entity's field name might just be 'id' or 'userId')

Symfony2 Doctrine Query with Left Join and Objects as the result

1 Answers