[Dbix-class] order_by problem with paged results - column name with spaces...

[Michael Higgins]

Hello, list --

Consider this code:

    my $shipments = $schema->resultset('DATA')->search_like(
                 %$params,
               {
                join => ['Shipper','Consignee'],
                 order_by =>\'"Invoice #" DESC',
#                rows => 10,
#                page => $page,

                }
                    ); #->page($page);

Basically, the 'order_by' directive works until I try to make it into a
paged resultset.

(From /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/ResultSet.pm):
"
Please note that if you have quoting enabled (see
DBIx::Class::Storage/quote_char) you will need to do \'year DESC' to specify
an order. (The scalar ref causes it to be passed as raw sql to the DB, so
you will need to manually quote things as appropriate.)
"
... and this works as advertised until I try to make paged results.

SQL::Abstract::Limit _seems_ responsible for the errors. If I pass the
reference, it carps about ORDER BY SCALAR(0x85d842c) DESC and if I don't,
then it complains bad column order spec: "Invoice #" DESC.

>From SQL::Abstract:

sub _order_by {
    my $self = shift;
    my $ref = ref $_[0];

    my @vals = $ref eq 'ARRAY'  ? @{$_[0]} :
               $ref eq 'SCALAR' ? ${$_[0]} :
               $ref eq ''       ? $_[0]    :
               puke "Unsupported data struct $ref for ORDER BY";

    my $val = join ', ', map { $self->_quote($_) } @vals;
    return $val ? $self->_sqlcase(' order by')." $val" : '';
}
( ... maybe the _quote routine has some magic?)

However, from SQL::Abstract::Limit:

    my $ref = ref $order;

    my @order;

CASE: {
    @order = @$order,     last CASE if $ref eq 'ARRAY';
    @order = ( $order ),  last CASE unless $ref;
    @order = ( $$order ), last CASE if $ref eq 'SCALAR';
    Carp::croak __PACKAGE__ . ": Unsupported data struct $ref for ORDER BY";
}
    foreach my $spec ( @order )
    {
        my @spec = split ' ', $spec;
        Carp::croak( "bad column order spec: $spec" ) if @spec > 2;
        push( @spec, 'ASC' ) unless @spec == 2;
        my ( $col, $up ) = @spec; # or maybe down
        $up = uc( $up );
        Carp::croak( "bad direction: $up" ) unless $up =~ /^(?:ASC|DESC)$/;
        $order_by_up .= ", $col $up";
        my $down = $up eq 'ASC' ? 'DESC' : 'ASC';
        $order_by_down .= ", $col $down";
    }
(... doesn't seem like the same thing is going on to create the order by
parts, at all.)

Anyway, has anyone come across this before? If so, is there a known
workaround? 

Thanks for any help.

Cheers,

-- 
Michael Higgins