[Dbix-class] Multiple joins, noob questions

[Oleg Kostyuk]

2011/5/16 Robert Kinyon <rob.kinyon at gmail.com>:
> 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.

Where is difference from this? There will be only one query too:

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

By the way, this will fetch only needed column, instead of all, and so
should be faster. This can be important - if this query will be
repeated often, of course.


>
> Rob
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- 
Sincerely yours,
Oleg Kostyuk (CUB-UANIC)