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

[edwrb .]

Hi guys,

  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      =3D $content_id
   AND  me.content_id        =3D image_1.content_id
   AND  image_1.content_id =3D image_2.content_id
   AND  image_1.image_type =3D 'thumbnail'
   AND  image_2.image_type =3D '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 =3D> 'DB::Main::Image', 'content_id');

>From Image.pm:

__PACKAGE__->belongs_to(content  =3D> '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 =3D $schema->resultset('Content')->search(
      { =

      , 'image_1.image_type' =3D> 'thumbnail'
      , 'image_2.image_type' =3D> 'main'
      },
      {
        join      =3D> [qw/ image image /],
        prefetch  =3D> [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!!!

-ed

_________________________________________________________________
Climb to the top of the charts!=A0Play the word scramble challenge with sta=
r power.
http://club.live.com/star_shuffle.aspx?icid=3Dstarshuffle_wlmailtextlink_jan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080122/34b=
1fccd/attachment-0001.htm