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 my @cdids = sort $cd_rs->get_column ('cdid')->all;
28 # Test a belongs_to prefetch of a has_many
30 my $track_rs = $schema->resultset ('Track')->search (
31 { 'me.cd' => { -in => \@cdids } },
35 { count => 'me.trackid' },
41 group_by => [qw/me.cd/],
46 # this used to fuck up ->all, do not remove!
47 ok ($track_rs->first, 'There is stuff in the rs');
49 is($track_rs->count, 5, 'Prefetched count with groupby');
50 is($track_rs->all, 5, 'Prefetched objects with groupby');
52 $schema->is_executed_querycount( sub {
53 while (my $collapsed_track = $track_rs->next) {
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" );
58 }, 1, 'Single query on prefetched titles');
60 # Test sql by hand, as the sqlite db will simply paper over
61 # improper group/select combinations
64 $track_rs->count_rs->as_query,
70 JOIN cd cd ON cd.cdid = me.cd
71 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
76 [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
78 'count() query generated expected SQL',
84 SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track
86 SELECT me.cd, COUNT (me.trackid) AS track_count
88 JOIN cd cd ON cd.cdid = me.cd
89 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
92 JOIN cd cd ON cd.cdid = me.cd
93 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
95 [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
96 => $_ ] } (@cdids) x 2 ],
97 'next() query generated expected SQL',
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',
108 my $top_cd_collapsed_track = $track_rs->search ({}, {
111 { -desc => 'track_count' },
115 is ($top_cd_collapsed_track->count, 2);
119 $top_cd_collapsed_track->first->cd->title,
120 'Correct collapsed track with prefetched CD returned on top'
124 # test a has_many/might_have prefetch at the same level
125 # Note that one of the CDs now has 4 tracks instead of 3
127 my $most_tracks_rs = $schema->resultset ('CD')->search (
129 'me.cdid' => { '!=' => undef }, # duh - this is just to test WHERE
132 prefetch => [qw/tracks liner_notes/],
133 select => ['me.cdid', { count => 'tracks.trackid' }, { max => 'tracks.trackid', -as => 'maxtr'} ],
134 as => [qw/cdid track_count max_track_id/],
135 group_by => 'me.cdid',
136 order_by => [ { -desc => 'track_count' }, { -asc => 'maxtr' } ],
142 $most_tracks_rs->count_rs->as_query,
148 WHERE ( me.cdid IS NOT NULL )
154 'count() query generated expected SQL',
158 $most_tracks_rs->as_query,
160 SELECT me.cdid, me.track_count, me.maxtr,
161 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
162 liner_notes.liner_id, liner_notes.notes
164 SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr
166 LEFT JOIN track tracks ON tracks.cd = me.cdid
167 WHERE ( me.cdid IS NOT NULL )
169 ORDER BY track_count DESC, maxtr ASC
172 LEFT JOIN track tracks ON tracks.cd = me.cdid
173 LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
174 WHERE ( me.cdid IS NOT NULL )
175 ORDER BY track_count DESC, maxtr ASC
178 'next() query generated expected SQL',
181 is ($most_tracks_rs->count, 2, 'Limit works');
182 my ($top_cd) = $most_tracks_rs->all;
183 is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
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');
190 $top_cd->liner_notes->notes,
192 'Correct liner pre-fetched with top cd',
194 }, 0, 'No queries executed during prefetched data access');
198 # test lifted from soulchild
200 my $most_tracks_rs = $schema->resultset ('CD')->search (
202 'me.cdid' => { '!=' => undef }, # this is just to test WHERE
203 'tracks.trackid' => { '!=' => undef },
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',
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
221 SELECT me.cdid, COUNT(tracks.trackid) AS tr_count, MAX(tracks.trackid) AS tr_maxid
223 LEFT JOIN track tracks
224 ON tracks.cd = me.cdid
225 WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL
227 ORDER BY tr_count DESC, tr_maxid ASC
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
238 'Oddball mysql-ish group_by usage yields valid SQL',
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
245 $schema->is_executed_querycount( sub {
246 is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
248 $top_cd->liner_notes->notes,
250 'Correct liner pre-fetched with top cd',
252 }, 0, 'No queries executed during prefetched data access');
256 # make sure that distinct still works
258 my $rs = $schema->resultset("CD")->search({}, {
267 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
268 tags.tagid, tags.cd, tags.tag
270 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
272 GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
274 LEFT JOIN tags tags ON tags.cd = me.cdid
278 'Prefetch + distinct resulted in correct group_by',
281 is ($rs->all, 5, 'Correct number of CD objects');
282 is ($rs->count, 5, 'Correct count of CDs');
285 # RT 47779, test group_by as a scalar ref
287 my $track_rs = $schema->resultset ('Track')->search (
288 { 'me.cd' => { -in => \@cdids } },
292 { count => 'me.trackid' },
298 group_by => \'SUBSTR(me.cd, 1, 1)',
304 $track_rs->count_rs->as_query,
308 SELECT SUBSTR(me.cd, 1, 1)
310 JOIN cd cd ON cd.cdid = me.cd
311 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
312 GROUP BY SUBSTR(me.cd, 1, 1)
316 [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
317 => $_ ] } (@cdids) ],
318 'count() query generated expected SQL',
323 my $cd_rs = $schema->resultset('CD')->search({}, {
325 join => [qw/ tracks /],
326 prefetch => [qw/ artist /],
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');
331 # make sure join tracks was thrown out
335 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
336 artist.artistid, artist.name, artist.rank, artist.charfield
338 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
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
343 JOIN artist artist ON artist.artistid = me.artist
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');
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
360 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
361 artist.artistid, artist.name, artist.rank, artist.charfield
363 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
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
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
376 [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
377 => 'ugabuganoexist' ] } (1,2)
382 # make sure distinct applies to the CD part only, not to the prefetched/collapsed order_by part
384 my $rs = $schema->resultset('CD')->search({}, {
385 columns => [qw( cdid title )],
386 '+select' => [{ count => 'tags.tag' }],
387 '+as' => ['test_count'],
388 prefetch => ['tags'],
390 order_by => {'-desc' => 'tags.tag'},
395 is_same_sql_bind($rs->as_query,
397 SELECT me.cdid, me.title, me.test_count,
398 tags.tagid, tags.cd, tags.tag
400 SELECT me.cdid, me.title,
401 COUNT( tags.tag ) AS test_count
405 GROUP BY me.cdid, me.title
406 ORDER BY MAX( tags.tag ) DESC
412 ORDER BY tags.tag DESC
414 [ [$ROWS => 3], [$OFFSET => 1] ],
415 'Expected limited prefetch with distinct SQL',
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 },
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 },
429 cdid => 1, test_count => 1, title => "Spoonful of bees", tags => [
430 { cd => 1, tag => "Blue", tagid => 1 },
437 'HRI dump of limited prefetch with distinct as expected'
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'],
447 order_by => {'-desc' => 'tags.tag'},
452 is_same_sql_bind($rs->as_query,
454 SELECT cds.cdid, cds.title, cds.test_count,
455 tags.tagid, tags.cd, tags.tag
458 ON artist.artistid = me.artist
460 SELECT cds.cdid, cds.title,
461 COUNT( tags.tag ) AS test_count,
465 ON artist.artistid = me.artist
467 ON cds.artist = artist.artistid
469 ON tags.cd = cds.cdid
470 GROUP BY cds.cdid, cds.title, cds.artist
471 ORDER BY MAX( tags.tag ) DESC
475 ON cds.artist = artist.artistid
477 ON tags.cd = cds.cdid
478 ORDER BY tags.tag DESC
480 [ [$ROWS => 3], [$OFFSET => 1] ],
481 'Expected limited prefetch with distinct SQL on premultiplied head',
484 # Tag counts are multiplied by the cd->artist->cds multiplication
485 # I would *almost* call this "expected" without wraping an as_subselect_rs
487 local $TODO = 'Not sure if we can stop the count/group of premultiplication abstraction leak';
491 'HRI dump of limited prefetch with distinct as expected on premultiplid head'