[Dbix-class] "Group by" query in Perl

[Dmitri Pissarenko]

Hello!

I have a table with "activities" and each of them has a certain
duration. Each activitiy is assigned to one category.

I want to create a table, in which

a) each category is shown and
b) for each category the sum of durations of its activities is shown.

In other words, I want to display the results of this query:

select SUM(duration), categories.name
FROM activities, categories
WHERE activities.categoryId = categories.id
GROUP BY activities.categoryId
ORDER BY categories.name

I want to get access to the results of this query in Perl.

This query returns following result, if executed in the database (I
mean via phpMyAdmin):

SUM(duration) name
30 	      Module 1.1
2 	      Module 1.2
10.5 	      View 1
1.15 	      View 2



I've tried this:

my @durationSums = $c->model('TimeTrackingAppDB::Activity')->search(
 undef,
 {
   join     => [qw/ goal /],
   select   => [ 'goal.name', { sum => 'me.duration' } ],
   as       => [qw/ name duration /],
   group_by => [qw/ 'me.goalId' /]
 }
;

This query returns only one row:

43.65    Module 1.1

instead of four.

How can I solve this problem, i. e. modify the query so that it
returns four rows and not one?

Activitity is defined so:

package TimeTrackingAppDB::Activity;

use base qw/DBIx::Class/;

# Load required DBIC stuff
__PACKAGE__->load_components(qw/PK::Auto Core/);
# Set the table name
__PACKAGE__->table('activities');
# Set columns in table
__PACKAGE__->add_columns(qw/id date categoryId name comment goalId duration/);
# Set the primary key for the table
__PACKAGE__->set_primary_key(qw/id/);

__PACKAGE__->belongs_to('goal', 'TimeTrackingAppDB::Goal',
                         { 'id' => 'goalId' } );

__PACKAGE__->belongs_to('category', 'TimeTrackingAppDB::Category',
                                 { 'id' => 'categoryId' } );

Goal is defined like this:

package TimeTrackingAppDB::Goal;

use base qw/DBIx::Class/;

# Load required DBIC stuff
__PACKAGE__->load_components(qw/PK::Auto Core/);
# Set the table name
__PACKAGE__->table('goals');
# Set columns in table
__PACKAGE__->add_columns(qw/id name comment superGoal/);
# Set the primary key for the table
__PACKAGE__->set_primary_key(qw/id/);

__PACKAGE__->has_one ( parentGoal => 'TimeTrackingAppDB::Goal',
'superGoal' );

__PACKAGE__->has_many(goalActivities => 'TimeTrackingAppDB::Activity',
'goalId');

In the log file, following SQL statements are shown:

SELECT goal.name, SUM( me.duration ) FROM activities me  JOIN goals goal ON ( go
al.id = me.goalId ) GROUP BY 'me.goalId':
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `6'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `7'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `11'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `10'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `7'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `6'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `11'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `11'
SELECT me.id, me.name, me.comment FROM categories me WHERE ( ( ( me.id = ? ) ) )
: `1'
SELECT me.id, me.name, me.comment, me.superGoal FROM goals me WHERE ( ( ( me.id
= ? ) ) ): `10'

TIA

Dmitri Pissarenko