DBIx::Class::Helper::ResultSet::CorrelateRelationship(3pm)

1DBIx::Class::Helper::ReUDssBueIlrxt:SC:eoCtnl:ta:rsCisob:ru:rtHeeeldlaptPeeerRr:el:lRaDetosicuoulnmtseShneittpa:(t:3iC)oonrrelateRelationship(3)
2
3
4

NAME

6       DBIx::Class::Helper::ResultSet::CorrelateRelationship - Easily
7       correlate your ResultSets
8

SYNOPSIS

10        package MyApp::Schema::ResultSet::Author;
11
12        use parent 'DBIx::Class::ResultSet';
13
14        __PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship));
15
16        sub with_book_count {
17          my $self = shift;
18
19          $self->search(undef, {
20            '+columns' => {
21              book_count => $self->correlate('books')->count_rs->as_query
22            }
23          });
24        }
25
26        1;
27
28       And then elsewhere, like in a controller:
29
30        my $rows = $schema->resultset('Author')->with_book_count->all;
31

DESCRIPTION

33       Correlated queries are one of the coolest things I've learned about for
34       SQL since my initial learning of SQL.  Unfortunately they are somewhat
35       confusing.  DBIx::Class has supported doing them for a long time, but
36       generally people don't think of them because they are so rare.  I won't
37       go through all the details of how they work and cool things you can do
38       with them, but here are a couple high level things you can use them for
39       to save you time or effort.
40
41       If you want to select a list of authors and counts of books for each
42       author, you could use "group_by" and something like "COUNT(book.id)",
43       but then you'd need to make your select list match your "group_by" and
44       it would just be a hassle forever after that.  The "SYNOPSIS" is a
45       perfect example of how to implement this.
46
47       If you want to select a list of authors and two separate kinds of
48       counts of books for each author, as far as I know, you must use a
49       correlated subquery in DBIx::Class.  Here is an example of how you
50       might do that:
51
52        package MyApp::Schema::ResultSet::Author;
53
54        use parent 'DBIx::Class::ResultSet';
55
56        __PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship));
57
58        sub with_good_book_count {
59          my $self = shift;
60
61          $self->search(undef, {
62            '+columns' => {
63              good_book_count => $self->correlate('books')->good->count_rs->as_query
64            }
65          });
66        }
67
68        sub with_bad_book_count {
69          my $self = shift;
70
71          $self->search(undef, {
72            '+columns' => {
73              bad_book_count => $self->correlate('books')->bad->count_rs->as_query
74            }
75          });
76        }
77
78        1;
79
80       And then elsewhere, like in a controller:
81
82        my $rows = $schema->resultset('Author')
83          ->with_bad_book_count
84          ->with_good_book_count
85          ->all;
86
87       This assumes that the Book resultset has "good" and "bad" methods.
88
89       See "NOTE" in DBIx::Class::Helper::ResultSet for a nice way to apply it
90       to your entire schema.
91

METHODS

93   correlate
94        $rs->correlate($relationship_name)
95
96       Correlate takes a single argument, a relationship for the invocant, and
97       returns a resultset that can be used in the selector list.
98

EXAMPLES

100   counting CD's and Tracks of Artists
101       If you had an Artist ResultSet and you wanted to count the tracks and
102       CD's per Artist, here is a recipe that will work:
103
104        sub with_track_count {
105          my $self = shift;
106
107          $self->search(undef, {
108            '+columns' => {
109              track_count => $self->correlate('cds')
110                ->related_resultset('tracks')
111                ->count_rs
112                ->as_query
113            }
114          });
115        }
116
117        sub with_cd_count {
118          my $self = shift;
119
120          $self->search(undef, {
121            '+columns' => {
122              cd_count => $self->correlate('cds')
123                ->count_rs
124                ->as_query
125            }
126          });
127        }
128
129        # elsewhere
130
131        my @artists = $artists->with_cd_count->with_track_count->all;
132
133       Note that the following will not work:
134
135        sub BUSTED_with_track_count {
136          my $self = shift;
137
138          $self->search(undef, {
139            '+columns' => {
140              track_count => $self->related_resultset('cds')
141                ->correlate('tracks')
142                ->count_rs
143                ->as_query
144            }
145          });
146        }
147
148       The above is broken because "correlate" returns a fresh resultset that
149       will only work as a subquery to the ResultSet it was chained off of.
150       The upshot of that is that the above "tracks" relationship is on the
151       "cds" ResultSet, whereas the query is for the Artist ResultSet, so the
152       correlation will be "broken" by effectively "joining" to columns that
153       are not in the current scope.
154
155       For the same reason, the following will also not work:
156
157        sub BUSTED2_with_track_count {
158          my $self = shift;
159
160          $self->search(undef, {
161            '+columns' => {
162              track_count => $self->correlate('cds')
163                ->correlate('tracks')
164                ->count_rs
165                ->as_query
166            }
167          });
168        }
169

AUTHOR

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

COPYRIGHT AND LICENSE

182       This software is copyright (c) 2020 by Arthur Axel "fREW" Schmidt.
183
184       This is free software; you can redistribute it and/or modify it under
185       the same terms as the Perl 5 programming language system itself.
186
187
188
189perl v5.34.0          DBIx::Class:2:0H2e1l-p0e7r-:2:2ResultSet::CorrelateRelationship(3)