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:

#!/usr/env/perl

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),
         'mailing_list.active|active',
         "COUNT(subscriber.person_id)|subcribercount",
    ],
    -from => [qw(mailing_list subscriber)],
    -where => {'subscriber.list_id' => {'=', \ 'mailing_list.id'},
           'subscriber.active' => {'<>', \ 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, 
      mailing_list.active AS active, 
      COUNT(subscriber.person_id) AS subcribercount
    FROM mailing_list, subscriber
    WHERE ( ( subscriber.active <> 0 AND 
      subscriber.list_id = mailing_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);
$sth->execute;

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),
                  \ "mailing_list.active AS active",
                  \ "COUNT(subscriber.person_id) AS subcribercount"
                 ],
                 {'subscriber.list_id' => \ '= mailing_list.id',
                  'subscriber.active' => \ '<> 0'},
                 "asc GROUP by list_id"
    );
print "$stmt\n" . join(',',@bind) . "\n";

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