1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
package DBIx::Class::Helper::ResultSet::CorrelateRelationship;
{
$DBIx::Class::Helper::ResultSet::CorrelateRelationship::VERSION = '2.013002';
}
use strict;
use warnings;
use base 'DBIx::Class::ResultSet';
# ABSTRACT: Easily correlate your ResultSets
sub correlate {
my ($self, $rel) = @_;
my $source = $self->result_source;
my $rel_info = $source->relationship_info($rel);
return $source->related_source($rel)->resultset
->search(scalar $source->_resolve_condition(
$rel_info->{cond},
"${rel}_alias",
$self->current_source_alias,
$rel
), {
alias => "${rel}_alias",
})
}
1;
__END__
=pod
=head1 NAME
DBIx::Class::Helper::ResultSet::CorrelateRelationship - Easily correlate your ResultSets
=head1 VERSION
version 2.013002
=head1 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;
=head1 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.
L<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<could> use C<group_by> and something like C<COUNT(book.id)>, but then
you'd need to make your select list match your C<group_by> and it would just
be a hassle forever after that. The L</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<must> use a correlated subquery
in L<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 C<good> and C<bad> methods.
See L<DBIx::Class::Helper::ResultSet/NOTE> for a nice way to apply it to
your entire schema.
=head1 METHODS
=head2 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.
=head1 AUTHOR
Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
=head1 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.
=cut
|
