[Melbourne-pm] Any DBIx::Class gurus around?

Toby Wintermute tjc at wintrmute.net
Tue Sep 28 07:10:29 PDT 2010


On 29 September 2010 00:00, Jacinta Richardson
<jarich at perltraining.com.au> wrote:
> G'day folk,
>
> I have two tables in DBIx::Class and I'm trying to work out how to do an
> aggregate query across both of them:
>
> package Staff::Schema::Result::Staff;
> use warnings;
> use strict;
> use base qw/DBIx::Class::Core/;
>
> __PACKAGE__->table('staff');
> __PACKAGE__->add_column(staffid => { is_auto_increment => 1},);
> __PACKAGE__->add_columns(qw(firstname lastname address city
>                               state position wage));
> __PACKAGE__->set_primary_key('staffid');
> __PACKAGE__->has_many(projects => 'Staff::Schema::Result::Project', 'staffid');
> 1;
>
> package Staff::Schema::Result::Project;
>
> use warnings;
> use strict;
> use base qw/DBIx::Class::Core/;
>
> __PACKAGE__->table('projects');
> __PACKAGE__->add_column(staffid => { is_foreign_key => 1 } );
> __PACKAGE__->add_columns(qw(projectname allocation));
> __PACKAGE__->set_primary_key('staffid', 'projectname');
> __PACKAGE__->belongs_to(staff => 'Staff::Schema::Result::Staff', 'staffid');
> 1;
>
>
> The SQL I'm trying to generate is this:
>
>        SELECT s.staffid, count(projectname) as numprojects
>        FROM staff s, projects p
>        WHERE p.staffid = s.staffid
>        GROUP BY s.staffid
>        ORDER BY numprojects DESC
>
>
> I've tried a few things, but I just don't seem to be able to get this one to
> work.  Is it true that I'll have to reach out to the underlying dbi object to do
> it?  If so, how do I do that?

I think something along the lines of:
my $query = $schema->resultset('Staff')->search(
  { },
  {
    join => 'projects',
    select => ['me.staffid', { count => 'projects.projectname' }]
    as => [qw(staffid projectnamecount)],
    group_by => 'projectname',
  }
);

while (my $row = $query->next) {
  say $row->staffid . " has project count=" .
$row->get_column('projectnamecount');
}


... which is, if anything, more convoluted than the original SQL.

DBIx::Class works really well for dealing with bunches of rows as if
they were objects, but not as well when it comes to reporting queries.

I'd advise looking at the perldoc for ResultSource::View, and then
creating a package that is one, which encapsulates the reporting
query, or at least a generic version of it.

Then you could do something like:
my $query = $schema->resultset('Report::Staff'Projects')->search;
Or to restrict it to certain staff, eg:
my $query = $schema->resultset('Report::Staff'Projects')->search(
  { staffid => { '<' => 1000 } }
);

Toby


More information about the Melbourne-pm mailing list