[Dbix-class] Problem with join

[Moritz Onken]

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;

fix this?

DBIC:

$rs->search(['table2.user_id' => 123, 'table2.user_id' => {'=' =>  
undef}], { prefetch => 'table2' });