+BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
+
use strict;
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;
+
+my ($ROWS, $OFFSET) = (
+ DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
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
JOIN track tracks ON tracks.cd = me.cdid
JOIN cd disc ON disc.cdid = tracks.cd
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()');
JOIN track tracks ON tracks.cd = me.cdid
JOIN cd disc ON disc.cdid = tracks.cd
WHERE ( ( position = ? OR position = ? ) )
- LIMIT 3 OFFSET 8
+ LIMIT ? OFFSET ?
) tracks
)',
- [ [ position => 1 ], [ position => 2 ] ],
+ [ @wherebind, [$ROWS => 3], [$OFFSET => 8] ],
'count_rs db-side limit applied',
);
}
{ '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
WHERE tracks.position = ? OR tracks.position = ?
GROUP BY cds.cdid
) cds
- ',
- [ qw/'1' '2'/ ],
- 'count softlimit applied',
- );
+ ', @wherebind
+ ]], 'count softlimit applied' );
my $crs = $rs->count_rs;
is ($crs->next, 1, 'Correct count via count_rs()');
JOIN artist artist ON artist.artistid = cds.artist
WHERE tracks.position = ? OR tracks.position = ?
GROUP BY cds.cdid
- LIMIT 3 OFFSET 4
+ 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
+# count with a having clause
{
my $rs = $schema->resultset("Artist")->search(
{},
$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
+ 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
)',
- [ [ 'newest_cd_year' => '2001' ],],
+ [ [ { 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;