7 use DBICTest ':DiffSQL';
8 use DBIx::Class::SQLMaker::LimitDialects;
10 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
11 my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
13 my $schema = DBICTest->init_schema();
15 my $cd_rs = $schema->resultset('CD')->search (
16 { 'tracks.cd' => { '!=', undef } },
17 { prefetch => 'tracks' },
20 # Database sanity check
21 is($cd_rs->count, 5, 'CDs with tracks count');
23 is ($_->tracks->count, 3, '3 tracks for CD' . $_->id );
26 # Test a belongs_to prefetch of a has_many
28 my $track_rs = $schema->resultset ('Track')->search (
29 { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
33 { count => 'me.trackid' },
39 group_by => [qw/me.cd/],
44 # this used to fuck up ->all, do not remove!
45 ok ($track_rs->first, 'There is stuff in the rs');
47 is($track_rs->count, 5, 'Prefetched count with groupby');
48 is($track_rs->all, 5, 'Prefetched objects with groupby');
50 $schema->is_executed_querycount( sub {
51 while (my $collapsed_track = $track_rs->next) {
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" );
56 }, 1, 'Single query on prefetched titles');
58 # Test sql by hand, as the sqlite db will simply paper over
59 # improper group/select combinations
62 $track_rs->count_rs->as_query,
68 JOIN cd cd ON cd.cdid = me.cd
69 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
74 [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
75 => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
76 'count() query generated expected SQL',
82 SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track
84 SELECT me.cd, COUNT (me.trackid) AS track_count
86 JOIN cd cd ON cd.cdid = me.cd
87 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
90 JOIN cd cd ON cd.cdid = me.cd
91 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
93 [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
94 => $_ ] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ],
95 'next() query generated expected SQL',
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',
106 my $top_cd_collapsed_track = $track_rs->search ({}, {
109 { -desc => 'track_count' },
113 is ($top_cd_collapsed_track->count, 2);
117 $top_cd_collapsed_track->first->cd->title,
118 'Correct collapsed track with prefetched CD returned on top'
122 # test a has_many/might_have prefetch at the same level
123 # Note that one of the CDs now has 4 tracks instead of 3
125 my $most_tracks_rs = $schema->resultset ('CD')->search (
127 'me.cdid' => { '!=' => undef }, # duh - this is just to test WHERE
130 prefetch => [qw/tracks liner_notes/],
131 select => ['me.cdid', { count => 'tracks.trackid' }, { max => 'tracks.trackid', -as => 'maxtr'} ],
132 as => [qw/cdid track_count max_track_id/],
133 group_by => 'me.cdid',
134 order_by => [ { -desc => 'track_count' }, { -asc => 'maxtr' } ],
140 $most_tracks_rs->count_rs->as_query,
146 WHERE ( me.cdid IS NOT NULL )
152 'count() query generated expected SQL',
156 $most_tracks_rs->as_query,
158 SELECT me.cdid, me.track_count, me.maxtr,
159 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
160 liner_notes.liner_id, liner_notes.notes
162 SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr
164 LEFT JOIN track tracks ON tracks.cd = me.cdid
165 WHERE ( me.cdid IS NOT NULL )
167 ORDER BY track_count DESC, maxtr ASC
170 LEFT JOIN track tracks ON tracks.cd = me.cdid
171 LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
172 WHERE ( me.cdid IS NOT NULL )
173 ORDER BY track_count DESC, maxtr ASC
176 'next() query generated expected SQL',
179 is ($most_tracks_rs->count, 2, 'Limit works');
180 my ($top_cd) = $most_tracks_rs->all;
181 is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
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');
188 $top_cd->liner_notes->notes,
190 'Correct liner pre-fetched with top cd',
192 }, 0, 'No queries executed during prefetched data access');
196 # test lifted from soulchild
198 my $most_tracks_rs = $schema->resultset ('CD')->search (
200 'me.cdid' => { '!=' => undef }, # this is just to test WHERE
201 'tracks.trackid' => { '!=' => undef },
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',
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
219 SELECT me.cdid, COUNT(tracks.trackid) AS tr_count, MAX(tracks.trackid) AS tr_maxid
221 LEFT JOIN track tracks
222 ON tracks.cd = me.cdid
223 WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL
225 ORDER BY tr_count DESC, tr_maxid ASC
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
236 'Oddball mysql-ish group_by usage yields valid SQL',
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
243 $schema->is_executed_querycount( sub {
244 is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
246 $top_cd->liner_notes->notes,
248 'Correct liner pre-fetched with top cd',
250 }, 0, 'No queries executed during prefetched data access');
254 # make sure that distinct still works
256 my $rs = $schema->resultset("CD")->search({}, {
265 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
266 tags.tagid, tags.cd, tags.tag
268 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
270 GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
272 LEFT JOIN tags tags ON tags.cd = me.cdid
276 'Prefetch + distinct resulted in correct group_by',
279 is ($rs->all, 5, 'Correct number of CD objects');
280 is ($rs->count, 5, 'Correct count of CDs');
283 # RT 47779, test group_by as a scalar ref
285 my $track_rs = $schema->resultset ('Track')->search (
286 { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
290 { count => 'me.trackid' },
296 group_by => \'SUBSTR(me.cd, 1, 1)',
302 $track_rs->count_rs->as_query,
306 SELECT SUBSTR(me.cd, 1, 1)
308 JOIN cd cd ON cd.cdid = me.cd
309 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
310 GROUP BY SUBSTR(me.cd, 1, 1)
314 [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
315 => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
316 'count() query generated expected SQL',
321 my $cd_rs = $schema->resultset('CD')->search({}, {
323 join => [qw/ tracks /],
324 prefetch => [qw/ artist /],
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');
329 # make sure join tracks was thrown out
333 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
334 artist.artistid, artist.name, artist.rank, artist.charfield
336 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
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
341 JOIN artist artist ON artist.artistid = me.artist
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');
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
358 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
359 artist.artistid, artist.name, artist.rank, artist.charfield
361 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
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
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
374 [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
375 => 'ugabuganoexist' ] } (1,2)
380 # make sure distinct applies to the CD part only, not to the prefetched/collapsed order_by part
382 my $rs = $schema->resultset('CD')->search({}, {
383 columns => [qw( cdid title )],
384 '+select' => [{ count => 'tags.tag' }],
385 '+as' => ['test_count'],
386 prefetch => ['tags'],
388 order_by => {'-desc' => 'tags.tag'},
393 is_same_sql_bind($rs->as_query,
395 SELECT me.cdid, me.title, me.test_count,
396 tags.tagid, tags.cd, tags.tag
398 SELECT me.cdid, me.title,
399 COUNT( tags.tag ) AS test_count
403 GROUP BY me.cdid, me.title
404 ORDER BY MAX( tags.tag ) DESC
410 ORDER BY tags.tag DESC
412 [ [$ROWS => 3], [$OFFSET => 1] ],
413 'Expected limited prefetch with distinct SQL',
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 },
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 },
427 cdid => 1, test_count => 1, title => "Spoonful of bees", tags => [
428 { cd => 1, tag => "Blue", tagid => 1 },
435 'HRI dump of limited prefetch with distinct as expected'
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'],
445 order_by => {'-desc' => 'tags.tag'},
450 is_same_sql_bind($rs->as_query,
452 SELECT cds.cdid, cds.title, cds.test_count,
453 tags.tagid, tags.cd, tags.tag
456 ON artist.artistid = me.artist
458 SELECT cds.cdid, cds.title,
459 COUNT( tags.tag ) AS test_count,
463 ON artist.artistid = me.artist
465 ON cds.artist = artist.artistid
467 ON tags.cd = cds.cdid
468 GROUP BY cds.cdid, cds.title, cds.artist
469 ORDER BY MAX( tags.tag ) DESC
473 ON cds.artist = artist.artistid
475 ON tags.cd = cds.cdid
476 ORDER BY tags.tag DESC
478 [ [$ROWS => 3], [$OFFSET => 1] ],
479 'Expected limited prefetch with distinct SQL on premultiplied head',
482 # Tag counts are multiplied by the cd->artist->cds multiplication
483 # I would *almost* call this "expected" without wraping an as_subselect_rs
485 local $TODO = 'Not sure if we can stop the count/group of premultiplication abstraction leak';
489 'HRI dump of limited prefetch with distinct as expected on premultiplid head'