[Catalyst] HOWTO: order_by a field in a related record, for paging

[Octavian Rasnita]

From: <will at serensoft.com>
> $c->model()->search( {}, {order_by=>???, page=>$page} )
> 
> How do we "order_by" a field from a related record when pulling a resultset?
> We want to order users by team (then by lastname, firstname) and be able to
> PAGE back and forth...
> 
> e.g. a User belongs_to a Team.
> 
>    my $team_name  = $c->user->team->name;
> 
>    my $users = $c->model('My::User')
>        ->search_rs( {}, {
>            order_by => {
>                -asc => ???user->team->name???,  ###### howto?
>                -asc => 'lastname',
>                -asc => 'firstname',
>            },
>            page => $page,
>        } );
> 
> We're wanting to order by team-name, then user-lastname, then
> user-firstname.
> 
> Here's a perl sort AFTER we have the results:
> 
>    my $user_collection = [
>        sort {
>                $a->team->name cmp $b->team->name ||
>                $a->lastname   cmp $b->lastname   ||
>                $a->firstname  cmp $b->firstname
>                }
>            $users->all
>    ];
> 
> But this approach doesn't allow for paging backward and forward over the
> list...?
> 
> -- 
> will trillich


Hi,

You can use an order_by block like:

order_by => [
{-asc => 'team.name'},
{-asc => 'me.lastname'},
{-asc => 'me.firstname'},
],

Or, if there is -asc for everyone, you can use:

order_by => ['team.name', 'me.firstname', 'me.lastname'],

In the order_by blocks you need to use the names of the columns as they are used in the generated SQL query. For finding those names, use $ENV{DBIC_TRACE}++.

HTH.

Octavian