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