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 ':DiffSQL';
   8 use DBIx::Class::SQLMaker::LimitDialects;
   9
  10 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
  11 my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
  12
  13 my $schema = DBICTest->init_schema();
  14
  15 my $cd_rs = $schema->resultset('CD')->search (
  16   { 'tracks.cd' => { '!=', undef } },
  17   { prefetch => 'tracks' },
  18 );
  19
  20 # Database sanity check
  21 is($cd_rs->count, 5, 'CDs with tracks count');
  22 for ($cd_rs->all) {
  23   is ($_->tracks->count, 3, '3 tracks for CD' . $_->id );
  24 }
  25
  26 # Test a belongs_to prefetch of a has_many
  27 {
  28   my $track_rs = $schema->resultset ('Track')->search (
  29     { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
  30     {
  31       select => [
  32         'me.cd',
  33         { count => 'me.trackid' },
  34       ],
  35       as => [qw/
  36         cd
  37         track_count
  38       /],
  39       group_by => [qw/me.cd/],
  40       prefetch => 'cd',
  41     },
  42   );
  43
  44   # this used to fuck up ->all, do not remove!
  45   ok ($track_rs->first, 'There is stuff in the rs');
  46
  47   is($track_rs->count, 5, 'Prefetched count with groupby');
  48   is($track_rs->all, 5, 'Prefetched objects with groupby');
  49
  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" );
  55     }
  56   }, 1, 'Single query on prefetched titles');
  57
  58   # Test sql by hand, as the sqlite db will simply paper over
  59   # improper group/select combinations
  60   #
  61   is_same_sql_bind (
  62     $track_rs->count_rs->as_query,
  63     '(
  64       SELECT COUNT( * )
  65         FROM (
  66           SELECT me.cd
  67             FROM track me
  68             JOIN cd cd ON cd.cdid = me.cd
  69           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  70           GROUP BY me.cd
  71         )
  72       me
  73     )',
  74     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
  75       => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
  76     'count() query generated expected SQL',
  77   );
  78
  79   is_same_sql_bind (
  80     $track_rs->as_query,
  81     '(
  82       SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track
  83         FROM (
  84           SELECT me.cd, COUNT (me.trackid) AS track_count
  85             FROM track me
  86             JOIN cd cd ON cd.cdid = me.cd
  87           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  88           GROUP BY me.cd
  89           ) me
  90         JOIN cd cd ON cd.cdid = me.cd
  91       WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  92     )',
  93     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
  94       => $_ ] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ],
  95     'next() query generated expected SQL',
  96   );
  97
  98
  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',
 104   });
 105
 106   my $top_cd_collapsed_track = $track_rs->search ({}, {
 107     rows => 2,
 108     order_by => [
 109       { -desc => 'track_count' },
 110     ],
 111   });
 112
 113   is ($top_cd_collapsed_track->count, 2);
 114
 115   is (
 116     $top_cd->title,
 117     $top_cd_collapsed_track->first->cd->title,
 118     'Correct collapsed track with prefetched CD returned on top'
 119   );
 120 }
 121
 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
 124 {
 125   my $most_tracks_rs = $schema->resultset ('CD')->search (
 126     {
 127       'me.cdid' => { '!=' => undef },  # duh - this is just to test WHERE
 128     },
 129     {
 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' } ],
 135       rows => 2,
 136     }
 137   );
 138
 139   is_same_sql_bind (
 140     $most_tracks_rs->count_rs->as_query,
 141     '(
 142       SELECT COUNT( * )
 143         FROM (
 144           SELECT me.cdid
 145             FROM cd me
 146           WHERE ( me.cdid IS NOT NULL )
 147           GROUP BY me.cdid
 148           LIMIT ?
 149         ) me
 150     )',
 151     [[$ROWS => 2]],
 152     'count() query generated expected SQL',
 153   );
 154
 155   is_same_sql_bind (
 156     $most_tracks_rs->as_query,
 157     '(
 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
 161         FROM (
 162           SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr
 163             FROM cd me
 164             LEFT JOIN track tracks ON tracks.cd = me.cdid
 165           WHERE ( me.cdid IS NOT NULL )
 166           GROUP BY me.cdid
 167           ORDER BY track_count DESC, maxtr ASC
 168           LIMIT ?
 169         ) me
 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
 174     )',
 175     [[$ROWS => 2]],
 176     'next() query generated expected SQL',
 177   );
 178
 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
 182
 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');
 187     is (
 188       $top_cd->liner_notes->notes,
 189       'Buy Whiskey!',
 190       'Correct liner pre-fetched with top cd',
 191     );
 192   }, 0, 'No queries executed during prefetched data access');
 193 }
 194
 195 {
 196   # test lifted from soulchild
 197
 198   my $most_tracks_rs = $schema->resultset ('CD')->search (
 199     {
 200       'me.cdid' => { '!=' => undef },  # this is just to test WHERE
 201       'tracks.trackid' => { '!=' => undef },
 202     },
 203     {
 204       join => 'tracks',
 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',
 210       rows => 2,
 211     }
 212   );
 213
 214   is_same_sql_bind(
 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
 218         FROM (
 219           SELECT me.cdid, COUNT(tracks.trackid) AS tr_count, MAX(tracks.trackid) AS tr_maxid
 220             FROM cd me
 221             LEFT JOIN track tracks
 222               ON tracks.cd = me.cdid
 223           WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL
 224           GROUP BY me.cdid
 225           ORDER BY tr_count DESC, tr_maxid ASC
 226           LIMIT ?
 227         ) me
 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
 234     )',
 235     [[$ROWS => 2]],
 236     'Oddball mysql-ish group_by usage yields valid SQL',
 237   );
 238
 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
 242
 243   $schema->is_executed_querycount( sub {
 244     is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
 245     is (
 246       $top_cd->liner_notes->notes,
 247       'Buy Whiskey!',
 248       'Correct liner pre-fetched with top cd',
 249     );
 250   }, 0, 'No queries executed during prefetched data access');
 251 }
 252
 253
 254 # make sure that distinct still works
 255 {
 256   my $rs = $schema->resultset("CD")->search({}, {
 257     prefetch => 'tags',
 258     order_by => 'cdid',
 259     distinct => 1,
 260   });
 261
 262   is_same_sql_bind (
 263     $rs->as_query,
 264     '(
 265       SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 266              tags.tagid, tags.cd, tags.tag
 267         FROM (
 268           SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 269             FROM cd me
 270           GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 271         ) me
 272         LEFT JOIN tags tags ON tags.cd = me.cdid
 273       ORDER BY cdid
 274     )',
 275     [],
 276     'Prefetch + distinct resulted in correct group_by',
 277   );
 278
 279   is ($rs->all, 5, 'Correct number of CD objects');
 280   is ($rs->count, 5, 'Correct count of CDs');
 281 }
 282
 283 # RT 47779, test group_by as a scalar ref
 284 {
 285   my $track_rs = $schema->resultset ('Track')->search (
 286     { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
 287     {
 288       select => [
 289         'me.cd',
 290         { count => 'me.trackid' },
 291       ],
 292       as => [qw/
 293         cd
 294         track_count
 295       /],
 296       group_by => \'SUBSTR(me.cd, 1, 1)',
 297       prefetch => 'cd',
 298     },
 299   );
 300
 301   is_same_sql_bind (
 302     $track_rs->count_rs->as_query,
 303     '(
 304       SELECT COUNT( * )
 305         FROM (
 306           SELECT SUBSTR(me.cd, 1, 1)
 307             FROM track me
 308             JOIN cd cd ON cd.cdid = me.cd
 309           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
 310           GROUP BY SUBSTR(me.cd, 1, 1)
 311         )
 312       me
 313     )',
 314     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
 315       => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
 316     'count() query generated expected SQL',
 317   );
 318 }
 319
 320 {
 321     my $cd_rs = $schema->resultset('CD')->search({}, {
 322             distinct => 1,
 323             join     => [qw/ tracks /],
 324             prefetch => [qw/ artist /],
 325         });
 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');
 328
 329     # make sure join tracks was thrown out
 330     is_same_sql_bind (
 331       $cd_rs->as_query,
 332       '(
 333         SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 334                artist.artistid, artist.name, artist.rank, artist.charfield
 335           FROM (
 336             SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 337               FROM cd me
 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
 340           ) me
 341           JOIN artist artist ON artist.artistid = me.artist
 342       )',
 343       [],
 344     );
 345
 346
 347
 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');
 352
 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
 355     is_same_sql_bind (
 356       $cd_rs2->as_query,
 357       '(
 358         SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 359                artist.artistid, artist.name, artist.rank, artist.charfield
 360           FROM (
 361             SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 362               FROM cd me
 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
 367           ) me
 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
 373       )',
 374       [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
 375             => 'ugabuganoexist' ] } (1,2)
 376       ],
 377     );
 378 }
 379
 380 # make sure distinct applies to the CD part only, not to the prefetched/collapsed order_by part
 381 {
 382   my $rs = $schema->resultset('CD')->search({}, {
 383     columns => [qw( cdid title )],
 384     '+select' => [{ count => 'tags.tag' }],
 385     '+as' => ['test_count'],
 386     prefetch => ['tags'],
 387     distinct => 1,
 388     order_by => {'-desc' => 'tags.tag'},
 389     offset => 1,
 390     rows => 3,
 391   });
 392
 393   is_same_sql_bind($rs->as_query,
 394     '(
 395       SELECT me.cdid, me.title, me.test_count,
 396              tags.tagid, tags.cd, tags.tag
 397         FROM (
 398           SELECT  me.cdid, me.title,
 399                   COUNT( tags.tag ) AS test_count
 400             FROM cd me
 401             LEFT JOIN tags tags
 402               ON tags.cd = me.cdid
 403           GROUP BY me.cdid, me.title
 404           ORDER BY MAX( tags.tag ) DESC
 405           LIMIT ?
 406           OFFSET ?
 407         ) me
 408         LEFT JOIN tags tags
 409           ON tags.cd = me.cdid
 410       ORDER BY tags.tag DESC
 411     )',
 412     [ [$ROWS => 3], [$OFFSET => 1] ],
 413     'Expected limited prefetch with distinct SQL',
 414   );
 415
 416   my $expected_hri = [
 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 },
 420     ]},
 421     {
 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 },
 425     ]},
 426     {
 427       cdid => 1, test_count => 1, title => "Spoonful of bees", tags => [
 428       { cd => 1, tag => "Blue", tagid => 1 },
 429     ]},
 430   ];
 431
 432   is_deeply (
 433     $rs->all_hri,
 434     $expected_hri,
 435     'HRI dump of limited prefetch with distinct as expected'
 436   );
 437
 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'],
 444     distinct => 1,
 445     order_by => {'-desc' => 'tags.tag'},
 446     offset => 1,
 447     rows => 3,
 448   });
 449
 450   is_same_sql_bind($rs->as_query,
 451     '(
 452       SELECT cds.cdid, cds.title, cds.test_count,
 453              tags.tagid, tags.cd, tags.tag
 454         FROM cd me
 455         JOIN artist artist
 456           ON artist.artistid = me.artist
 457         JOIN (
 458           SELECT  cds.cdid, cds.title,
 459                   COUNT( tags.tag ) AS test_count,
 460                   cds.artist
 461             FROM cd me
 462             JOIN artist artist
 463               ON artist.artistid = me.artist
 464             JOIN cd cds
 465               ON cds.artist = artist.artistid
 466             LEFT JOIN tags tags
 467               ON tags.cd = cds.cdid
 468           GROUP BY cds.cdid, cds.title, cds.artist
 469           ORDER BY MAX( tags.tag ) DESC
 470           LIMIT ?
 471           OFFSET ?
 472         ) cds
 473           ON cds.artist = artist.artistid
 474         LEFT JOIN tags tags
 475           ON tags.cd = cds.cdid
 476       ORDER BY tags.tag DESC
 477     )',
 478     [ [$ROWS => 3], [$OFFSET => 1] ],
 479     'Expected limited prefetch with distinct SQL on premultiplied head',
 480   );
 481
 482   # Tag counts are multiplied by the cd->artist->cds multiplication
 483   # I would *almost* call this "expected" without wraping an as_subselect_rs
 484   {
 485     local $TODO = 'Not sure if we can stop the count/group of premultiplication abstraction leak';
 486     is_deeply (
 487       $rs->all_hri,
 488       $expected_hri,
 489       'HRI dump of limited prefetch with distinct as expected on premultiplid head'
 490     );
 491   }
 492 }
 493
 494 done_testing;