Save one _resolve_aliastypes in the case of a subqueried prefetch

[dbsrgits/DBIx-Class.git] / t / count / count_rs.t

diff --git a/t/count/count_rs.t b/t/count/count_rs.t
index 7153d3e..174f630 100644 (file)
--- a/t/count/count_rs.t
+++ b/t/count/count_rs.t
@@ -4,11 +4,13 @@ use warnings;
 use lib qw(t/lib);
 
 use Test::More;
-use DBICTest;
-use DBIC::SqlMakerTest;
-use DBIC::DebugObj;
+use DBICTest ':DiffSQL';
+use DBIx::Class::SQLMaker::LimitDialects;
 
-plan tests => 10;
+my ($ROWS, $OFFSET) = (
+   DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+   DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
 
 my $schema = DBICTest->init_schema();
 
@@ -19,28 +21,25 @@ my $schema = DBICTest->init_schema();
                 { position => [1,2] },
                 { prefetch => [qw/disc lyrics/], rows => 3, offset => 8 },
             );
-  is ($rs->all, 2, 'Correct number of objects');
-
-
-  my ($sql, @bind);
-  $schema->storage->debugobj(DBIC::DebugObj->new(\$sql, \@bind));
-  $schema->storage->debug(1);
+  my @wherebind = (
+    [ { sqlt_datatype => 'int', dbic_colname => 'position' }
+      => 1 ],
+    [ { sqlt_datatype => 'int', dbic_colname => 'position' }
+      => 2 ],
+  );
 
-  is ($rs->count, 2, 'Correct count via count()');
+  is ($rs->all, 2, 'Correct number of objects');
 
-  is_same_sql_bind (
-    $sql,
-    \@bind,
+  $schema->is_executed_sql_bind( sub {
+    is ($rs->count, 2, 'Correct count via count()');
+  }, [[
     'SELECT COUNT( * )
       FROM cd me
-      LEFT JOIN track tracks ON tracks.cd = me.cdid
+      JOIN track tracks ON tracks.cd = me.cdid
       JOIN cd disc ON disc.cdid = tracks.cd
-      LEFT JOIN lyrics lyrics ON lyrics.track_id = tracks.trackid 
      WHERE ( ( position = ? OR position = ? ) )
-    ',
-    [ qw/'1' '2'/ ],
-    'count softlimit applied',
-  );
+    ', @wherebind
+  ]], 'count softlimit applied');
 
   my $crs = $rs->count_rs;
   is ($crs->next, 2, 'Correct count via count_rs()');
@@ -51,14 +50,13 @@ my $schema = DBICTest->init_schema();
        FROM (
         SELECT tracks.trackid
           FROM cd me
-          LEFT JOIN track tracks ON tracks.cd = me.cdid
+          JOIN track tracks ON tracks.cd = me.cdid
           JOIN cd disc ON disc.cdid = tracks.cd
-          LEFT JOIN lyrics lyrics ON lyrics.track_id = tracks.trackid 
         WHERE ( ( position = ? OR position = ? ) )
-        LIMIT 3 OFFSET 8
-       ) count_subq
+        LIMIT ? OFFSET ?
+       ) tracks
     )',
-    [ [ position => 1 ], [ position => 2 ] ],
+    [ @wherebind, [$ROWS => 3], [$OFFSET => 8] ],
     'count_rs db-side limit applied',
   );
 }
@@ -70,31 +68,30 @@ my $schema = DBICTest->init_schema();
                 { 'tracks.position' => [1,2] },
                 { prefetch => [qw/tracks artist/], rows => 3, offset => 4 },
             );
-  is ($rs->all, 1, 'Correct number of objects');
-
-  my ($sql, @bind);
-  $schema->storage->debugobj(DBIC::DebugObj->new(\$sql, \@bind));
-  $schema->storage->debug(1);
+  my @wherebind = (
+    [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' }
+      => 1 ],
+    [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' }
+      => 2 ],
+  );
 
-  is ($rs->count, 1, 'Correct count via count()');
+  is ($rs->all, 1, 'Correct number of objects');
 
-  is_same_sql_bind (
-    $sql,
-    \@bind,
+  $schema->is_executed_sql_bind( sub {
+    is ($rs->count, 1, 'Correct count via count()');
+  }, [ [
     'SELECT COUNT( * )
       FROM (
         SELECT cds.cdid
           FROM artist me
-          LEFT JOIN cd cds ON cds.artist = me.artistid
+          JOIN cd cds ON cds.artist = me.artistid
           LEFT JOIN track tracks ON tracks.cd = cds.cdid
           JOIN artist artist ON artist.artistid = cds.artist
         WHERE tracks.position = ? OR tracks.position = ?
         GROUP BY cds.cdid
-      ) count_subq
-    ',
-    [ qw/'1' '2'/ ],
-    'count softlimit applied',
-  );
+      ) cds
+    ', @wherebind
+  ]], 'count softlimit applied' );
 
   my $crs = $rs->count_rs;
   is ($crs->next, 1, 'Correct count via count_rs()');
@@ -105,15 +102,89 @@ my $schema = DBICTest->init_schema();
       FROM (
         SELECT cds.cdid
           FROM artist me
-          LEFT JOIN cd cds ON cds.artist = me.artistid
+          JOIN cd cds ON cds.artist = me.artistid
           LEFT JOIN track tracks ON tracks.cd = cds.cdid
           JOIN artist artist ON artist.artistid = cds.artist
         WHERE tracks.position = ? OR tracks.position = ?
         GROUP BY cds.cdid
-        LIMIT 3 OFFSET 4
-      ) count_subq
+        LIMIT ? OFFSET ?
+      ) cds
     )',
-    [ [ 'tracks.position' => 1 ], [ 'tracks.position' => 2 ] ],
+    [ @wherebind, [$ROWS => 3], [$OFFSET => 4], ],
     'count_rs db-side limit applied',
   );
 }
+
+# count with a having clause
+{
+  my $rs = $schema->resultset("Artist")->search(
+    {},
+    {
+      join      => 'cds',
+      group_by  => 'me.artistid',
+      '+select' => [ { max => 'cds.year', -as => 'newest_cd_year' } ],
+      '+as'     => ['newest_cd_year'],
+      having    => { 'newest_cd_year' => '2001' }
+    }
+  );
+
+  my $crs = $rs->count_rs;
+
+  is_same_sql_bind (
+    $crs->as_query,
+    '(SELECT COUNT( * )
+      FROM (
+        SELECT me.artistid, MAX( cds.year ) AS newest_cd_year
+          FROM artist me
+          LEFT JOIN cd cds ON cds.artist = me.artistid
+        GROUP BY me.artistid
+        HAVING newest_cd_year = ?
+      ) me
+    )',
+    [ [ { dbic_colname => 'newest_cd_year' }
+          => '2001' ] ],
+    'count with having clause keeps sql as alias',
+  );
+
+  is ($crs->next, 2, 'Correct artist count (each with one 2001 cd)');
+}
+
+# count with two having clauses
+{
+  my $rs = $schema->resultset("Artist")->search(
+    {},
+    {
+      join      => 'cds',
+      group_by  => 'me.artistid',
+      '+select' => [ { max => 'cds.year', -as => 'newest_cd_year' } ],
+      '+as'     => ['newest_cd_year'],
+      having    => { 'newest_cd_year' => [ '1998', '2001' ] }
+    }
+  );
+
+  my $crs = $rs->count_rs;
+
+  is_same_sql_bind (
+    $crs->as_query,
+    '(SELECT COUNT( * )
+      FROM (
+        SELECT me.artistid, MAX( cds.year ) AS newest_cd_year
+          FROM artist me
+          LEFT JOIN cd cds ON cds.artist = me.artistid
+        GROUP BY me.artistid
+        HAVING newest_cd_year = ? OR newest_cd_year = ?
+      ) me
+    )',
+    [
+      [ { dbic_colname => 'newest_cd_year' }
+          => '1998' ],
+      [ { dbic_colname => 'newest_cd_year' }
+          => '2001' ],
+    ],
+    'count with having clause keeps sql as alias',
+  );
+
+  is ($crs->next, 3, 'Correct artist count (each with one 1998 or 2001 cd)');
+}
+
+done_testing;