Query aggregates

From ClassDBI

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