Commit | Line | Data |
a04af85f |
1 | # Test to ensure we get a consistent result set wether or not we use the |
2 | # prefetch option in combination rows (LIMIT). |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
61fd5dfc |
7 | use Test::Exception; |
a04af85f |
8 | use lib qw(t/lib); |
9 | use DBICTest; |
53c29913 |
10 | use DBIC::SqlMakerTest; |
fcb7fcbb |
11 | use DBIx::Class::SQLMaker::LimitDialects; |
abbbc000 |
12 | use Data::Query::ExprDeclare; |
fcb7fcbb |
13 | |
14 | my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype; |
a04af85f |
15 | |
a04af85f |
16 | my $schema = DBICTest->init_schema(); |
51a296b4 |
17 | |
18 | |
a04af85f |
19 | my $no_prefetch = $schema->resultset('Artist')->search( |
51a296b4 |
20 | [ # search deliberately contrived |
21 | { 'artwork.cd_id' => undef }, |
22 | { 'tracks.title' => { '!=' => 'blah-blah-1234568' }} |
23 | ], |
96faafb4 |
24 | { rows => 3, join => { cds => [qw/artwork tracks/] }, |
25 | } |
26 | ); |
27 | |
28 | my $use_prefetch = $no_prefetch->search( |
29 | {}, |
25cac750 |
30 | { |
c9d29bb2 |
31 | select => ['me.artistid', 'me.name'], |
32 | as => ['artistid', 'name'], |
25cac750 |
33 | prefetch => 'cds', |
51a296b4 |
34 | order_by => { -desc => 'name' }, |
25cac750 |
35 | } |
a04af85f |
36 | ); |
37 | |
f7f53a89 |
38 | # add an extra +select to make sure it does not throw things off |
36fd7f07 |
39 | # we also expect it to appear in both selectors, as we can not know |
40 | # for sure which part of the query it applies to (may be order_by, |
41 | # maybe something else) |
42 | # |
43 | # we use a reference to the same array in bind vals, because |
44 | # is_deeply picks up this difference too (not sure if bug or |
45 | # feature) |
36fd7f07 |
46 | $use_prefetch = $use_prefetch->search({}, { |
f7f53a89 |
47 | '+columns' => { monkeywrench => \[ 'me.artistid + ?', [ \ 'inTEger' => 1 ] ] }, |
36fd7f07 |
48 | }); |
49 | |
0e773352 |
50 | my $bind_int_resolved = sub { [ { sqlt_datatype => 'inTEger' } => 1 ] }; |
51 | my $bind_vc_resolved = sub { [ |
52 | { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' } |
53 | => 'blah-blah-1234568' |
54 | ] }; |
36fd7f07 |
55 | is_same_sql_bind ( |
56 | $use_prefetch->as_query, |
57 | '( |
f7f53a89 |
58 | SELECT me.artistid + ?, |
59 | me.artistid, me.name, |
60 | cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track |
36fd7f07 |
61 | FROM ( |
f7f53a89 |
62 | SELECT me.artistid + ?, |
63 | me.artistid, me.name |
36fd7f07 |
64 | FROM artist me |
65 | LEFT JOIN cd cds |
66 | ON cds.artist = me.artistid |
67 | LEFT JOIN cd_artwork artwork |
68 | ON artwork.cd_id = cds.cdid |
69 | LEFT JOIN track tracks |
70 | ON tracks.cd = cds.cdid |
71 | WHERE artwork.cd_id IS NULL |
72 | OR tracks.title != ? |
f7f53a89 |
73 | GROUP BY me.artistid + ?, me.artistid, me.name |
fcb7fcbb |
74 | ORDER BY name DESC LIMIT ? |
36fd7f07 |
75 | ) me |
76 | LEFT JOIN cd cds |
77 | ON cds.artist = me.artistid |
78 | LEFT JOIN cd_artwork artwork |
79 | ON artwork.cd_id = cds.cdid |
80 | LEFT JOIN track tracks |
81 | ON tracks.cd = cds.cdid |
82 | WHERE artwork.cd_id IS NULL |
83 | OR tracks.title != ? |
908aa1bb |
84 | ORDER BY name DESC |
36fd7f07 |
85 | )', |
86 | [ |
0e773352 |
87 | $bind_int_resolved->(), # outer select |
88 | $bind_int_resolved->(), # inner select |
89 | $bind_vc_resolved->(), # inner where |
90 | $bind_int_resolved->(), # inner group_by |
fcb7fcbb |
91 | [ $ROWS => 3 ], |
0e773352 |
92 | $bind_vc_resolved->(), # outer where |
36fd7f07 |
93 | ], |
94 | 'Expected SQL on complex limited prefetch' |
95 | ); |
96 | |
a04af85f |
97 | is($no_prefetch->count, $use_prefetch->count, '$no_prefetch->count == $use_prefetch->count'); |
5624ba1f |
98 | is( |
9117ccfb |
99 | scalar ($no_prefetch->all), |
100 | scalar ($use_prefetch->all), |
101 | "Amount of returned rows is right" |
5624ba1f |
102 | ); |
103 | |
9117ccfb |
104 | my $artist_many_cds = $schema->resultset('Artist')->search ( {}, { |
105 | join => 'cds', |
106 | group_by => 'me.artistid', |
107 | having => \ 'count(cds.cdid) > 1', |
108 | })->first; |
109 | |
110 | |
5624ba1f |
111 | $no_prefetch = $schema->resultset('Artist')->search( |
9117ccfb |
112 | { artistid => $artist_many_cds->id }, |
5624ba1f |
113 | { rows => 1 } |
114 | ); |
115 | |
61fd5dfc |
116 | $use_prefetch = $no_prefetch->search ({}, { prefetch => 'cds' }); |
5624ba1f |
117 | |
61fd5dfc |
118 | my $normal_artist = $no_prefetch->single; |
119 | my $prefetch_artist = $use_prefetch->find({ name => $artist_many_cds->name }); |
120 | my $prefetch2_artist = $use_prefetch->first; |
5624ba1f |
121 | |
122 | is( |
123 | $prefetch_artist->cds->count, |
124 | $normal_artist->cds->count, |
61fd5dfc |
125 | "Count of child rel with prefetch + rows => 1 is right (find)" |
126 | ); |
127 | is( |
128 | $prefetch2_artist->cds->count, |
129 | $normal_artist->cds->count, |
130 | "Count of child rel with prefetch + rows => 1 is right (first)" |
5624ba1f |
131 | ); |
61fd5dfc |
132 | |
9117ccfb |
133 | is ( |
134 | scalar ($prefetch_artist->cds->all), |
135 | scalar ($normal_artist->cds->all), |
61fd5dfc |
136 | "Amount of child rel rows with prefetch + rows => 1 is right (find)" |
137 | ); |
138 | is ( |
139 | scalar ($prefetch2_artist->cds->all), |
140 | scalar ($normal_artist->cds->all), |
141 | "Amount of child rel rows with prefetch + rows => 1 is right (first)" |
142 | ); |
143 | |
144 | throws_ok ( |
145 | sub { $use_prefetch->single }, |
5b309063 |
146 | qr/\Qsingle() can not be used on resultsets collapsing a has_many/, |
61fd5dfc |
147 | 'single() with multiprefetch is illegal', |
9117ccfb |
148 | ); |
01c781fe |
149 | |
14e26c5f |
150 | throws_ok ( |
151 | sub { |
152 | $use_prefetch->search( |
153 | {'tracks.title' => { '!=' => 'foo' }}, |
154 | { order_by => \ 'some oddball literal sql', join => { cds => 'tracks' } } |
155 | )->next |
156 | }, qr/A required group_by clause could not be constructed automatically/, |
eb58c082 |
157 | ) || exit; |
14e26c5f |
158 | |
abbbc000 |
159 | my $artist = $use_prefetch->search(expr { $_->cds->title eq $artist_many_cds->cds->first->title })->next; |
01c781fe |
160 | is($artist->cds->count, 1, "count on search limiting prefetched has_many"); |
161 | |
a3683eae |
162 | # try with double limit |
163 | my $artist2 = $use_prefetch->search({'cds.title' => { '!=' => $artist_many_cds->cds->first->title } })->slice (0,0)->next; |
164 | is($artist2->cds->count, 2, "count on search limiting prefetched has_many"); |
165 | |
53c29913 |
166 | # make sure 1:1 joins do not force a subquery (no point to exercise the optimizer, if at all available) |
167 | # get cd's that have any tracks and their artists |
168 | my $single_prefetch_rs = $schema->resultset ('CD')->search ( |
169 | { 'me.year' => 2010, 'artist.name' => 'foo' }, |
170 | { prefetch => ['tracks', 'artist'], rows => 15 }, |
171 | ); |
172 | is_same_sql_bind ( |
173 | $single_prefetch_rs->as_query, |
174 | '( |
175 | SELECT |
176 | me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
3d98c75e |
177 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, |
53c29913 |
178 | artist.artistid, artist.name, artist.rank, artist.charfield |
179 | FROM ( |
180 | SELECT |
181 | me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
182 | FROM cd me |
183 | JOIN artist artist ON artist.artistid = me.artist |
184 | WHERE ( ( artist.name = ? AND me.year = ? ) ) |
fcb7fcbb |
185 | LIMIT ? |
53c29913 |
186 | ) me |
187 | LEFT JOIN track tracks |
188 | ON tracks.cd = me.cdid |
189 | JOIN artist artist |
190 | ON artist.artistid = me.artist |
191 | WHERE ( ( artist.name = ? AND me.year = ? ) ) |
53c29913 |
192 | )', |
fcb7fcbb |
193 | [ |
194 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ], |
195 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ], |
196 | [ $ROWS => 15 ], |
197 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ], |
198 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ], |
199 | ], |
53c29913 |
200 | 'No grouping of non-multiplying resultsets', |
201 | ); |
202 | |
eb58c082 |
203 | my $many_one_many_rs = $schema->resultset('CD')->search({}, { |
204 | prefetch => { tracks => { lyrics => 'lyric_versions' } }, |
205 | rows => 2, |
206 | order_by => ['lyrics.track_id'], |
207 | }); |
208 | |
209 | is_same_sql_bind( |
210 | $many_one_many_rs->as_query, |
211 | '( |
212 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
213 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, |
214 | lyrics.lyric_id, lyrics.track_id, lyric_versions.id, lyric_versions.lyric_id, lyric_versions.text |
215 | FROM ( |
216 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
217 | FROM cd me |
218 | LEFT JOIN track tracks |
219 | ON tracks.cd = me.cdid |
220 | LEFT JOIN lyrics lyrics |
221 | ON lyrics.track_id = tracks.trackid |
222 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
223 | ORDER BY MIN(lyrics.track_id) |
224 | LIMIT ? |
225 | ) me |
226 | LEFT JOIN track tracks |
227 | ON tracks.cd = me.cdid |
228 | LEFT JOIN lyrics lyrics |
229 | ON lyrics.track_id = tracks.trackid |
230 | LEFT JOIN lyric_versions lyric_versions |
231 | ON lyric_versions.lyric_id = lyrics.lyric_id |
232 | ORDER BY lyrics.track_id |
233 | )', |
234 | [ |
235 | [ { sqlt_datatype => 'integer' } => 2 ] |
236 | ], |
237 | 'Correct SQL on indirectly multiplied orderer', |
238 | ); |
239 | |
240 | my $cond_on_multi_ord_by_single = $schema->resultset('CD')->search( |
241 | { |
242 | 'tracks.position' => { '!=', 1 }, |
243 | }, |
244 | { |
245 | prefetch => [qw( tracks artist )], |
246 | order_by => 'artist.name', |
247 | rows => 1, |
248 | }, |
249 | ); |
250 | |
251 | is_same_sql_bind( |
252 | $cond_on_multi_ord_by_single->as_query, |
253 | '( |
254 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
255 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, |
256 | artist.artistid, artist.name, artist.rank, artist.charfield |
257 | FROM ( |
258 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
259 | FROM cd me |
260 | LEFT JOIN track tracks |
261 | ON tracks.cd = me.cdid |
262 | JOIN artist artist |
263 | ON artist.artistid = me.artist |
264 | WHERE tracks.position != ? |
265 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, artist.name |
266 | ORDER BY artist.name |
267 | LIMIT ? |
268 | ) me |
269 | LEFT JOIN track tracks |
270 | ON tracks.cd = me.cdid |
271 | JOIN artist artist |
272 | ON artist.artistid = me.artist |
273 | WHERE tracks.position != ? |
274 | ORDER BY artist.name |
275 | )', |
276 | [ |
277 | [ { dbic_colname => "tracks.position", sqlt_datatype => "int" } |
278 | => 1 |
279 | ], |
280 | [ { sqlt_datatype => "integer" } |
281 | => 1 |
282 | ], |
283 | [ { dbic_colname => "tracks.position", sqlt_datatype => "int" } |
284 | => 1 |
285 | ], |
286 | ], |
287 | 'Correct SQl on prefetch with limit of restricting multi ordered by a single' |
288 | ); |
289 | |
c9d29bb2 |
290 | done_testing; |