GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  DBIX::CLASS::HELPER::RESULTSET::CORRELATERELATIONSHIP (3)

.ds Aq ’

NAME

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

CONTENTS

SYNOPSIS



 package MyApp::Schema::ResultSet::Author;

 use parent 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(books)->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 <B>couldB> 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 <B>mustB> use a correlated subquery in DBIx::Class. Here is an example of how you might do that:



 package MyApp::Schema::ResultSet::Author;

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

EXAMPLES

    counting CD’s and Tracks of Artists

If you had an Artist ResultSet and you wanted to count the tracks and CD’s per Artist, here is a recipe that will work:



 sub with_track_count {
   my $self = shift;

   $self->search(undef, {
     +columns => {
       track_count => $self->correlate(cds)
         ->related_resultset(tracks)
         ->count_rs
         ->as_query
     }
   });
 }

 sub with_cd_count {
   my $self = shift;

   $self->search(undef, {
     +columns => {
       cd_count => $self->correlate(cds)
         ->count_rs
         ->as_query
     }
   });
 }

 # elsewhere

 my @artists = $artists->with_cd_count->with_track_count->all;



Note that the following will <B>notB> work:



 sub BUSTED_with_track_count {
   my $self = shift;

   $self->search(undef, {
     +columns => {
       track_count => $self->related_resultset(cds)
         ->correlate(tracks)
         ->count_rs
         ->as_query
     }
   });
 }



The above is broken because correlate returns a fresh resultset that will only work as a subquery to the ResultSet it was chained off of. The upshot of that is that the above tracks relationship is on the cds ResultSet, whereas the query is for the Artist ResultSet, so the correlation will be broken by effectively joining to columns that are not in the current scope.

For the same reason, the following will also not work:



 sub BUSTED2_with_track_count {
   my $self = shift;

   $self->search(undef, {
     +columns => {
       track_count => $self->correlate(cds)
         ->correlate(tracks)
         ->count_rs
         ->as_query
     }
   });
 }



AUTHOR

Arthur Axel fREW Schmidt <frioux+cpan@gmail.com>

COPYRIGHT AND LICENSE

This software is copyright (c) 2015 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.

Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 DBIX::CLASS::HELPER::RESULTSET::CORRELATERELATIONSHIP (3) 2015-11-07

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.