[Dbix-class] Problem with join

[Peter Rabbitson]

Moritz Onken wrote:
> 
> Am 29.05.2009 um 19:04 schrieb Андрей Костенко:
> 
>> Not so easy. If DBMS can't find records in joined table, it returns
>> NULLs:
>> test=# SELECT * FROM table1 LEFT JOIN table2 ON
>> table2.table1_id=table1.id AND table2.user_id=123;
>>  id | table1_id | user_id
>> ----+-----------+---------
>>   1 |         1 |     123
>>   2 |           |
>>   3 |         3 |     123
>>   4 |           |
>>   5 |           |
>> (5 rows)
>>
>> but your code will return only:
>>
>> test=# SELECT * FROM table1 LEFT JOIN table2 ON
>> table2.table1_id=table1.id WHERE table2.user_id=123;
>>  id | table1_id | user_id
>> ----+-----------+---------
>>   1 |         1 |     123
>>   3 |         3 |     123
>> (2 rows)
> 
> 
> does
> 
>> SELECT * FROM table1 LEFT JOIN table2 ON table2.table1_id=table1.id
>> WHERE table2.user_id=123 OR table2.user_id IS NULL;
> 

No it doesn't. Remember - where operates on the resulting "virtual"
table after all the joins are resolved. So you do your left join
and get back ALL rows from table 1 and ALL related rows from table2
(or you get NULLs). Now you apply the where filter and you get back
_only_ the table1 rows that have a related row with user_id=123, or
ones that have no related rows at all. All table1 rows which have
related rows that are _different_ than user_id=123 will be thrown
away.

This is the infamous "left join with right condition" problem, and
there is no workaround for it, except by specifying the condition
in the join itself.