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