Query aggregates

Sum of column

I needed a way to calculate the sum of a column.

The docs describe a way to do this when you don't need constraints:

my $sum = $class->sql_single("sum(my_column)")->select_val;

However, you often have to restrict this with certain criteria, so this is what I came up with. It uses SQL::Abstract to add the criteria.

First we set up the sql statement in our CDBI class:

__PACKAGE__->set_sql(sum_them => "SELECT SUM(%s) FROM __TABLE__ %s");

and then the core function:

sub sum { my ($class) = shift; my ($column) = shift; my $sql = new SQL::Abstract; my ($where, @bind) = $sql->where(@_); return $class->sql_sum_them($column, $where)->select_val(@bind); }

You'd call this as follows:

# no restrictions: my $total = $class->sum('my_column');

# field1 must be 3: my $sum_col = $class->sum('my_column', { field1 => 3 });

Now if SQL::Abstract supported "GROUP BY", then we could make this even more versatile... But this principle works great for what I needed to do.

-- RhesaRozendaal

Aggregates which don't set all of the primary key

Consider the following simple class with an aggregate search:

# Declare our primary key field(s) __PACKAGE__->columns(Primary => 'StaffID', 'ProjectName'); __PACKAGE__->columns(All => 'StaffID', 'ProjectName', 'Allocation'); __PACKAGE__->columns(TEMP => 'Total');

# Declare our relationships to the other tables/classes ...

# Select total time allocated to each project __PACKAGE__->set_sql(total_times =>       "SELECT ProjectName, sum(Allocation) as Total         FROM Projects         GROUP BY ProjectName         ORDER BY ProjectName"   );

Since our aggregating function (search_total_times) isn't setting all of the primary keys, the following will fail to print any results:

my $results_it = search_total_times;

while(my $result = $results_it->next) { print Dumper $result; }

This is because when a Class::DBI object is taken in boolean form it returns false if any part of its primary key is null. This is important because without a fully developed primary key the object cannot be a representation of a row in the database. As such changes to the object cannot be saved to the database.

A solution to make this work, in the understanding that search results of this form must be treated as if they were *read-only* is as follows:

my $results_it = search_total_times;

while( ref (my $result = $results_it->next) ) { print Dumper $result; }

This asks instead whether $result was set to a reference, which it will be unless you've gone through all of your results.

The problem can also be avoided by using your results in a list format:

my @results = search_total_times;

foreach my $result (@results) { print Dumper $result; }

which will work as desired but shouldn't be used where your result set is large.

-- Jacinta Richardson