Cleanly create SQL with SQL::Abstract::More


Some years ago now, I had written my own wrapper around DBI to make common SQL statements easier to write. Awhile back I found SQL::Abstract and recently revisited some old code to bring it up to the modern era. I found that the related SQL::Abstract::More was almost a direct replacement for what I had written − except with more features and better debugged. Here's an example:


use strict;
use warnings;

use SQL::Abstract::More;

my $sqla = SQL::Abstract::More->new;

# More maintainable with named parameters.
($stmt, @bind) = $sqla->select(
    -columns => [qw(listname id description from_email),
    -from => [qw(mailing_list subscriber)],
    -where => {'subscriber.list_id' => {'=', \ ''},
           '' => {'<>', \ 0}
    -group_by => "list_id",

print "$stmt\n" . join(',',@bind) . "\n";

The Above example produces the code ready to pass to SQL:

    SELECT listname, id, description, from_email, AS active, 
      COUNT(subscriber.person_id) AS subcribercount
    FROM mailing_list, subscriber
    WHERE ( ( <> 0 AND 
      subscriber.list_id = ) ) 
    GROUP BY list_id

After that, it's just a matter of execution:

my $sth = $dbh->prepare($stmt);
$sqla->bind_params($sth, @bind);

You could do the same thing with SQL::Abstract itself, but you have to use somewhat-undocumented features for the "as" and "group by" pieces. Specifically, you can use a scalar reference for a literal field name in the columns part, and shove the "group by" clause into the order field; but you'll have to build the group clause yourself:

use SQL::Abstract;

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

my ($stmt, @bind) =
    $sql->select([qw(mailing_list subscriber)], 
                 [qw(listname id description from_email),
                  \ " AS active",
                  \ "COUNT(subscriber.person_id) AS subcribercount"
                 {'subscriber.list_id' => \ '=',
                  '' => \ '<> 0'},
                 "asc GROUP by list_id"
print "$stmt\n" . join(',',@bind) . "\n";

Altogether, SQL::Abstract::More is superior.