7 use DBICTest ':DiffSQL';
8 use DBIx::Class::SQLMaker::LimitDialects;
10 my ($ROWS, $OFFSET) = (
11 DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
12 DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
15 my $schema = DBICTest->init_schema();
17 # non-collapsing prefetch (no multi prefetches)
19 my $rs = $schema->resultset("CD")
20 ->search_related('tracks',
21 { position => [1,2] },
22 { prefetch => [qw/disc lyrics/], rows => 3, offset => 8 },
25 [ { sqlt_datatype => 'int', dbic_colname => 'position' }
27 [ { sqlt_datatype => 'int', dbic_colname => 'position' }
31 is ($rs->all, 2, 'Correct number of objects');
33 $schema->is_executed_sql_bind( sub {
34 is ($rs->count, 2, 'Correct count via count()');
38 JOIN track tracks ON tracks.cd = me.cdid
39 JOIN cd disc ON disc.cdid = tracks.cd
40 WHERE ( ( position = ? OR position = ? ) )
42 ]], 'count softlimit applied');
44 my $crs = $rs->count_rs;
45 is ($crs->next, 2, 'Correct count via count_rs()');
53 JOIN track tracks ON tracks.cd = me.cdid
54 JOIN cd disc ON disc.cdid = tracks.cd
55 WHERE ( ( position = ? OR position = ? ) )
59 [ @wherebind, [$ROWS => 3], [$OFFSET => 8] ],
60 'count_rs db-side limit applied',
64 # has_many prefetch with limit
66 my $rs = $schema->resultset("Artist")
67 ->search_related('cds',
68 { 'tracks.position' => [1,2] },
69 { prefetch => [qw/tracks artist/], rows => 3, offset => 4 },
72 [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' }
74 [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' }
78 is ($rs->all, 1, 'Correct number of objects');
80 $schema->is_executed_sql_bind( sub {
81 is ($rs->count, 1, 'Correct count via count()');
87 JOIN cd cds ON cds.artist = me.artistid
88 LEFT JOIN track tracks ON tracks.cd = cds.cdid
89 JOIN artist artist ON artist.artistid = cds.artist
90 WHERE tracks.position = ? OR tracks.position = ?
94 ]], 'count softlimit applied' );
96 my $crs = $rs->count_rs;
97 is ($crs->next, 1, 'Correct count via count_rs()');
105 JOIN cd cds ON cds.artist = me.artistid
106 LEFT JOIN track tracks ON tracks.cd = cds.cdid
107 JOIN artist artist ON artist.artistid = cds.artist
108 WHERE tracks.position = ? OR tracks.position = ?
113 [ @wherebind, [$ROWS => 3], [$OFFSET => 4], ],
114 'count_rs db-side limit applied',
118 # count with a having clause
120 my $rs = $schema->resultset("Artist")->search(
124 group_by => 'me.artistid',
125 '+select' => [ { max => 'cds.year', -as => 'newest_cd_year' } ],
126 '+as' => ['newest_cd_year'],
127 having => { 'newest_cd_year' => '2001' }
131 my $crs = $rs->count_rs;
137 SELECT me.artistid, MAX( cds.year ) AS newest_cd_year
139 LEFT JOIN cd cds ON cds.artist = me.artistid
141 HAVING newest_cd_year = ?
144 [ [ { dbic_colname => 'newest_cd_year' }
146 'count with having clause keeps sql as alias',
149 is ($crs->next, 2, 'Correct artist count (each with one 2001 cd)');
152 # count with two having clauses
154 my $rs = $schema->resultset("Artist")->search(
158 group_by => 'me.artistid',
159 '+select' => [ { max => 'cds.year', -as => 'newest_cd_year' } ],
160 '+as' => ['newest_cd_year'],
161 having => { 'newest_cd_year' => [ '1998', '2001' ] }
165 my $crs = $rs->count_rs;
171 SELECT me.artistid, MAX( cds.year ) AS newest_cd_year
173 LEFT JOIN cd cds ON cds.artist = me.artistid
175 HAVING newest_cd_year = ? OR newest_cd_year = ?
179 [ { dbic_colname => 'newest_cd_year' }
181 [ { dbic_colname => 'newest_cd_year' }
184 'count with having clause keeps sql as alias',
187 is ($crs->next, 3, 'Correct artist count (each with one 1998 or 2001 cd)');