DBIx::Class::Helper::ResultSet::CorrelateRelationship - Easily correlate your ResultSets
version 2.013000
package MyApp::Schema::ResultSet::Author; use base 'DBIx::Class::ResultSet'; __PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship)); sub with_book_count { my $self = shift; $self->search(undef, { '+columns' => { book_count => $self->correlate('book')->count_rs->as_query } }); } 1;
And then elsewhere, like in a controller:
my $rows = $schema->resultset('Author')->with_book_count->all;
Correlated queries are one of the coolest things I've learned about for SQL since my initial learning of SQL. Unfortunately they are somewhat confusing. DBIx::Class has supported doing them for a long time, but generally people don't think of them because they are so rare. I won't go through all the details of how they work and cool things you can do with them, but here are a couple high level things you can use them for to save you time or effort.
If you want to select a list of authors and counts of books for each author, you could use group_by and something like COUNT(book.id), but then you'd need to make your select list match your group_by and it would just be a hassle forever after that. The "SYNOPSIS" is a perfect example of how to implement this.
group_by
COUNT(book.id)
If you want to select a list of authors and two separate kinds of counts of books for each author, as far as I know, you must use a correlated subquery in DBIx::Class. Here is an example of how you might do that:
package MyApp::Schema::ResultSet::Author; use base 'DBIx::Class::ResultSet'; __PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship)); sub with_good_book_count { my $self = shift; $self->search(undef, { '+columns' => { good_book_count => $self->correlate('books')->good->count_rs->as_query } }); } sub with_bad_book_count { my $self = shift; $self->search(undef, { '+columns' => { bad_book_count => $self->correlate('books')->bad->count_rs->as_query } }); } 1;
my $rows = $schema->resultset('Author') ->with_bad_book_count ->with_good_book_count ->all;
This assumes that the Book resultset has good and bad methods.
good
bad
$rs->correlate($relationship_name)
Correlate takes a single argument, a relationship for the invocant, and returns a resultset that can be used in the selector list.
Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
This software is copyright (c) 2012 by Arthur Axel "fREW" Schmidt.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
To install DBIx::Class::Helpers, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class::Helpers
CPAN shell
perl -MCPAN -e shell install DBIx::Class::Helpers
For more information on module installation, please visit the detailed CPAN module installation guide.