[Dbix-class] DBIx::Class 'joining same table twice' SQL difficulty
zzbbyy at gmail.com
Tue Jan 22 07:51:11 GMT 2008
On Jan 22, 2008 8:40 AM, edwrb . <datecrepe at hotmail.com> wrote:
> 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 = $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
> 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.
More information about the DBIx-Class