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

[Jacinta Richardson]

Toby Wintermute wrote:

> 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');
> }

Because I needed the order_by as well, I ended up doing this:

my $query = $schema->resultset('Staff')->search(
  { },
  {
    join => 'projects',
    select => ['me.staffid',
              { count => 'projects.projectname', -as => 'projectnamecount'}
    ],
    as => [qw(staffid projectnamecount)],
    group_by => 'me.staffid',
    order_by => 'projectnamecount',
  }
);

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

But thank you so much for setting me on the right track!

> 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.

That's exactly what I expected and what this example is intended to show.  :)
(In fact, this example is derived from a similar example I used for Class::DBI
where there was _no_ way to create such a query via the object interface so you
farmed it out towards DBI.)

> 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 } }
> );

Great plan, and thanks again.  I owe you a beer or cider.  ;)

	J

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