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