|
|
X-programming for beginners
|
|
DBIx::Class::Helper::ResultSet::CorrelateRelationship(3pUser Contributed Perl DocumentatDBIx::Class::Helper::ResultSet::CorrelateRelationship(3pm) NAME
DBIx::Class::Helper::ResultSet::CorrelateRelationship - Easily correlate your ResultSets VERSION
version 2.013002 SYNOPSIS
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; DESCRIPTION
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. 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; And then elsewhere, like in a controller: 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. See "NOTE" in DBIx::Class::Helper::ResultSet for a nice way to apply it to your entire schema. METHODS
correlate $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. AUTHOR
Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com> COPYRIGHT AND LICENSE
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. perl v5.14.2 2012-06-18 DBIx::Class::Helper::ResultSet::CorrelateRelationship(3pm)
