[Dbix-class] DBIx::Class 'joining same table twice' SQL difficulty

[Zbigniew Lukasiak]

On Jan 22, 2008 8:40 AM, edwrb . <datecrepe at hotmail.com> wrote:
>
>  Hi guys,

Hi,

>
>   I'm trying to implement the following SQL in DBIx::Class and I seem to be
> running into difficulty
> with the "joining to the same table twice" bit:
>
> SELECT  me.*
>      ,  image_1.image_name
>      ,  image_1.image_location
>      ,  image_2.image_name
>      ,  image_2.image_location
>   FROM  content me
>      ,  image   image_1
>      ,  image   image_2
>  WHERE  me.content_id      = $content_id
>    AND  me.content_id        = image_1.content_id
>    AND  image_1.content_id = image_2.content_id
>    AND  image_1.image_type = 'thumbnail'
>    AND  image_2.image_type = 'main'
>
> I have the table files all setup and non-complex queries are working fine.
> Here's the relationships from the table files:
>
> From Content.pm:
>
> __PACKAGE__->has_many(image => 'DB::Main::Image', 'content_id');
>
> From Image.pm:
>
> __PACKAGE__->belongs_to(content  => 'DB::Main::Content' , 'content_id');
>
>
>  >From the little "Joining to the same table twice" blurb in the
> DBIx::Class::Manual::Joining, it appears that something like the following
> should
>  maybe work:
>
>     my $rs = $schema->resultset('Content')->search(
>       {
>       , 'image_1.image_type' => 'thumbnail'

# The first join does not get the _1 postfix - so
# this line should be
, 'image.image_type' => 'thumbnail'

>       , 'image_2.image_type' => 'main'
>       },
>       {
>         join      => [qw/ image image /],
>         prefetch  => [qw/ image image/]
>       },
>     );
>
> Of course, I'm totally misunderstanding it and it doesn't.  Anyone have any
> ideas for how to implement such a query?
> MANY, MANY THANKS IN ADVANCE!!!
>

And general tip - use the  Tracing SQL trick from perldoc
DBIx::Class::Manual::Troubleshooting to output the generated SQL - I
think this would be easier to spot in the SQL.

Cheers,
Zbigniew
http://perlalchemy.blogspot.com/