[Dbix-class] Multiple joins, noob questions

[Robert Kinyon]

On Sun, May 15, 2011 at 19:51, Bill Moseley <moseley at hank.org> wrote:
> On Sun, May 15, 2011 at 2:23 PM, Robert Kinyon <rob.kinyon at gmail.com> wrote:
>>
>> >
>> > First, I make
>> > my $user = $schema->resultset('User')->find(1);
>> > Now I can get all user roles trought user object, but what can I do to
>> > get this roles, ordered by role_rank?
>>
>> my @ordered_roles = map { $_->role } $user->user_roles( {}, { order_by
>> => 'role_rank' } );
>
> role_rank is on the role (or roles, as the table is named), not the
> user_role.

Ok. Easily fixed.

my @ordered_roles =
$user->search_related('user_roles')->search_related( 'roles', {}, {
order_by => 'role_rank' } );

> BTW  -- be careful doing things like that.  Probably minor in this case, but
> might as well do that in a single query.  I tracked down a slow page the
> other day and it turned out looping and and generating over a thousand
> separate queries.  It's one of the reasons we must run database slaves.
>  Avoid the looping.

This is what prefetch is for. My original, if perfectly optimized,
could have been:

my @ordered_roles = map { $_->role } $user->user_roles( {}, { order_by
=> 'role_rank', prefetch => 'role' } );

Then, there's only one query.

Rob