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