[Rt-devel] DBIx::SearchBuilder patch for ordering by functions on joined table cols

[Jesse Vincent]

On Fri, Jul 23, 2010 at 03:53:59PM -0700, Ivan Kohler wrote:
> On Thu, Jul 22, 2010 at 08:39:14PM -0400, Jesse Vincent wrote:
> > 
> > On Wed, Jul 21, 2010 at 06:18:35PM -0700, Ivan Kohler wrote:
> > > I'm asking for feedback here, because I'm not sure if I'm Doing It Wrong 
> > > or patching symptoms or something like that...
> > > 
> > > 
> > > I'm adding a new ticket sort option, and I have a need to order search 
> > > results on an SQL function in Tickets_Overlay::OrderByCols, i.e.
> > > 
> > > push @res, { %$row, ALIAS => $linkalias,
> > >                     FIELD => "CAST(SUBSTR(Target,31) AS INTEGER)",
> > >                     ORDER => ($row->{ORDER} || 'ASC') 
> > >            };
> > > 
> > > 
> > > DBIx::SearchBuilder barfed on that badly, so I patched it thus:
> > > 
> > > This seems to work and not break anything else.. but I'm a bit wary of 
> > > changing existing behavior.  Any review and/or comments would be very 
> > > much appreciated... is this a resonable fix, or am I patching the SB 
> > > module to handle my incorrect usage?
> > > 
> > 
> > Hmm. On a quick sleep-deprived read, it looks like you could pass in 
> > FUNCTION rather than FIELD. Would that do what you mean?
> 
> I couldn't find FUNCTION in DBIx::SearchBuilder _OrderClause ?
> 
> But taking that approach I just passed 
> 
>   ALIAS => '',
>   FIELD => "CAST(SUBSTR($alias.Target,31) AS INTEGER)",
> 
> which works fine and makes me happier than expecting SB to substitute in 
> the alias name in arbitrarily complicated expressions.
> 
> Thanks a ton for pointing me in the right direction.
> 

Indeed. I was looking at some confusingly similar code (that clearly wants refactoring) But I'm glad you've found the reasonable answer.