[Dbix-class] Chaining multiple many-to-many relations

[Jean-François Stenuit]

Hi Jon,

Thanks for the answer.

That's what I suspected. However, this approach is not optimal in
terms of queries : I actually get one query per group like this :
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '3' )
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '5' )
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '6' )
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '7' )
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '10' )
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '11' )
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '12' )
SELECT bapplication.id, bapplication.site_id, bapplication.code,
bapplication.comment FROM bgroup_2_application me JOIN bapplication
bapplication ON bapplication.id = me.application_id WHERE (
me.group_id = '16' )

where a single query of this form should do the trick :
select bapplication.* from bapplication
  join bgroup_2_application on
bapplication.id=bgroup_2_application.application_id
  join bgroup               on bgroup_2_application.group_id=bgroup.id
  join buser_2_group        on bgroup.id=buser_2_group.group_id
  join buser                on buser_2_group.user_id=buser.id
where buser.id=8;

Will keep on investigating.

-- Jean-François "Jef" Stenuit


On Wed, Dec 15, 2010 at 4:37 PM, Jon <jon at texttoall.com> wrote:
>> I'd like to use a shortcut of the form :
>> my $rsApplications=$rowUser->groups->applications
>>
>> But it does not seem to work. I get this error :  Can't locate object
>> method "applications" via package "DBIx::Class::ResultSet"
>
> The result of $rowUser->groups is a DBIx::Class::ResultSet...so it's a
> collection of your "groups", not an actual "group".  You can only call
> ResultSet methods on it like, next, and count.  If you want to get to
> your actual group object, which is where you can then call
> "applications", you need to address the actual result object.  e.g.
> my $groups = $rowUser->groups;
> while ( my $group = $groups->next ) {
>   my $applications = $group->applications;
>    while ( my $app = $applications->next ) {
>       # doing awesome app stuff
>   }
> }
>
> Hope that helps.
>
> - jon
>