Give Firebird (and Interbase) a common sqlt_type
[dbsrgits/DBIx-Class-Historic.git] / t / prefetch / grouped.t
1 use strict;
2 use warnings;
3
4 use Test::More;
5
6 use lib qw(t/lib);
7 use DBICTest;
8 use DBIC::SqlMakerTest;
9 use DBIx::Class::SQLMaker::LimitDialects;
10
11 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
12 my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
13
14 my $schema = DBICTest->init_schema();
15 my $sdebug = $schema->storage->debug;
16
17 my $cd_rs = $schema->resultset('CD')->search (
18   { 'tracks.cd' => { '!=', undef } },
19   { prefetch => 'tracks' },
20 );
21
22 # Database sanity check
23 is($cd_rs->count, 5, 'CDs with tracks count');
24 for ($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     {
33       select => [
34         'me.cd',
35         { count => 'me.trackid' },
36       ],
37       as => [qw/
38         cd
39         track_count
40       /],
41       group_by => [qw/me.cd/],
42       prefetch => 'cd',
43     },
44   );
45
46   # this used to fuck up ->all, do not remove!
47   ok ($track_rs->first, 'There is stuff in the rs');
48
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++ } );
55     $schema->storage->debug (1);
56
57     while (my $collapsed_track = $track_rs->next) {
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
63     is ($query_cnt, 1, 'Single query on prefetched titles');
64     $schema->storage->debugcb (undef);
65     $schema->storage->debug ($sdebug);
66   }
67
68   # Test sql by hand, as the sqlite db will simply paper over
69   # improper group/select combinations
70   #
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
79           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
80           GROUP BY me.cd
81         )
82       me
83     )',
84     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
85       => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
86     'count() query generated expected SQL',
87   );
88
89   is_same_sql_bind (
90     $track_rs->as_query,
91     '(
92       SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track
93         FROM (
94           SELECT me.cd, COUNT (me.trackid) AS track_count
95             FROM track me
96             JOIN cd cd ON cd.cdid = me.cd
97           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
98           GROUP BY me.cd
99           ) me
100         JOIN cd cd ON cd.cdid = me.cd
101       WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
102     )',
103     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
104       => $_ ] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ],
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
133 # Note that one of the CDs now has 4 tracks instead of 3
134 {
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/],
141       select => ['me.cdid', { count => 'tracks.trackid' }, { max => 'tracks.trackid', -as => 'maxtr'} ],
142       as => [qw/cdid track_count max_track_id/],
143       group_by => 'me.cdid',
144       order_by => [ { -desc => 'track_count' }, { -asc => 'maxtr' } ],
145       rows => 2,
146     }
147   );
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
156           WHERE ( me.cdid IS NOT NULL )
157           GROUP BY me.cdid
158           LIMIT ?
159         ) me
160     )',
161     [[$ROWS => 2]],
162     'count() query generated expected SQL',
163   );
164
165   is_same_sql_bind (
166     $most_tracks_rs->as_query,
167     '(
168       SELECT  me.cdid, me.track_count, me.maxtr,
169               tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
170               liner_notes.liner_id, liner_notes.notes
171         FROM (
172           SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr
173             FROM cd me
174             LEFT JOIN track tracks ON tracks.cd = me.cdid
175           WHERE ( me.cdid IS NOT NULL )
176           GROUP BY me.cdid
177           ORDER BY track_count DESC, maxtr ASC
178           LIMIT ?
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
182       WHERE ( me.cdid IS NOT NULL )
183       ORDER BY track_count DESC, maxtr ASC
184     )',
185     [[$ROWS => 2]],
186     'next() query generated expected SQL',
187   );
188
189   is ($most_tracks_rs->count, 2, 'Limit works');
190   my ($top_cd) = $most_tracks_rs->all;
191   is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
192
193   my $query_cnt = 0;
194   $schema->storage->debugcb ( sub { $query_cnt++ } );
195   $schema->storage->debug (1);
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);
208   $schema->storage->debug ($sdebug);
209 }
210
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
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,
288              tags.tagid, tags.cd, tags.tag
289         FROM (
290           SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
291             FROM cd me
292           GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
293         ) me
294         LEFT JOIN tags tags ON tags.cd = me.cdid
295       ORDER BY cdid
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 }
304
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 (
328           SELECT SUBSTR(me.cd, 1, 1)
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         )
334       me
335     )',
336     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
337       => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
338     'count() query generated expected SQL',
339   );
340 }
341
342 {
343     my $cd_rs = $schema->resultset('CD')->search({}, {
344             distinct => 1,
345             join     => [qw/ tracks /],
346             prefetch => [qw/ artist /],
347         });
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
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
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       )',
396       [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
397             => 'ugabuganoexist' ] } (1,2)
398       ],
399     );
400 }
401
402 # make sure distinct applies to the CD part only, not to the prefetched/collapsed order_by part
403 {
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'
512     );
513   }
514 }
515
516 done_testing;