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

[Jacinta Richardson]

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?

# doesn't work, but is where I got up to...
my $most_busy = $schema->resultset('Project')->search(
        { count    => { join => 'Staff' } },
        { group_by => 'me.staffid' },
        { prefetch     => 'staff' },
);

# generates
"SELECT me.staffid, me.projectname, me.allocation, staff.staffid,
staff.firstname, staff.lastname, staff.address, staff.city, staff.state,
staff.position, staff.wage FROM projects me JOIN staff staff ON staff.staffid =
me.staffid WHERE ( count JOIN ? )" with ParamValues: 0='Staff']


-- 
   ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
    `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
    (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
  _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
 (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |