Re: multiple order_by clauses?

[prev] [thread] [next] [Date index for 2005/06/15]

you can put any string in the order_by clause.


On 15 Jun 2005, at 19:22, d. Taylor Singletary wrote:

>
> Hi all,
>
> I was wondering if it's possible to specify multiple order_by  
> clauses as
> you can in standard SQL, where there are fallbacks in ordering in the
> event that the first order_by qualifier is the same?
>
> The equivalent of:
>
> SELECT CATEGORY.NAME FROM CATEGORY ORDER BY CATEGORY.RANK,  
> CATEGORY.NAME

You can supply any string as an order_by parameter, so long as it  
makes sense when the SQL is assembled. The sql template for search is  
just

     SELECT __ESSENTIAL__
     FROM   __TABLE__
     WHERE  %s

and if there's an order_by parameter it's appended to the WHERE  
clause (at cdbi line 1096 ish):

     $frag .= " ORDER BY $search_opts->{order_by}".

So I often use

     $class->search(%criteria, { order_by => 'date DESC' };

and this should also work:

     $class->search(%criteria, { order_by => 'rank, name' };

You should only need to resort to set_sql if there is a join  
involved, and I don't think you should need to sort in perl at all.

best

Will