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