.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings. \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote. \*(C+ will
.\" give a nicer C++. Capital omega is used to do unbreakable dashes and
.\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
. ds -- \(*W-
. ds PI pi
. if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
. if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch
. ds L" ""
. ds R" ""
. ds C` ""
. ds C' ""
'br\}
.el\{\
. ds -- \|\(em\|
. ds PI \(*p
. ds L" ``
. ds R" ''
. ds C`
. ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD. Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
. if \nF \{\
. de IX
. tm Index:\\$1\t\\n%\t"\\$2"
..
. if !\nF==2 \{\
. nr % 0
. nr F 2
. \}
. \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBIx::Class::Helper::ResultSet::CorrelateRelationship 3pm"
.TH DBIx::Class::Helper::ResultSet::CorrelateRelationship 3pm "2022-12-06" "perl v5.36.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification. Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBIx::Class::Helper::ResultSet::CorrelateRelationship \- Easily correlate your ResultSets
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 1
\& package MyApp::Schema::ResultSet::Author;
\&
\& use parent \*(AqDBIx::Class::ResultSet\*(Aq;
\&
\& _\|_PACKAGE_\|_\->load_components(qw(Helper::ResultSet::CorrelateRelationship));
\&
\& sub with_book_count {
\& my $self = shift;
\&
\& $self\->search(undef, {
\& \*(Aq+columns\*(Aq => {
\& book_count => $self\->correlate(\*(Aqbooks\*(Aq)\->count_rs\->as_query
\& }
\& });
\& }
\&
\& 1;
.Ve
.PP
And then elsewhere, like in a controller:
.PP
.Vb 1
\& my $rows = $schema\->resultset(\*(AqAuthor\*(Aq)\->with_book_count\->all;
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
Correlated queries are one of the coolest things I've learned about for \s-1SQL\s0
since my initial learning of \s-1SQL.\s0 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.
.PP
If you want to select a list of authors and counts of books for each author,
you \fBcould\fR use \f(CW\*(C`group_by\*(C'\fR and something like \f(CW\*(C`COUNT(book.id)\*(C'\fR, but then
you'd need to make your select list match your \f(CW\*(C`group_by\*(C'\fR and it would just
be a hassle forever after that. The \*(L"\s-1SYNOPSIS\*(R"\s0 is a perfect example of how
to implement this.
.PP
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 \fBmust\fR use a correlated subquery
in DBIx::Class. Here is an example of how you might do that:
.PP
.Vb 1
\& package MyApp::Schema::ResultSet::Author;
\&
\& use parent \*(AqDBIx::Class::ResultSet\*(Aq;
\&
\& _\|_PACKAGE_\|_\->load_components(qw(Helper::ResultSet::CorrelateRelationship));
\&
\& sub with_good_book_count {
\& my $self = shift;
\&
\& $self\->search(undef, {
\& \*(Aq+columns\*(Aq => {
\& good_book_count => $self\->correlate(\*(Aqbooks\*(Aq)\->good\->count_rs\->as_query
\& }
\& });
\& }
\&
\& sub with_bad_book_count {
\& my $self = shift;
\&
\& $self\->search(undef, {
\& \*(Aq+columns\*(Aq => {
\& bad_book_count => $self\->correlate(\*(Aqbooks\*(Aq)\->bad\->count_rs\->as_query
\& }
\& });
\& }
\&
\& 1;
.Ve
.PP
And then elsewhere, like in a controller:
.PP
.Vb 4
\& my $rows = $schema\->resultset(\*(AqAuthor\*(Aq)
\& \->with_bad_book_count
\& \->with_good_book_count
\& \->all;
.Ve
.PP
This assumes that the Book resultset has \f(CW\*(C`good\*(C'\fR and \f(CW\*(C`bad\*(C'\fR methods.
.PP
See \*(L"\s-1NOTE\*(R"\s0 in DBIx::Class::Helper::ResultSet for a nice way to apply it to
your entire schema.
.SH "METHODS"
.IX Header "METHODS"
.SS "correlate"
.IX Subsection "correlate"
.Vb 1
\& $rs\->correlate($relationship_name)
.Ve
.PP
Correlate takes a single argument, a relationship for the invocant, and returns
a resultset that can be used in the selector list.
.SH "EXAMPLES"
.IX Header "EXAMPLES"
.SS "counting \s-1CD\s0's and Tracks of Artists"
.IX Subsection "counting CD's and Tracks of Artists"
If you had an Artist ResultSet and you wanted to count the tracks and \s-1CD\s0's per
Artist, here is a recipe that will work:
.PP
.Vb 2
\& sub with_track_count {
\& my $self = shift;
\&
\& $self\->search(undef, {
\& \*(Aq+columns\*(Aq => {
\& track_count => $self\->correlate(\*(Aqcds\*(Aq)
\& \->related_resultset(\*(Aqtracks\*(Aq)
\& \->count_rs
\& \->as_query
\& }
\& });
\& }
\&
\& sub with_cd_count {
\& my $self = shift;
\&
\& $self\->search(undef, {
\& \*(Aq+columns\*(Aq => {
\& cd_count => $self\->correlate(\*(Aqcds\*(Aq)
\& \->count_rs
\& \->as_query
\& }
\& });
\& }
\&
\& # elsewhere
\&
\& my @artists = $artists\->with_cd_count\->with_track_count\->all;
.Ve
.PP
Note that the following will \fBnot\fR work:
.PP
.Vb 2
\& sub BUSTED_with_track_count {
\& my $self = shift;
\&
\& $self\->search(undef, {
\& \*(Aq+columns\*(Aq => {
\& track_count => $self\->related_resultset(\*(Aqcds\*(Aq)
\& \->correlate(\*(Aqtracks\*(Aq)
\& \->count_rs
\& \->as_query
\& }
\& });
\& }
.Ve
.PP
The above is broken because \f(CW\*(C`correlate\*(C'\fR 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 \f(CW\*(C`tracks\*(C'\fR relationship is on the \f(CW\*(C`cds\*(C'\fR ResultSet,
whereas the query is for the Artist ResultSet, so the correlation will be
\&\*(L"broken\*(R" by effectively \*(L"joining\*(R" to columns that are not in the current scope.
.PP
For the same reason, the following will also not work:
.PP
.Vb 2
\& sub BUSTED2_with_track_count {
\& my $self = shift;
\&
\& $self\->search(undef, {
\& \*(Aq+columns\*(Aq => {
\& track_count => $self\->correlate(\*(Aqcds\*(Aq)
\& \->correlate(\*(Aqtracks\*(Aq)
\& \->count_rs
\& \->as_query
\& }
\& });
\& }
.Ve
.SH "SEE ALSO"
.IX Header "SEE ALSO"
.IP "\(bu" 4
Introducing DBIx::Class::Helper::ResultSet::CorrelateRelationship
.IP "\(bu" 4
Set-based DBIx::Class Advent Article
.SH "AUTHOR"
.IX Header "AUTHOR"
Arthur Axel \*(L"fREW\*(R" Schmidt
.SH "COPYRIGHT AND LICENSE"
.IX Header "COPYRIGHT AND LICENSE"
This software is copyright (c) 2020 by Arthur Axel \*(L"fREW\*(R" Schmidt.
.PP
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.