[Dbix-class] Problem with join

[Alexander Hartmaier]

I had the need for this type of join in the past and was told to wait
for the SQL::Abstract rewrite.
Now version 1.5x is out but I haven't looked into it if it's already
possible now that there is subselect support.

I circumvent that problem with an additional result class which has a
select query or a database view as table and join that to the table to
filter the rows.

Am Freitag, den 29.05.2009, 19:04 +0200 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)
>
> test=#
>
>
> On Fri, May 29, 2009 at 8:50 PM, Moritz Onken <onken at houseofdesign.de>
> wrote:
>
>         Am 29.05.2009 um 16:50 schrieb Андрей Костенко:
>
>
>                 Has I can write in DBIx::Class this query:
>
>                 SELECT * FROM table1 LEFT JOIN table2 ON
>                 table2.table1_id=table2.id AND table2.user_id=123?
>
>                 Where user_id is an any random number.
>
>
>         Depends on your schema.
>
>
>         I'd say this is equivalent to:
>
>                 SELECT * FROM table1 LEFT JOIN table2 ON
>                 table2.table1_id=table2.id WHERE table2.user_id=123
>
>
>         Could be
>
>         $rs->search({ 'table2.user_id' => 123}, { prefetch =>
>         'table2' });
>
>         cheers,
>
>         moritz
>
>
>         _______________________________________________
>         List:
>         http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>         IRC: irc.perl.org#dbix-class
>         SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>         Searchable Archive:
>         http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
--
LG Alex


*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*