Ordering of columns in where clause when using SQL::Abstract

Question

I have been reading some recipes in the Perl Hacks book. Recipe #24 "Query Databases Dynamically without SQL" looked interesting. The idea is to use SQL-Abstract to generate the SQL statement for you.

The syntax to generate a select statement looks something like this:

my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order);

To illustrate further, an example could look like this (taken from the perldoc):

my %where = (
   requestor => 'inna',
   worker => ['nwiger', 'rcwe', 'sfz'],
   status => { '!=', 'completed' }
);
my($stmt, @bind) = $sql->select('tickets', '*', \%where);

The above would give you something like this:

$stmt = "SELECT * FROM tickets WHERE
            ( requestor = ? ) AND ( status != ? )
            AND ( worker = ? OR worker = ? OR worker = ? )";
@bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');

Which you could then use in DBI code like so:

my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);

Now, sometimes the order of the columns in the WHERE clause is very important, especially if you want to make good use of indexes.

But, since the columns to the WHERE clause generator in SQL-Abstract are specified by means of a hash - and as is known, the order that data is retrieved out of perl hashes cannot be guaranteed - you seem to loose the ability to specify the order of the columns.

Am i missing something? Is there an alternate facility to guarantee the order that columns appear in the WHERE clause when using SQL-Abstract ?

Answer 1

I originally misinterpreted your question.

You can use -and to achieve the desired ordering.

For example:

#!/usr/bin/perl

use strict; use warnings;

use SQL::Abstract;

my $sql = SQL::Abstract->new;

my ($stmt, @bind) = $sql->select(
    tickets => '*',
    {
        -and => [
            requestor => 'inna',
            status => { '!=', 'completed' },
            worker => ['nwiger', 'rcwe', 'sfz'],
        ],
    }
);

print "$stmt\n";

See Nested conditions, -and/-or prefixes.

Answer 2

This module cannot do everything -- it is meant as a convenience for constructing queries that will do the job "most of the time". Sometimes you still may need to write a query by hand. I use SQL::Abstract in my main $work::app and have never run into the situation that you describe. A good SQL engine will know which keys are indexed, and optimize the query to use those first, no matter the ordering you specify. Are you sure that your engine is not the same, and that the order you specify in the query is really significant?

If you really need to order your WHERE clauses in a special order, you may find it easier to write subqueries instead. SQL::Abstract can make this easier too.