NAME

DBIx::Class::Helper::ResultSet::CorrelateRelationship - Easily correlate your ResultSets

VERSION

version 2.010001

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.

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.