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
  13 my $schema = DBICTest->init_schema();
  14 my $sdebug = $schema->storage->debug;
  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   {
  52     my $query_cnt = 0;
  53     $schema->storage->debugcb ( sub { $query_cnt++ } );
  54     $schema->storage->debug (1);
  55
  56     while (my $collapsed_track = $track_rs->next) {
  57       my $cdid = $collapsed_track->get_column('cd');
  58       is($collapsed_track->get_column('track_count'), 3, "Correct count of tracks for CD $cdid" );
  59       ok($collapsed_track->cd->title, "Prefetched title for CD $cdid" );
  60     }
  61
  62     is ($query_cnt, 1, 'Single query on prefetched titles');
  63     $schema->storage->debugcb (undef);
  64     $schema->storage->debug ($sdebug);
  65   }
  66
  67   # Test sql by hand, as the sqlite db will simply paper over
  68   # improper group/select combinations
  69   #
  70   is_same_sql_bind (
  71     $track_rs->count_rs->as_query,
  72     '(
  73       SELECT COUNT( * )
  74         FROM (
  75           SELECT me.cd
  76             FROM track me
  77             JOIN cd cd ON cd.cdid = me.cd
  78           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  79           GROUP BY me.cd
  80         )
  81       me
  82     )',
  83     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
  84       => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
  85     'count() query generated expected SQL',
  86   );
  87
  88   is_same_sql_bind (
  89     $track_rs->as_query,
  90     '(
  91       SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track
  92         FROM (
  93           SELECT me.cd, COUNT (me.trackid) AS track_count
  94             FROM track me
  95             JOIN cd cd ON cd.cdid = me.cd
  96           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
  97           GROUP BY me.cd
  98           ) me
  99         JOIN cd cd ON cd.cdid = me.cd
 100       WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
 101     )',
 102     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
 103       => $_ ] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ],
 104     'next() query generated expected SQL',
 105   );
 106
 107
 108   # add an extra track to one of the cds, and then make sure we can get it on top
 109   # (check if limit works)
 110   my $top_cd = $cd_rs->slice (1,1)->next;
 111   $top_cd->create_related ('tracks', {
 112     title => 'over the top',
 113   });
 114
 115   my $top_cd_collapsed_track = $track_rs->search ({}, {
 116     rows => 2,
 117     order_by => [
 118       { -desc => 'track_count' },
 119     ],
 120   });
 121
 122   is ($top_cd_collapsed_track->count, 2);
 123
 124   is (
 125     $top_cd->title,
 126     $top_cd_collapsed_track->first->cd->title,
 127     'Correct collapsed track with prefetched CD returned on top'
 128   );
 129 }
 130
 131 # test a has_many/might_have prefetch at the same level
 132 # Note that one of the CDs now has 4 tracks instead of 3
 133 {
 134   my $most_tracks_rs = $schema->resultset ('CD')->search (
 135     {
 136       'me.cdid' => { '!=' => undef },  # duh - this is just to test WHERE
 137     },
 138     {
 139       prefetch => [qw/tracks liner_notes/],
 140       select => ['me.cdid', { count => 'tracks.trackid' }, { max => 'tracks.trackid', -as => 'maxtr'} ],
 141       as => [qw/cdid track_count max_track_id/],
 142       group_by => 'me.cdid',
 143       order_by => [ { -desc => 'track_count' }, { -asc => 'maxtr' } ],
 144       rows => 2,
 145     }
 146   );
 147
 148   is_same_sql_bind (
 149     $most_tracks_rs->count_rs->as_query,
 150     '(
 151       SELECT COUNT( * )
 152         FROM (
 153           SELECT me.cdid
 154             FROM cd me
 155           WHERE ( me.cdid IS NOT NULL )
 156           GROUP BY me.cdid
 157           LIMIT ?
 158         ) me
 159     )',
 160     [[$ROWS => 2]],
 161     'count() query generated expected SQL',
 162   );
 163
 164   is_same_sql_bind (
 165     $most_tracks_rs->as_query,
 166     '(
 167       SELECT  me.cdid, me.track_count, me.maxtr,
 168               tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
 169               liner_notes.liner_id, liner_notes.notes
 170         FROM (
 171           SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr
 172             FROM cd me
 173             LEFT JOIN track tracks ON tracks.cd = me.cdid
 174           WHERE ( me.cdid IS NOT NULL )
 175           GROUP BY me.cdid
 176           ORDER BY track_count DESC, maxtr ASC
 177           LIMIT ?
 178         ) me
 179         LEFT JOIN track tracks ON tracks.cd = me.cdid
 180         LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
 181       WHERE ( me.cdid IS NOT NULL )
 182       ORDER BY track_count DESC, maxtr ASC
 183     )',
 184     [[$ROWS => 2]],
 185     'next() query generated expected SQL',
 186   );
 187
 188   is ($most_tracks_rs->count, 2, 'Limit works');
 189   my ($top_cd) = $most_tracks_rs->all;
 190   is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
 191
 192   my $query_cnt = 0;
 193   $schema->storage->debugcb ( sub { $query_cnt++ } );
 194   $schema->storage->debug (1);
 195
 196   is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
 197   is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct');
 198   is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct');
 199   is (
 200     $top_cd->liner_notes->notes,
 201     'Buy Whiskey!',
 202     'Correct liner pre-fetched with top cd',
 203   );
 204
 205   is ($query_cnt, 0, 'No queries executed during prefetched data access');
 206   $schema->storage->debugcb (undef);
 207   $schema->storage->debug ($sdebug);
 208 }
 209
 210 {
 211   # test lifted from soulchild
 212
 213   my $most_tracks_rs = $schema->resultset ('CD')->search (
 214     {
 215       'me.cdid' => { '!=' => undef },  # this is just to test WHERE
 216       'tracks.trackid' => { '!=' => undef },
 217     },
 218     {
 219       join => 'tracks',
 220       prefetch => 'liner_notes',
 221       select => ['me.cdid', 'liner_notes.notes', { count => 'tracks.trackid', -as => 'tr_count' }, { max => 'tracks.trackid', -as => 'tr_maxid'} ],
 222       as => [qw/cdid notes track_count max_track_id/],
 223       order_by => [ { -desc => 'tr_count' }, { -asc => 'tr_maxid' } ],
 224       group_by => 'me.cdid',
 225       rows => 2,
 226     }
 227   );
 228
 229   is_same_sql_bind(
 230     $most_tracks_rs->as_query,
 231     '(SELECT  me.cdid, liner_notes.notes, me.tr_count, me.tr_maxid,
 232               liner_notes.liner_id, liner_notes.notes
 233         FROM (
 234           SELECT me.cdid, COUNT(tracks.trackid) AS tr_count, MAX(tracks.trackid) AS tr_maxid
 235             FROM cd me
 236             LEFT JOIN track tracks
 237               ON tracks.cd = me.cdid
 238           WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL
 239           GROUP BY me.cdid
 240           ORDER BY tr_count DESC, tr_maxid ASC
 241           LIMIT ?
 242         ) me
 243         LEFT JOIN track tracks
 244           ON tracks.cd = me.cdid
 245         LEFT JOIN liner_notes liner_notes
 246           ON liner_notes.liner_id = me.cdid
 247       WHERE me.cdid IS NOT NULL AND tracks.trackid IS NOT NULL
 248       ORDER BY tr_count DESC, tr_maxid ASC
 249     )',
 250     [[$ROWS => 2]],
 251     'Oddball mysql-ish group_by usage yields valid SQL',
 252   );
 253
 254   is ($most_tracks_rs->count, 2, 'Limit works');
 255   my ($top_cd) = $most_tracks_rs->all;
 256   is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
 257
 258   my $query_cnt = 0;
 259   $schema->storage->debugcb ( sub { $query_cnt++ } );
 260   $schema->storage->debug (1);
 261
 262   is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
 263   is (
 264     $top_cd->liner_notes->notes,
 265     'Buy Whiskey!',
 266     'Correct liner pre-fetched with top cd',
 267   );
 268
 269   is ($query_cnt, 0, 'No queries executed during prefetched data access');
 270   $schema->storage->debugcb (undef);
 271   $schema->storage->debug ($sdebug);
 272 }
 273
 274
 275 # make sure that distinct still works
 276 {
 277   my $rs = $schema->resultset("CD")->search({}, {
 278     prefetch => 'tags',
 279     order_by => 'cdid',
 280     distinct => 1,
 281   });
 282
 283   is_same_sql_bind (
 284     $rs->as_query,
 285     '(
 286       SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 287              tags.tagid, tags.cd, tags.tag
 288         FROM (
 289           SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 290             FROM cd me
 291           GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 292         ) me
 293         LEFT JOIN tags tags ON tags.cd = me.cdid
 294       ORDER BY cdid
 295     )',
 296     [],
 297     'Prefetch + distinct resulted in correct group_by',
 298   );
 299
 300   is ($rs->all, 5, 'Correct number of CD objects');
 301   is ($rs->count, 5, 'Correct count of CDs');
 302 }
 303
 304 # RT 47779, test group_by as a scalar ref
 305 {
 306   my $track_rs = $schema->resultset ('Track')->search (
 307     { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
 308     {
 309       select => [
 310         'me.cd',
 311         { count => 'me.trackid' },
 312       ],
 313       as => [qw/
 314         cd
 315         track_count
 316       /],
 317       group_by => \'SUBSTR(me.cd, 1, 1)',
 318       prefetch => 'cd',
 319     },
 320   );
 321
 322   is_same_sql_bind (
 323     $track_rs->count_rs->as_query,
 324     '(
 325       SELECT COUNT( * )
 326         FROM (
 327           SELECT SUBSTR(me.cd, 1, 1)
 328             FROM track me
 329             JOIN cd cd ON cd.cdid = me.cd
 330           WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
 331           GROUP BY SUBSTR(me.cd, 1, 1)
 332         )
 333       me
 334     )',
 335     [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' }
 336       => $_ ] } ($cd_rs->get_column ('cdid')->all) ],
 337     'count() query generated expected SQL',
 338   );
 339 }
 340
 341 {
 342     my $cd_rs = $schema->resultset('CD')->search({}, {
 343             distinct => 1,
 344             join     => [qw/ tracks /],
 345             prefetch => [qw/ artist /],
 346         });
 347     is($cd_rs->count, 5, 'complex prefetch + non-prefetching has_many join count correct');
 348     is($cd_rs->all, 5, 'complex prefetch + non-prefetching has_many join number of objects correct');
 349
 350     # make sure join tracks was thrown out
 351     is_same_sql_bind (
 352       $cd_rs->as_query,
 353       '(
 354         SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 355                artist.artistid, artist.name, artist.rank, artist.charfield
 356           FROM (
 357             SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 358               FROM cd me
 359               JOIN artist artist ON artist.artistid = me.artist
 360             GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 361           ) me
 362           JOIN artist artist ON artist.artistid = me.artist
 363       )',
 364       [],
 365     );
 366
 367
 368
 369     # try the same as above, but add a condition so the tracks join can not be thrown away
 370     my $cd_rs2 = $cd_rs->search ({ 'tracks.title' => { '!=' => 'ugabuganoexist' } });
 371     is($cd_rs2->count, 5, 'complex prefetch + non-prefetching restricted has_many join count correct');
 372     is($cd_rs2->all, 5, 'complex prefetch + non-prefetching restricted has_many join number of objects correct');
 373
 374     # the outer group_by seems like a necessary evil, if someone can figure out how to take it away
 375     # without breaking compat - be my guest
 376     is_same_sql_bind (
 377       $cd_rs2->as_query,
 378       '(
 379         SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 380                artist.artistid, artist.name, artist.rank, artist.charfield
 381           FROM (
 382             SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 383               FROM cd me
 384               LEFT JOIN track tracks ON tracks.cd = me.cdid
 385               JOIN artist artist ON artist.artistid = me.artist
 386             WHERE ( tracks.title != ? )
 387             GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
 388           ) me
 389           LEFT JOIN track tracks ON tracks.cd = me.cdid
 390           JOIN artist artist ON artist.artistid = me.artist
 391         WHERE ( tracks.title != ? )
 392         GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
 393                  artist.artistid, artist.name, artist.rank, artist.charfield
 394       )',
 395       [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
 396             => 'ugabuganoexist' ] } (1,2)
 397       ],
 398     );
 399 }
 400
 401 {
 402     my $rs = $schema->resultset('CD')->search({},
 403         {
 404            '+select' => [{ count => 'tags.tag' }],
 405            '+as' => ['test_count'],
 406            prefetch => ['tags'],
 407            distinct => 1,
 408            order_by => {'-asc' => 'tags.tag'},
 409            rows => 1
 410         }
 411     );
 412     is_same_sql_bind($rs->as_query, q{
 413         (SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, me.test_count, tags.tagid, tags.cd, tags.tag
 414           FROM (SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, COUNT( tags.tag ) AS test_count
 415                 FROM cd me LEFT JOIN tags tags ON tags.cd = me.cdid
 416             GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, tags.tag
 417             ORDER BY tags.tag ASC LIMIT ?)
 418             me
 419           LEFT JOIN tags tags ON tags.cd = me.cdid
 420          ORDER BY tags.tag ASC
 421         )
 422     }, [[$ROWS => 1]]);
 423 }
 424
 425 done_testing;