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 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;