Gather Up Even More Moose

It another gather day here in the Moose-Pen.

Now that I did the re-factoring for gather it is time to move on the Driver::DBI and do to code to generate the SQL. Of course I always start with a test case and this time it is a new one 50_having.t. It is much the same as the other test cases so no need to repeat that here, the only important bit is this hash;

my $tests = [{
key =>'gather',
gather =>{
elements => [
name => 'first_name',
name => 'last_name',
view => 'people'
name => 'user_id',
view => 'people'
conditions => [
left => {
name => 'last_name',
right => { value => 'Bloggings' },
operator => '=',
caption => "Group bu with 1 param",
sql => "SELECT people.first_name, people.last_name, people.user_id FROM people GROUP BY people.first_name, people.last_name, people.user_id HAVING people.last_name = ?",
params => ['Bloggings']

The fist thing I need to do is add in a call to generate the SQL;

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

This time I checked and the ISO standard way to formulate SQL is 'WHERE' before 'GROUP BY' though some SQL engines let you do it either other way round.

For the '_group_by_clause' sub I think I can get away with something as simple as

sub _group_by_clause {
my $self = shift;
return ""
unless ( $self->gather );
my $having = $self->gather;
return " ".join(" "
,$having->condition_count >=1
? join(" "
,$self->_predicate_clause( Database::Accessor::Driver::DBI::SQL::GROUP_BY,
$having->conditions ) )
: "");

I gave it the old one~two and my test case gave me

Attribute (gather) does not pass the type constraint because: Validation failed for 'Gather|Undef' with value [ { conditions: ARRAY(0x422b4e4), elements: ARRAY(0x422b01c) }

and that is because I test is assuming I want to put an element in an array not a hash; A little adjustment for that

-- $in_hash->{ $opts->{key} }->[ $opts->{index} ] = $opts->{ $opts->{key} };
if (exists($opts->{index})) {
$in_hash->{ $opts->{key} }->[ $opts->{index} ] = $opts->{ $opts->{key} };
else {
$in_hash->{ $opts->{key} } = $opts->{ $opts->{key} };

and now I get

Can't call method "view" on unblessed reference at GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/ line 508.

a little progress, I guess? What is happening here is I am passing an array-ref of elements down into '_element_sql' sub and it is designed to take only a scalar. A little re-factoring is in order me thinks;

First I created a new sub;

sub _elements_sql {
my $self = shift;
my ($elements) = @_;
my @fields = ();
foreach my $field ( @{$elements} ) {
my $sql = join(", ",@fields);
return $sql;

for formatting up a set of elements/field and then changed the code where this was to be used, first in the '_select_clause' sub

--  my @fields           = ();
--    foreach my $field ( @{$self->elements()} ) {
--        push(@fields,join(" ",
--                        $self->_element_sql($field,1)));
--     }
--   my $select_clause    = join(" ",
--                              Database::Accessor::Driver::DBI::SQL::SELECT,
--                               join(", ",@fields));
++    my $select_clause    = join(" "
++                               ,Database::Accessor::Driver::DBI::SQL::SELECT
++                               ,$self->_elements_sql($self->elements()));

and then in the '_group_by_clause' sub;

-- ,$self->_element_sql($having->elements())
++ ,$self->_elements_sql($having->elements())
,$having->condition_count >=1

and I get an error in the SQL

GROUP BY .first_name, people.last_name, people.user_id HAVING people.last_name = ?

'people' missing off of 'first_name' for now I will stub that in the test hash, but the real problem is in Database::Accessor as it should set that value before it gets to the Driver::DBI. After a quick stub in I now get

ok 1 - Having with 1 param SQL correct
ok 2 - Having with 1 param params correct

Again mostly painless. Now to go back and fix that problem in Database::Accessor but that is for another post.


Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations