byterock

Its sort day here in the Moose-Pen.

Well I have been on a role since I started on 'Group By' so I figure I might as well get the last of the base SQL code snippets in place. The last one is 'sort' or in SQL 'ORDER BY' and why change something that isn't broke here is a new test case '60_order_by.t' and here it the hash I am going to start with;

my $tests = [{

    key  =>'sorts',

    sorts => [

             {name => 'last_name',

              view => 'people'

            },

            {

              name => 'first_name',

              view => 'people'

            },

            ],

    caption => "Order by ",

    sql     => "SELECT people.first_name, people.last_name, people.user_id FROM people ORDER BY people.last_name, people.first_name",

}];

The first thing I will have to do it fix up my test so I will not check for any prams as this SQL has none and this quick one line fix should handle that case;

    cmp_deeply( $da->result()->params, $opts->{params},

        $opts->{caption} . " params correct" )

      if (exists($opts->{params}));

Next the call to the clause in the execture;

...

    $sql .= $self->_group_by_clause();

++    $sql .= $self->_order_by_clause();

…

and the clause sub itself;

sub _order_by_clause {

    my $self = shift;

    return ""

      unless ( $self->sort_count );

    return " "

      . join( " ",

        Database::Accessor::Driver::DBI::SQL::ORDER_BY,

        $self->_elements_sql( $self->sorts() ) );

}

Which is most likely the simplest so far and it must be as on my first run I get;

ok 1 - Order by  SQL correct

So it is a short and sweet post today.

Now I was thinking that I should put an alias in there as well so you can do SQL like this;

select location_id||'-'||department_name as one,manager_id  from departments

order by one

which is perfectly fine as a matter of fact you an even do an SQL like this

select location_id||'-'||department_name as one,manager_id  from departments

order by department_name||'-'||location_id

where the order by is a function or even an expression, at least in all the high end RDBMS.

I am not going to fix this right now. I am going to take some time out and have a look at the code so far and see if I can re-factor things a little and spend some time putting all my tests in order for both Database::Accessor and Driver:DBI.