left join Doctrine query

Question

I have 2 table that I want to join with doctrine left join. I want the result to show every user on my "User table" even when score table doesn't have user data on it. And I want it to still show every user when I query other months.

My "User table" as main table

 userid | username | usergroup
------------------------------
   1    | AAAA     |    A
   2    | BBBB     |    A
   3    | CCCC     |    B
   4    | DDDD     |    C

And this is my "Score table" to join with "User table"

 scoreid | userid | month | game1 | game2 
-----------------------------------------
   1     |   1    |   1   |   80  |  80   
   2     |   1    |   2   |   60  |  90
   3     |   3    |   1   |   80  |  70

I want it to look like this when I choose for usergroup = A and with month = 1

 username | game1 | game2 
---------------------------
   AAAA   |  80   |  80   
   BBBB   |  NULL |  NULL

This is what I have done so far. this is not my actual doctrine code.

->select('u.username','s.game1','s.game2')
->from('xxxxxxx:User', 'u')
->leftJoin('xxxxxxx:Score', 's', 'WITH', 'u.userid = s.userid')
->where('u.usergroup = :group')
->andWhere('s.month = :month OR s.month is NULL')

the problem is When I trying to query again with for example month= 2 the query result is empty or not showing all the user from "User table", since I only filter for equal month or when its NULL. How to make a query so I could still be able to query every user for every month?

any help would be very appreciated. thanks

This might help you. http://stackoverflow.com/questions/15087933/how-to-do-left-join-in-doctrine — prava, May 29 '14 at 11:29

score 0 · Accepted Answer · answered Jun 01 '14 at 07:53

ok thanks to Prava for the link. so i come up with this to solve the problem.

->select('u.username','s.game1','s.game2')
->from('xxxxxxx:User', 'u')
->leftJoin('xxxxxxx:Score', 's', 'WITH', 'u.userid = s.userid AND s.month =:month')
->where('u.usergroup = :group')

left join Doctrine query

1 Answers1