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

[Ivan Kohler]

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:

--- DBIx-SearchBuilder-1.56.dist/SearchBuilder.pm       2009-07-16 14:57:18.000000000 -0700
+++ DBIx-SearchBuilder-1.56/SearchBuilder.pm    2010-07-21 18:06:22.000000000 -0700
@@ -1094,10 +1094,10 @@
             $rowhash{'FIELD'} and
              $rowhash{'ORDER'} ) {
 
-           if ( length $rowhash{'ALIAS'} && $rowhash{'FIELD'} =~ /^(\w+\()(.*\))$/ ) {
+           if ( length $rowhash{'ALIAS'} && $rowhash{'FIELD'} =~ /^((\w+\()+)(.*\)+)$/ ) {
                # handle 'FUNCTION(FIELD)' formatted fields
-               $rowhash{'ALIAS'} = $1 . $rowhash{'ALIAS'};
-               $rowhash{'FIELD'} = $2;
+               $rowhash{'FIELD'} = $1. $rowhash{'ALIAS'}. '.'. $3;
+               $rowhash{'ALIAS'} = '';
            }
 
             $clause .= ($clause ? ", " : " ");


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?

-- 
Ivan Kohler
Open-source billing, ticketing and provisioning
for ISPs, VoIP providers and online businesses
http://www.freeside.biz/freeside/