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.