Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
DBIx::Class::Helper::RUser:ContributedpPerlRDocumentatiorrelateRelationship(3)

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

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 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 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 not	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
	    }
	  });
	}

SEE ALSO
       o   Introducing DBIx::Class::Helper::ResultSet::CorrelateRelationship
	   <https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-
	   helper-resultset-correlaterelationship/>

       o   Set-based DBIx::Class Advent	Article
	   <http://www.perladvent.org/2012/2012-12-21.html>

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

COPYRIGHT AND LICENSE
       This software is	copyright (c) 2017 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.24.1	      DBIx::Class::Helper::ResultSet::CorrelateRelationship(3)

NAME | SYNOPSIS | DESCRIPTION | METHODS | EXAMPLES | SEE ALSO | AUTHOR | COPYRIGHT AND LICENSE

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=DBIx::Class::Helper::ResultSet::CorrelateRelationship&sektion=3&manpath=FreeBSD+12.1-RELEASE+and+Ports>

home | help