[Melbourne-pm] Any DBIx::Class gurus around?
Jacinta Richardson
jarich at perltraining.com.au
Tue Sep 28 16:41:31 PDT 2010
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 |
More information about the Melbourne-pm
mailing list