Commit | Line | Data |
c0329273 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
2 | |
0c5ea449 |
3 | use strict; |
4 | use warnings; |
6841b059 |
5 | |
0c5ea449 |
6 | use Test::More; |
7 | |
a5a7bb73 |
8 | use DBICTest ':DiffSQL'; |
fcb7fcbb |
9 | use DBIx::Class::SQLMaker::LimitDialects; |
10 | |
11 | my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype; |
560978e2 |
12 | my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype; |
0c5ea449 |
13 | |
0c5ea449 |
14 | my $schema = DBICTest->init_schema(); |
15 | |
16 | my $cd_rs = $schema->resultset('CD')->search ( |
17 | { 'tracks.cd' => { '!=', undef } }, |
18 | { prefetch => 'tracks' }, |
19 | ); |
20 | |
21 | # Database sanity check |
22 | is($cd_rs->count, 5, 'CDs with tracks count'); |
23 | for ($cd_rs->all) { |
24 | is ($_->tracks->count, 3, '3 tracks for CD' . $_->id ); |
25 | } |
26 | |
1b658919 |
27 | my @cdids = sort $cd_rs->get_column ('cdid')->all; |
28 | |
0c5ea449 |
29 | # Test a belongs_to prefetch of a has_many |
30 | { |
31 | my $track_rs = $schema->resultset ('Track')->search ( |
1b658919 |
32 | { 'me.cd' => { -in => \@cdids } }, |
0c5ea449 |
33 | { |
0c5ea449 |
34 | select => [ |
35 | 'me.cd', |
0c5ea449 |
36 | { count => 'me.trackid' }, |
37 | ], |
38 | as => [qw/ |
39 | cd |
40 | track_count |
0c5ea449 |
41 | /], |
42 | group_by => [qw/me.cd/], |
43 | prefetch => 'cd', |
44 | }, |
45 | ); |
46 | |
22ed9526 |
47 | # this used to fuck up ->all, do not remove! |
48 | ok ($track_rs->first, 'There is stuff in the rs'); |
49 | |
0c5ea449 |
50 | is($track_rs->count, 5, 'Prefetched count with groupby'); |
51 | is($track_rs->all, 5, 'Prefetched objects with groupby'); |
52 | |
49eeb48d |
53 | $schema->is_executed_querycount( sub { |
0c5ea449 |
54 | while (my $collapsed_track = $track_rs->next) { |
0c5ea449 |
55 | my $cdid = $collapsed_track->get_column('cd'); |
56 | is($collapsed_track->get_column('track_count'), 3, "Correct count of tracks for CD $cdid" ); |
57 | ok($collapsed_track->cd->title, "Prefetched title for CD $cdid" ); |
58 | } |
49eeb48d |
59 | }, 1, 'Single query on prefetched titles'); |
0c5ea449 |
60 | |
61 | # Test sql by hand, as the sqlite db will simply paper over |
62 | # improper group/select combinations |
63 | # |
0c5ea449 |
64 | is_same_sql_bind ( |
65 | $track_rs->count_rs->as_query, |
66 | '( |
67 | SELECT COUNT( * ) |
68 | FROM ( |
69 | SELECT me.cd |
70 | FROM track me |
71 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
72 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
73 | GROUP BY me.cd |
74 | ) |
336feb8e |
75 | me |
0c5ea449 |
76 | )', |
0e773352 |
77 | [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' } |
1b658919 |
78 | => $_ ] } @cdids ], |
0c5ea449 |
79 | 'count() query generated expected SQL', |
80 | ); |
81 | |
0c5ea449 |
82 | is_same_sql_bind ( |
83 | $track_rs->as_query, |
84 | '( |
5b45001f |
85 | SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track |
0c5ea449 |
86 | FROM ( |
0491b597 |
87 | SELECT me.cd, COUNT (me.trackid) AS track_count |
0c5ea449 |
88 | FROM track me |
1c1937b7 |
89 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
90 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
91 | GROUP BY me.cd |
0491b597 |
92 | ) me |
0c5ea449 |
93 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
94 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
95 | )', |
0e773352 |
96 | [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' } |
1b658919 |
97 | => $_ ] } (@cdids) x 2 ], |
0c5ea449 |
98 | 'next() query generated expected SQL', |
99 | ); |
100 | |
101 | |
102 | # add an extra track to one of the cds, and then make sure we can get it on top |
103 | # (check if limit works) |
104 | my $top_cd = $cd_rs->slice (1,1)->next; |
105 | $top_cd->create_related ('tracks', { |
106 | title => 'over the top', |
107 | }); |
108 | |
109 | my $top_cd_collapsed_track = $track_rs->search ({}, { |
110 | rows => 2, |
111 | order_by => [ |
112 | { -desc => 'track_count' }, |
113 | ], |
114 | }); |
115 | |
116 | is ($top_cd_collapsed_track->count, 2); |
117 | |
118 | is ( |
119 | $top_cd->title, |
120 | $top_cd_collapsed_track->first->cd->title, |
121 | 'Correct collapsed track with prefetched CD returned on top' |
122 | ); |
123 | } |
124 | |
125 | # test a has_many/might_have prefetch at the same level |
0c5ea449 |
126 | # Note that one of the CDs now has 4 tracks instead of 3 |
127 | { |
dace9819 |
128 | my $most_tracks_rs = $schema->resultset ('CD')->search ( |
129 | { |
130 | 'me.cdid' => { '!=' => undef }, # duh - this is just to test WHERE |
131 | }, |
132 | { |
133 | prefetch => [qw/tracks liner_notes/], |
50136dd9 |
134 | select => ['me.cdid', { count => 'tracks.trackid' }, { max => 'tracks.trackid', -as => 'maxtr'} ], |
135 | as => [qw/cdid track_count max_track_id/], |
dace9819 |
136 | group_by => 'me.cdid', |
50136dd9 |
137 | order_by => [ { -desc => 'track_count' }, { -asc => 'maxtr' } ], |
dace9819 |
138 | rows => 2, |
139 | } |
140 | ); |
0c5ea449 |
141 | |
142 | is_same_sql_bind ( |
143 | $most_tracks_rs->count_rs->as_query, |
144 | '( |
145 | SELECT COUNT( * ) |
146 | FROM ( |
147 | SELECT me.cdid |
148 | FROM cd me |
dace9819 |
149 | WHERE ( me.cdid IS NOT NULL ) |
0c5ea449 |
150 | GROUP BY me.cdid |
fcb7fcbb |
151 | LIMIT ? |
336feb8e |
152 | ) me |
0c5ea449 |
153 | )', |
fcb7fcbb |
154 | [[$ROWS => 2]], |
0c5ea449 |
155 | 'count() query generated expected SQL', |
156 | ); |
157 | |
158 | is_same_sql_bind ( |
159 | $most_tracks_rs->as_query, |
160 | '( |
dc81dba3 |
161 | SELECT me.cdid, me.track_count, me.maxtr, |
3d98c75e |
162 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, |
8bc3fbf5 |
163 | liner_notes.liner_id, liner_notes.notes |
0c5ea449 |
164 | FROM ( |
dc81dba3 |
165 | SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr |
0c5ea449 |
166 | FROM cd me |
167 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
dace9819 |
168 | WHERE ( me.cdid IS NOT NULL ) |
0c5ea449 |
169 | GROUP BY me.cdid |
50136dd9 |
170 | ORDER BY track_count DESC, maxtr ASC |
fcb7fcbb |
171 | LIMIT ? |
0c5ea449 |
172 | ) me |
173 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
174 | LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid |
dace9819 |
175 | WHERE ( me.cdid IS NOT NULL ) |
908aa1bb |
176 | ORDER BY track_count DESC, maxtr ASC |
0c5ea449 |
177 | )', |
fcb7fcbb |
178 | [[$ROWS => 2]], |
0c5ea449 |
179 | 'next() query generated expected SQL', |
180 | ); |
181 | |
182 | is ($most_tracks_rs->count, 2, 'Limit works'); |
69e99ee6 |
183 | my ($top_cd) = $most_tracks_rs->all; |
22ed9526 |
184 | is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier |
0c5ea449 |
185 | |
49eeb48d |
186 | $schema->is_executed_querycount( sub { |
187 | is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly'); |
188 | is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct'); |
189 | is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct'); |
190 | is ( |
191 | $top_cd->liner_notes->notes, |
192 | 'Buy Whiskey!', |
193 | 'Correct liner pre-fetched with top cd', |
194 | ); |
195 | }, 0, 'No queries executed during prefetched data access'); |
0c5ea449 |
196 | } |
f785d72e |
197 | |
1e4f9fb3 |
198 | { |
199 | # test lifted from soulchild |
200 | |
201 | my $most_tracks_rs = $schema->resultset ('CD')->search ( |
202 | { |
203 | 'me.cdid' => { '!=' => undef }, # this is just to test WHERE |
204 | 'tracks.trackid' => { '!=' => undef }, |
205 | }, |
206 | { |
207 | join => 'tracks', |
208 | prefetch => 'liner_notes', |
209 | select => ['me.cdid', 'liner_notes.notes', { count => 'tracks.trackid', -as => 'tr_count' }, { max => 'tracks.trackid', -as => 'tr_maxid'} ], |
210 | as => [qw/cdid notes track_count max_track_id/], |
211 | order_by => [ { -desc => 'tr_count' }, { -asc => 'tr_maxid' } ], |
212 | group_by => 'me.cdid', |
213 | rows => 2, |
214 | } |
215 | ); |
216 | |
217 | is_same_sql_bind( |
218 | $most_tracks_rs->as_query, |
219 | '(SELECT me.cdid, liner_notes.notes, me.tr_count, me.tr_maxid, |
220 | liner_notes.liner_id, liner_notes.notes |
221 | FROM ( |
222 | SELECT me.cdid, COUNT(tracks.trackid) AS tr_count, MAX(tracks.trackid) AS tr_maxid |
223 | FROM cd me |
224 | LEFT JOIN track tracks |
225 | ON tracks.cd = me.cdid |
226 | WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL |
227 | GROUP BY me.cdid |
228 | ORDER BY tr_count DESC, tr_maxid ASC |
229 | LIMIT ? |
230 | ) me |
231 | LEFT JOIN track tracks |
232 | ON tracks.cd = me.cdid |
233 | LEFT JOIN liner_notes liner_notes |
234 | ON liner_notes.liner_id = me.cdid |
235 | WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL |
236 | ORDER BY tr_count DESC, tr_maxid ASC |
237 | )', |
238 | [[$ROWS => 2]], |
239 | 'Oddball mysql-ish group_by usage yields valid SQL', |
240 | ); |
241 | |
242 | is ($most_tracks_rs->count, 2, 'Limit works'); |
243 | my ($top_cd) = $most_tracks_rs->all; |
244 | is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier |
245 | |
49eeb48d |
246 | $schema->is_executed_querycount( sub { |
247 | is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly'); |
248 | is ( |
249 | $top_cd->liner_notes->notes, |
250 | 'Buy Whiskey!', |
251 | 'Correct liner pre-fetched with top cd', |
252 | ); |
253 | }, 0, 'No queries executed during prefetched data access'); |
1e4f9fb3 |
254 | } |
255 | |
256 | |
f785d72e |
257 | # make sure that distinct still works |
258 | { |
259 | my $rs = $schema->resultset("CD")->search({}, { |
260 | prefetch => 'tags', |
261 | order_by => 'cdid', |
262 | distinct => 1, |
263 | }); |
264 | |
265 | is_same_sql_bind ( |
266 | $rs->as_query, |
267 | '( |
268 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
d59eba65 |
269 | tags.tagid, tags.cd, tags.tag |
f785d72e |
270 | FROM ( |
271 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
272 | FROM cd me |
d955e938 |
273 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
f785d72e |
274 | ) me |
275 | LEFT JOIN tags tags ON tags.cd = me.cdid |
908aa1bb |
276 | ORDER BY cdid |
f785d72e |
277 | )', |
278 | [], |
279 | 'Prefetch + distinct resulted in correct group_by', |
280 | ); |
281 | |
282 | is ($rs->all, 5, 'Correct number of CD objects'); |
283 | is ($rs->count, 5, 'Correct count of CDs'); |
284 | } |
6841b059 |
285 | |
ffad45f5 |
286 | # RT 47779, test group_by as a scalar ref |
287 | { |
288 | my $track_rs = $schema->resultset ('Track')->search ( |
1b658919 |
289 | { 'me.cd' => { -in => \@cdids } }, |
ffad45f5 |
290 | { |
291 | select => [ |
292 | 'me.cd', |
293 | { count => 'me.trackid' }, |
294 | ], |
295 | as => [qw/ |
296 | cd |
297 | track_count |
298 | /], |
299 | group_by => \'SUBSTR(me.cd, 1, 1)', |
300 | prefetch => 'cd', |
301 | }, |
302 | ); |
303 | |
304 | is_same_sql_bind ( |
305 | $track_rs->count_rs->as_query, |
306 | '( |
307 | SELECT COUNT( * ) |
308 | FROM ( |
efd7c5e5 |
309 | SELECT SUBSTR(me.cd, 1, 1) |
ffad45f5 |
310 | FROM track me |
311 | JOIN cd cd ON cd.cdid = me.cd |
312 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
313 | GROUP BY SUBSTR(me.cd, 1, 1) |
314 | ) |
336feb8e |
315 | me |
ffad45f5 |
316 | )', |
0e773352 |
317 | [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' } |
1b658919 |
318 | => $_ ] } (@cdids) ], |
ffad45f5 |
319 | 'count() query generated expected SQL', |
320 | ); |
321 | } |
322 | |
8423891f |
323 | { |
d8ce00f5 |
324 | my $cd_rs = $schema->resultset('CD')->search({}, { |
8423891f |
325 | distinct => 1, |
326 | join => [qw/ tracks /], |
327 | prefetch => [qw/ artist /], |
328 | }); |
d8ce00f5 |
329 | is($cd_rs->count, 5, 'complex prefetch + non-prefetching has_many join count correct'); |
330 | is($cd_rs->all, 5, 'complex prefetch + non-prefetching has_many join number of objects correct'); |
331 | |
7cc643b4 |
332 | # make sure join tracks was thrown out |
333 | is_same_sql_bind ( |
334 | $cd_rs->as_query, |
335 | '( |
336 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
337 | artist.artistid, artist.name, artist.rank, artist.charfield |
338 | FROM ( |
339 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
340 | FROM cd me |
341 | JOIN artist artist ON artist.artistid = me.artist |
342 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
343 | ) me |
344 | JOIN artist artist ON artist.artistid = me.artist |
345 | )', |
346 | [], |
347 | ); |
348 | |
349 | |
350 | |
d8ce00f5 |
351 | # try the same as above, but add a condition so the tracks join can not be thrown away |
352 | my $cd_rs2 = $cd_rs->search ({ 'tracks.title' => { '!=' => 'ugabuganoexist' } }); |
353 | is($cd_rs2->count, 5, 'complex prefetch + non-prefetching restricted has_many join count correct'); |
354 | is($cd_rs2->all, 5, 'complex prefetch + non-prefetching restricted has_many join number of objects correct'); |
355 | |
356 | # the outer group_by seems like a necessary evil, if someone can figure out how to take it away |
357 | # without breaking compat - be my guest |
358 | is_same_sql_bind ( |
359 | $cd_rs2->as_query, |
360 | '( |
361 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
362 | artist.artistid, artist.name, artist.rank, artist.charfield |
363 | FROM ( |
364 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
365 | FROM cd me |
366 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
367 | JOIN artist artist ON artist.artistid = me.artist |
368 | WHERE ( tracks.title != ? ) |
369 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
370 | ) me |
371 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
372 | JOIN artist artist ON artist.artistid = me.artist |
373 | WHERE ( tracks.title != ? ) |
374 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
375 | artist.artistid, artist.name, artist.rank, artist.charfield |
376 | )', |
0e773352 |
377 | [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' } |
378 | => 'ugabuganoexist' ] } (1,2) |
379 | ], |
d8ce00f5 |
380 | ); |
8423891f |
381 | } |
382 | |
eb58c082 |
383 | # make sure distinct applies to the CD part only, not to the prefetched/collapsed order_by part |
d59eba65 |
384 | { |
560978e2 |
385 | my $rs = $schema->resultset('CD')->search({}, { |
386 | columns => [qw( cdid title )], |
387 | '+select' => [{ count => 'tags.tag' }], |
388 | '+as' => ['test_count'], |
389 | prefetch => ['tags'], |
390 | distinct => 1, |
391 | order_by => {'-desc' => 'tags.tag'}, |
392 | offset => 1, |
393 | rows => 3, |
394 | }); |
395 | |
396 | is_same_sql_bind($rs->as_query, |
397 | '( |
398 | SELECT me.cdid, me.title, me.test_count, |
399 | tags.tagid, tags.cd, tags.tag |
400 | FROM ( |
401 | SELECT me.cdid, me.title, |
402 | COUNT( tags.tag ) AS test_count |
403 | FROM cd me |
404 | LEFT JOIN tags tags |
405 | ON tags.cd = me.cdid |
406 | GROUP BY me.cdid, me.title |
407 | ORDER BY MAX( tags.tag ) DESC |
408 | LIMIT ? |
409 | OFFSET ? |
410 | ) me |
411 | LEFT JOIN tags tags |
412 | ON tags.cd = me.cdid |
413 | ORDER BY tags.tag DESC |
414 | )', |
415 | [ [$ROWS => 3], [$OFFSET => 1] ], |
416 | 'Expected limited prefetch with distinct SQL', |
417 | ); |
418 | |
419 | my $expected_hri = [ |
420 | { cdid => 4, test_count => 2, title => "Generic Manufactured Singles", tags => [ |
421 | { cd => 4, tag => "Shiny", tagid => 9 }, |
422 | { cd => 4, tag => "Cheesy", tagid => 6 }, |
423 | ]}, |
424 | { |
425 | cdid => 5, test_count => 2, title => "Come Be Depressed With Us", tags => [ |
426 | { cd => 5, tag => "Cheesy", tagid => 7 }, |
427 | { cd => 5, tag => "Blue", tagid => 4 }, |
428 | ]}, |
429 | { |
430 | cdid => 1, test_count => 1, title => "Spoonful of bees", tags => [ |
431 | { cd => 1, tag => "Blue", tagid => 1 }, |
432 | ]}, |
433 | ]; |
434 | |
435 | is_deeply ( |
436 | $rs->all_hri, |
437 | $expected_hri, |
438 | 'HRI dump of limited prefetch with distinct as expected' |
439 | ); |
440 | |
441 | # pre-multiplied main source also should work |
442 | $rs = $schema->resultset('CD')->search_related('artist')->search_related('cds', {}, { |
443 | columns => [qw( cdid title )], |
444 | '+select' => [{ count => 'tags.tag' }], |
445 | '+as' => ['test_count'], |
446 | prefetch => ['tags'], |
447 | distinct => 1, |
448 | order_by => {'-desc' => 'tags.tag'}, |
449 | offset => 1, |
450 | rows => 3, |
451 | }); |
452 | |
453 | is_same_sql_bind($rs->as_query, |
454 | '( |
455 | SELECT cds.cdid, cds.title, cds.test_count, |
456 | tags.tagid, tags.cd, tags.tag |
457 | FROM cd me |
458 | JOIN artist artist |
459 | ON artist.artistid = me.artist |
460 | JOIN ( |
461 | SELECT cds.cdid, cds.title, |
462 | COUNT( tags.tag ) AS test_count, |
463 | cds.artist |
464 | FROM cd me |
465 | JOIN artist artist |
466 | ON artist.artistid = me.artist |
467 | JOIN cd cds |
468 | ON cds.artist = artist.artistid |
469 | LEFT JOIN tags tags |
470 | ON tags.cd = cds.cdid |
471 | GROUP BY cds.cdid, cds.title, cds.artist |
472 | ORDER BY MAX( tags.tag ) DESC |
473 | LIMIT ? |
474 | OFFSET ? |
475 | ) cds |
476 | ON cds.artist = artist.artistid |
477 | LEFT JOIN tags tags |
478 | ON tags.cd = cds.cdid |
479 | ORDER BY tags.tag DESC |
480 | )', |
481 | [ [$ROWS => 3], [$OFFSET => 1] ], |
482 | 'Expected limited prefetch with distinct SQL on premultiplied head', |
483 | ); |
484 | |
485 | # Tag counts are multiplied by the cd->artist->cds multiplication |
486 | # I would *almost* call this "expected" without wraping an as_subselect_rs |
487 | { |
488 | local $TODO = 'Not sure if we can stop the count/group of premultiplication abstraction leak'; |
489 | is_deeply ( |
490 | $rs->all_hri, |
491 | $expected_hri, |
492 | 'HRI dump of limited prefetch with distinct as expected on premultiplid head' |
d59eba65 |
493 | ); |
560978e2 |
494 | } |
d59eba65 |
495 | } |
496 | |
6841b059 |
497 | done_testing; |