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