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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
package DBIx::Class::Helper::ResultSet::SetOperations;
{
$DBIx::Class::Helper::ResultSet::SetOperations::VERSION = '2.013002';
}
use strict;
use warnings;
# ABSTRACT: Do set operations with DBIx::Class
# cribbed from perlfaq4
sub _compare_arrays {
my ($self, $first, $second) = @_;
no warnings; # silence spurious -w undef complaints
return 0 unless @$first == @$second;
for (my $i = 0; $i < @$first; $i++) {
return 0 if $first->[$i] ne $second->[$i];
}
return 1;
}
sub union {
shift->_set_operation( UNION => @_ );
}
sub union_all {
shift->_set_operation( "UNION ALL" => @_ );
}
sub intersect {
shift->_set_operation( INTERSECT => @_ );
}
sub intersect_all {
shift->_set_operation( "INTERSECT ALL" => @_ );
}
sub _except_keyword {
my $self = shift;
$self->{_except_keyword} ||= ( $self->result_source->schema->storage->sqlt_type eq 'Oracle' ? "MINUS" : "EXCEPT" );
}
sub except {
my ( $self, @args ) = @_;
$self->_set_operation( $self->_except_keyword => @args );
}
sub except_all {
# not supported on most DBs
shift->_set_operation( "EXCEPT ALL" => @_ );
}
sub _set_operation {
my ( $self, $operation, $other ) = @_;
my @sql;
my @params;
my $as = $self->_resolved_attrs->{as};
my @operands = ( $self, ref $other eq 'ARRAY' ? @$other : $other );
for (@operands) {
$self->throw_exception("ResultClass of ResultSets do not match!")
unless $self->result_class eq $_->result_class;
my $attrs = $_->_resolved_attrs;
$self->throw_exception('ResultSets do not all have the same selected columns!')
unless $self->_compare_arrays($as, $attrs->{as});
my ($sql, $bind) = $self->result_source->storage->_select_args_to_query(
$attrs->{from}, $attrs->{select}, $attrs->{where}, $attrs
);
push @sql, $sql;
push @params, @{$bind};
}
my $query = q<(> . join(" $operation ", @sql). q<)>;
my $attrs = $self->_resolved_attrs;
return $self->result_source->resultset->search(undef, {
alias => $self->current_source_alias,
from => [{
$self->current_source_alias => \[ $query, @params ],
-alias => $self->current_source_alias,
-source_handle => $self->result_source->handle,
}],
columns => $attrs->{as},
result_class => $self->result_class,
});
}
1;
__END__
=pod
=head1 NAME
DBIx::Class::Helper::ResultSet::SetOperations - Do set operations with DBIx::Class
=head1 VERSION
version 2.013002
=head1 SYNOPSIS
package MyApp::Schema::ResultSet::Foo;
__PACKAGE__->load_components(qw{Helper::ResultSet::SetOperations});
...
1;
And then elsewhere, like in a controller:
my $rs1 = $rs->search({ foo => 'bar' });
my $rs2 = $rs->search({ baz => 'biff' });
for ($rs1->union($rs2)->all) { ... }
=head1 DESCRIPTION
This component allows you to use various set operations with your ResultSets.
See L<DBIx::Class::Helper::ResultSet/NOTE> for a nice way to apply it to your
entire schema.
Component throws exceptions if ResultSets have different ResultClasses or
different "Columns Specs."
The basic idea here is that in SQL if you use a set operation they must be
selecting the same columns names, so that the results will all match. The deal
with the ResultClasses is that DBIC needs to inflate the results the same for
the entire ResultSet, so if one were to try to apply something like a union in
a table with the same column name but different classes DBIC wouldn't be doing
what you would expect.
A nice way to use this is with L<DBIx::Class::ResultClass::HashRefInflator>.
You might have something like the following sketch autocompletion code:
my $rs1 = $schema->resultset('Album')->search({
name => { -like => "$input%" }
}, {
columns => [qw( id name ), {
tablename => \['?', [{} => 'album']],
}],
});
my $rs2 = $schema->resultset('Artist')->search({
name => { -like => "$input%" }
}, {
columns => [qw( id name ), {
tablename => \['?', [{} => 'artist']],
}],
});
my $rs3 = $schema->resultset('Song')->search({
name => { -like => "$input%" }
}, {
columns => [qw( id name ), {
tablename => \['?', [{} => 'song']],
}],
});
$_->result_class('DBIx::Class::ResultClass::HashRefInflator')
for ($rs1, $rs2, $rs3);
my $data = [$rs1->union([$rs2, $rs3])->all];
=head1 METHODS
=head2 union
=head2 union_all
=head2 intersect
=head2 intersect_all
=head2 except
=head2 except_all
All of these methods take a single ResultSet or an ArrayRef of ResultSets as
the parameter only parameter.
On Oracle C<except> will issue a C<MINUS> operation.
=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
|
