t/prefetch/grouped.t

   1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
   2
   3 use strict;
   4 use warnings;
   5
   6 use Test::More;
   7
   8 use DBICTest ':DiffSQL';
   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
  16 my $cd_rs = $schema->resultset('CD')->search (
  17   { 'tracks.cd' => { '!=', undef } },
  18   { prefetch => 'tracks' },
  19 );
  20
  21 # Database sanity check
  22 is($cd_rs->count, 5, 'CDs with tracks count');
  23 for ($cd_rs->all) {
  24   is ($_->tracks->count, 3, '3 tracks for CD' . $_->id );
  25 }
  26
  27 my @cdids = sort $cd_rs->get_column ('cdid')->all;
  28
  29 # Test a belongs_to prefetch of a has_many
  30 {
  31   my $track_rs = $schema->resultset ('Track')->search (
  32     { 'me.cd' => { -in => \@cdids } },
  33     {
  34       select => [
  35         'me.cd',
  36         { count => 'me.trackid' },
  37       ],
  38       as => [qw/
  39         cd
  40         track_count
  41       /],
  42       group_by => [qw/me.cd/],
  43       prefetch => 'cd',
  44     },
  45   );
  46
  47   # this used to fuck up ->all, do not remove!
  48   ok ($track_rs->first, 'There is stuff in the rs');
  49
  50   is($track_rs->count, 5, 'Prefetched count with groupby');
  51   is($track_rs->all, 5, 'Prefetched objects with groupby');
  52
  53   $schema->is_executed_querycount( sub {
  54     while (my $collapsed_track = $track_rs->next) {
  55       my $cdid = $collapsed_track->get_column('cd');
  56       is($collapsed_track->get_column('track_count'), 3, "Correct count of tracks for CD $cdid" );
  57       ok($collapsed_track->cd->title, "Prefetched title for CD $cdid" );
  58     }
  59   }, 1, 'Single query on prefetched titles');
  60
  61   # Test sql by hand, as the sqlite db will simply paper over
  62   # improper group/select combinations
  63   #
  64   is_same_sql_bind (
  65     $track_rs->count_rs->as_query,
  66     '(
  67       SELECT COUNT( * )
  68         FROM (
  69           SELECT me.cd
  70             FROM track me
  71             JOIN cd cd ON cd.cdid = me.cd
  72           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  73           GROUP BY me.cd
  74         )
  75       me
  76     )',
  77     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
  78       => $_ ] } @cdids ],
  79     'count() query generated expected SQL',
  80   );
  81
  82   is_same_sql_bind (
  83     $track_rs->as_query,
  84     '(
  85       SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track
  86         FROM (
  87           SELECT me.cd, COUNT (me.trackid) AS track_count
  88             FROM track me
  89             JOIN cd cd ON cd.cdid = me.cd
  90           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  91           GROUP BY me.cd
  92           ) me
  93         JOIN cd cd ON cd.cdid = me.cd
  94       WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  95     )',
  96     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
  97       => $_ ] } (@cdids) x 2 ],
  98     'next() query generated expected SQL',
  99   );
 100
 101
 102   # add an extra track to one of the cds, and then make sure we can get it on top
 103   # (check if limit works)
 104   my $top_cd = $cd_rs->search({}, { order_by => 'cdid' })->slice (1,1)->next;
 105   $top_cd->create_related ('tracks', {
 106     title => 'over the top',
 107   });
 108
 109   my $top_cd_collapsed_track = $track_rs->search ({}, {
 110     rows => 2,
 111     order_by => [
 112       { -desc => 'track_count' },
 113     ],
 114   });
 115
 116   is ($top_cd_collapsed_track->count, 2);
 117
 118   is (
 119     $top_cd->title,
 120     $top_cd_collapsed_track->first->cd->title,
 121     'Correct collapsed track with prefetched CD returned on top'
 122   );
 123 }
 124
 125 # test a has_many/might_have prefetch at the same level
 126 # Note that one of the CDs now has 4 tracks instead of 3
 127 {
 128   my $most_tracks_rs = $schema->resultset ('CD')->search (
 129     {
 130       'me.cdid' => { '!=' => undef },  # duh - this is just to test WHERE
 131     },
 132     {
 133       prefetch => [qw/tracks liner_notes/],
 134       select => ['me.cdid', { count => 'tracks.trackid' }, { max => 'tracks.trackid', -as => 'maxtr'} ],
 135       as => [qw/cdid track_count max_track_id/],
 136       group_by => 'me.cdid',
 137       order_by => [ { -desc => 'track_count' }, { -asc => 'maxtr' } ],
 138       rows => 2,
 139     }
 140   );
 141
 142   is_same_sql_bind (
 143     $most_tracks_rs->count_rs->as_query,
 144     '(
 145       SELECT COUNT( * )
 146         FROM (
 147           SELECT me.cdid
 148             FROM cd me
 149           WHERE ( me.cdid IS NOT NULL )
 150           GROUP BY me.cdid
 151           LIMIT ?
 152         ) me
 153     )',
 154     [[$ROWS => 2]],
 155     'count() query generated expected SQL',
 156   );
 157
 158   is_same_sql_bind (
 159     $most_tracks_rs->as_query,
 160     '(
 161       SELECT  me.cdid, me.track_count, me.maxtr,
 162               tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
 163               liner_notes.liner_id, liner_notes.notes
 164         FROM (
 165           SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr
 166             FROM cd me
 167             LEFT JOIN track tracks ON tracks.cd = me.cdid
 168           WHERE ( me.cdid IS NOT NULL )
 169           GROUP BY me.cdid
 170           ORDER BY track_count DESC, maxtr ASC
 171           LIMIT ?
 172         ) me
 173         LEFT JOIN track tracks ON tracks.cd = me.cdid
 174         LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
 175       WHERE ( me.cdid IS NOT NULL )
 176       ORDER BY track_count DESC, maxtr ASC
 177     )',
 178     [[$ROWS => 2]],
 179     'next() query generated expected SQL',
 180   );
 181
 182   is ($most_tracks_rs->count, 2, 'Limit works');
 183   my ($top_cd) = $most_tracks_rs->all;
 184   is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
 185
 186   $schema->is_executed_querycount( sub {
 187     is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
 188     is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct');
 189     is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct');
 190     is (
 191       $top_cd->liner_notes->notes,
 192       'Buy Whiskey!',
 193       'Correct liner pre-fetched with top cd',
 194     );
 195   }, 0, 'No queries executed during prefetched data access');
 196 }
 197
 198 {
 199   # test lifted from soulchild
 200
 201   my $most_tracks_rs = $schema->resultset ('CD')->search (
 202     {
 203       'me.cdid' => { '!=' => undef },  # this is just to test WHERE
 204       'tracks.trackid' => { '!=' => undef },
 205     },
 206     {
 207       join => 'tracks',
 208       prefetch => 'liner_notes',
 209       select => ['me.cdid', 'liner_notes.notes', { count => 'tracks.trackid', -as => 'tr_count' }, { max => 'tracks.trackid', -as => 'tr_maxid'} ],
 210       as => [qw/cdid notes track_count max_track_id/],
 211       order_by => [ { -desc => 'tr_count' }, { -asc => 'tr_maxid' } ],
 212       group_by => 'me.cdid',
 213       rows => 2,
 214     }
 215   );
 216
 217   is_same_sql_bind(
 218     $most_tracks_rs->as_query,
 219     '(SELECT  me.cdid, liner_notes.notes, me.tr_count, me.tr_maxid,
 220               liner_notes.liner_id, liner_notes.notes
 221         FROM (
 222           SELECT me.cdid, COUNT(tracks.trackid) AS tr_count, MAX(tracks.trackid) AS tr_maxid
 223             FROM cd me
 224             LEFT JOIN track tracks
 225               ON tracks.cd = me.cdid
 226           WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL
 227           GROUP BY me.cdid
 228           ORDER BY tr_count DESC, tr_maxid ASC
 229           LIMIT ?
 230         ) me
 231         LEFT JOIN track tracks
 232           ON tracks.cd = me.cdid
 233         LEFT JOIN liner_notes liner_notes
 234           ON liner_notes.liner_id = me.cdid
 235       WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL
 236       ORDER BY tr_count DESC, tr_maxid ASC
 237     )',
 238     [[$ROWS => 2]],
 239     'Oddball mysql-ish group_by usage yields valid SQL',
 240   );
 241
 242   is ($most_tracks_rs->count, 2, 'Limit works');
 243   my ($top_cd) = $most_tracks_rs->all;
 244   is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
 245
 246   $schema->is_executed_querycount( sub {
 247     is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
 248     is (
 249       $top_cd->liner_notes->notes,
 250       'Buy Whiskey!',
 251       'Correct liner pre-fetched with top cd',
 252     );
 253   }, 0, 'No queries executed during prefetched data access');
 254 }
 255
 256
 257 # make sure that distinct still works
 258 {
 259   my $rs = $schema->resultset("CD")->search({}, {
 260     prefetch => 'tags',
 261     order_by => 'cdid',
 262     distinct => 1,
 263   });
 264
 265   is_same_sql_bind (
 266     $rs->as_query,
 267     '(
 268       SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 269              tags.tagid, tags.cd, tags.tag
 270         FROM (
 271           SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 272             FROM cd me
 273           GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 274         ) me
 275         LEFT JOIN tags tags ON tags.cd = me.cdid
 276       ORDER BY cdid
 277     )',
 278     [],
 279     'Prefetch + distinct resulted in correct group_by',
 280   );
 281
 282   is ($rs->all, 5, 'Correct number of CD objects');
 283   is ($rs->count, 5, 'Correct count of CDs');
 284 }
 285
 286 # RT 47779, test group_by as a scalar ref
 287 {
 288   my $track_rs = $schema->resultset ('Track')->search (
 289     { 'me.cd' => { -in => \@cdids } },
 290     {
 291       select => [
 292         'me.cd',
 293         { count => 'me.trackid' },
 294       ],
 295       as => [qw/
 296         cd
 297         track_count
 298       /],
 299       group_by => \'SUBSTR(me.cd, 1, 1)',
 300       prefetch => 'cd',
 301     },
 302   );
 303
 304   is_same_sql_bind (
 305     $track_rs->count_rs->as_query,
 306     '(
 307       SELECT COUNT( * )
 308         FROM (
 309           SELECT SUBSTR(me.cd, 1, 1)
 310             FROM track me
 311             JOIN cd cd ON cd.cdid = me.cd
 312           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
 313           GROUP BY SUBSTR(me.cd, 1, 1)
 314         )
 315       me
 316     )',
 317     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
 318       => $_ ] } (@cdids) ],
 319     'count() query generated expected SQL',
 320   );
 321 }
 322
 323 {
 324     my $cd_rs = $schema->resultset('CD')->search({}, {
 325             distinct => 1,
 326             join     => [qw/ tracks /],
 327             prefetch => [qw/ artist /],
 328         });
 329     is($cd_rs->count, 5, 'complex prefetch + non-prefetching has_many join count correct');
 330     is($cd_rs->all, 5, 'complex prefetch + non-prefetching has_many join number of objects correct');
 331
 332     # make sure join tracks was thrown out
 333     is_same_sql_bind (
 334       $cd_rs->as_query,
 335       '(
 336         SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 337                artist.artistid, artist.name, artist.rank, artist.charfield
 338           FROM (
 339             SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 340               FROM cd me
 341               JOIN artist artist ON artist.artistid = me.artist
 342             GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 343           ) me
 344           JOIN artist artist ON artist.artistid = me.artist
 345       )',
 346       [],
 347     );
 348
 349
 350
 351     # try the same as above, but add a condition so the tracks join can not be thrown away
 352     my $cd_rs2 = $cd_rs->search ({ 'tracks.title' => { '!=' => 'ugabuganoexist' } });
 353     is($cd_rs2->count, 5, 'complex prefetch + non-prefetching restricted has_many join count correct');
 354     is($cd_rs2->all, 5, 'complex prefetch + non-prefetching restricted has_many join number of objects correct');
 355
 356     # the outer group_by seems like a necessary evil, if someone can figure out how to take it away
 357     # without breaking compat - be my guest
 358     is_same_sql_bind (
 359       $cd_rs2->as_query,
 360       '(
 361         SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 362                artist.artistid, artist.name, artist.rank, artist.charfield
 363           FROM (
 364             SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 365               FROM cd me
 366               LEFT JOIN track tracks ON tracks.cd = me.cdid
 367               JOIN artist artist ON artist.artistid = me.artist
 368             WHERE ( tracks.title != ? )
 369             GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 370           ) me
 371           LEFT JOIN track tracks ON tracks.cd = me.cdid
 372           JOIN artist artist ON artist.artistid = me.artist
 373         WHERE ( tracks.title != ? )
 374         GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 375                  artist.artistid, artist.name, artist.rank, artist.charfield
 376       )',
 377       [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
 378             => 'ugabuganoexist' ] } (1,2)
 379       ],
 380     );
 381 }
 382
 383 # make sure distinct applies to the CD part only, not to the prefetched/collapsed order_by part
 384 {
 385   my $rs = $schema->resultset('CD')->search({}, {
 386     columns => [qw( cdid title )],
 387     '+select' => [{ count => 'tags.tag' }],
 388     '+as' => ['test_count'],
 389     prefetch => ['tags'],
 390     distinct => 1,
 391     order_by => {'-desc' => 'tags.tag'},
 392     offset => 1,
 393     rows => 3,
 394   });
 395
 396   is_same_sql_bind($rs->as_query,
 397     '(
 398       SELECT me.cdid, me.title, me.test_count,
 399              tags.tagid, tags.cd, tags.tag
 400         FROM (
 401           SELECT  me.cdid, me.title,
 402                   COUNT( tags.tag ) AS test_count
 403             FROM cd me
 404             LEFT JOIN tags tags
 405               ON tags.cd = me.cdid
 406           GROUP BY me.cdid, me.title
 407           ORDER BY MAX( tags.tag ) DESC
 408           LIMIT ?
 409           OFFSET ?
 410         ) me
 411         LEFT JOIN tags tags
 412           ON tags.cd = me.cdid
 413       ORDER BY tags.tag DESC
 414     )',
 415     [ [$ROWS => 3], [$OFFSET => 1] ],
 416     'Expected limited prefetch with distinct SQL',
 417   );
 418
 419   my $expected_hri = [
 420     { cdid => 4, test_count => 2, title => "Generic Manufactured Singles", tags => [
 421       { cd => 4, tag => "Shiny", tagid => 9 },
 422       { cd => 4, tag => "Cheesy", tagid => 6 },
 423     ]},
 424     {
 425       cdid => 5, test_count => 2, title => "Come Be Depressed With Us", tags => [
 426       { cd => 5, tag => "Cheesy", tagid => 7 },
 427       { cd => 5, tag => "Blue", tagid => 4 },
 428     ]},
 429     {
 430       cdid => 1, test_count => 1, title => "Spoonful of bees", tags => [
 431       { cd => 1, tag => "Blue", tagid => 1 },
 432     ]},
 433   ];
 434
 435   is_deeply (
 436     $rs->all_hri,
 437     $expected_hri,
 438     'HRI dump of limited prefetch with distinct as expected'
 439   );
 440
 441   # pre-multiplied main source also should work
 442   $rs = $schema->resultset('CD')->search_related('artist')->search_related('cds', {}, {
 443     columns => [qw( cdid title )],
 444     '+select' => [{ count => 'tags.tag' }],
 445     '+as' => ['test_count'],
 446     prefetch => ['tags'],
 447     distinct => 1,
 448     order_by => {'-desc' => 'tags.tag'},
 449     offset => 1,
 450     rows => 3,
 451   });
 452
 453   is_same_sql_bind($rs->as_query,
 454     '(
 455       SELECT cds.cdid, cds.title, cds.test_count,
 456              tags.tagid, tags.cd, tags.tag
 457         FROM cd me
 458         JOIN artist artist
 459           ON artist.artistid = me.artist
 460         JOIN (
 461           SELECT  cds.cdid, cds.title,
 462                   COUNT( tags.tag ) AS test_count,
 463                   cds.artist
 464             FROM cd me
 465             JOIN artist artist
 466               ON artist.artistid = me.artist
 467             JOIN cd cds
 468               ON cds.artist = artist.artistid
 469             LEFT JOIN tags tags
 470               ON tags.cd = cds.cdid
 471           GROUP BY cds.cdid, cds.title, cds.artist
 472           ORDER BY MAX( tags.tag ) DESC
 473           LIMIT ?
 474           OFFSET ?
 475         ) cds
 476           ON cds.artist = artist.artistid
 477         LEFT JOIN tags tags
 478           ON tags.cd = cds.cdid
 479       ORDER BY tags.tag DESC
 480     )',
 481     [ [$ROWS => 3], [$OFFSET => 1] ],
 482     'Expected limited prefetch with distinct SQL on premultiplied head',
 483   );
 484
 485   # Tag counts are multiplied by the cd->artist->cds multiplication
 486   # I would *almost* call this "expected" without wraping an as_subselect_rs
 487   {
 488     local $TODO = 'Not sure if we can stop the count/group of premultiplication abstraction leak';
 489     is_deeply (
 490       $rs->all_hri,
 491       $expected_hri,
 492       'HRI dump of limited prefetch with distinct as expected on premultiplid head'
 493     );
 494   }
 495 }
 496
 497 done_testing;