DBIx::SearchBuilder patch for ordering by functions on joined table cols

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

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’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.

Ivan Kohler
Open-source billing, ticketing and provisioning
for ISPs, VoIP providers and online businesses

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.