Introduce GOVERNANCE document and empty RESOLUTIONS file.
[dbsrgits/DBIx-Class.git] / t / count / count_rs.t
CommitLineData
c0329273 1BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
2
a70f69d1 3use strict;
4use warnings;
5
a70f69d1 6use Test::More;
a5a7bb73 7use DBICTest ':DiffSQL';
fcb7fcbb 8use DBIx::Class::SQLMaker::LimitDialects;
9
10my ($ROWS, $OFFSET) = (
11 DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
12 DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
13);
a70f69d1 14
a70f69d1 15my $schema = DBICTest->init_schema();
16
17# non-collapsing prefetch (no multi prefetches)
18{
19 my $rs = $schema->resultset("CD")
20 ->search_related('tracks',
21 { position => [1,2] },
22 { prefetch => [qw/disc lyrics/], rows => 3, offset => 8 },
23 );
2cfc22dd 24 my @wherebind = (
25 [ { sqlt_datatype => 'int', dbic_colname => 'position' }
26 => 1 ],
27 [ { sqlt_datatype => 'int', dbic_colname => 'position' }
28 => 2 ],
29 );
a70f69d1 30
2cfc22dd 31 is ($rs->all, 2, 'Correct number of objects');
a70f69d1 32
2cfc22dd 33 $schema->is_executed_sql_bind( sub {
34 is ($rs->count, 2, 'Correct count via count()');
35 }, [[
a70f69d1 36 'SELECT COUNT( * )
37 FROM cd me
c98169a7 38 JOIN track tracks ON tracks.cd = me.cdid
a70f69d1 39 JOIN cd disc ON disc.cdid = tracks.cd
a70f69d1 40 WHERE ( ( position = ? OR position = ? ) )
2cfc22dd 41 ', @wherebind
42 ]], 'count softlimit applied');
a70f69d1 43
44 my $crs = $rs->count_rs;
45 is ($crs->next, 2, 'Correct count via count_rs()');
46
47 is_same_sql_bind (
48 $crs->as_query,
49 '(SELECT COUNT( * )
50 FROM (
51 SELECT tracks.trackid
52 FROM cd me
c98169a7 53 JOIN track tracks ON tracks.cd = me.cdid
a70f69d1 54 JOIN cd disc ON disc.cdid = tracks.cd
a70f69d1 55 WHERE ( ( position = ? OR position = ? ) )
fcb7fcbb 56 LIMIT ? OFFSET ?
336feb8e 57 ) tracks
a70f69d1 58 )',
2cfc22dd 59 [ @wherebind, [$ROWS => 3], [$OFFSET => 8] ],
a70f69d1 60 'count_rs db-side limit applied',
61 );
62}
63
64# has_many prefetch with limit
65{
66 my $rs = $schema->resultset("Artist")
67 ->search_related('cds',
68 { 'tracks.position' => [1,2] },
69 { prefetch => [qw/tracks artist/], rows => 3, offset => 4 },
70 );
2cfc22dd 71 my @wherebind = (
72 [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' }
73 => 1 ],
74 [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' }
75 => 2 ],
76 );
a70f69d1 77
2cfc22dd 78 is ($rs->all, 1, 'Correct number of objects');
a70f69d1 79
2cfc22dd 80 $schema->is_executed_sql_bind( sub {
81 is ($rs->count, 1, 'Correct count via count()');
82 }, [ [
a70f69d1 83 'SELECT COUNT( * )
84 FROM (
85 SELECT cds.cdid
86 FROM artist me
c98169a7 87 JOIN cd cds ON cds.artist = me.artistid
a70f69d1 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 = ?
91 GROUP BY cds.cdid
336feb8e 92 ) cds
2cfc22dd 93 ', @wherebind
94 ]], 'count softlimit applied' );
a70f69d1 95
96 my $crs = $rs->count_rs;
97 is ($crs->next, 1, 'Correct count via count_rs()');
98
99 is_same_sql_bind (
100 $crs->as_query,
101 '(SELECT COUNT( * )
102 FROM (
103 SELECT cds.cdid
104 FROM artist me
c98169a7 105 JOIN cd cds ON cds.artist = me.artistid
a70f69d1 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 = ?
109 GROUP BY cds.cdid
fcb7fcbb 110 LIMIT ? OFFSET ?
336feb8e 111 ) cds
a70f69d1 112 )',
2cfc22dd 113 [ @wherebind, [$ROWS => 3], [$OFFSET => 4], ],
a70f69d1 114 'count_rs db-side limit applied',
115 );
116}
e493ecb2 117
fcb7fcbb 118# count with a having clause
e493ecb2 119{
120 my $rs = $schema->resultset("Artist")->search(
121 {},
122 {
123 join => 'cds',
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' }
128 }
129 );
130
131 my $crs = $rs->count_rs;
132
133 is_same_sql_bind (
134 $crs->as_query,
135 '(SELECT COUNT( * )
136 FROM (
b19b11ef 137 SELECT me.artistid, MAX( cds.year ) AS newest_cd_year
fcb7fcbb 138 FROM artist me
139 LEFT JOIN cd cds ON cds.artist = me.artistid
140 GROUP BY me.artistid
e493ecb2 141 HAVING newest_cd_year = ?
142 ) me
143 )',
0e773352 144 [ [ { dbic_colname => 'newest_cd_year' }
145 => '2001' ] ],
e493ecb2 146 'count with having clause keeps sql as alias',
147 );
148
149 is ($crs->next, 2, 'Correct artist count (each with one 2001 cd)');
150}
151
913b4bae 152# count with two having clauses
153{
154 my $rs = $schema->resultset("Artist")->search(
155 {},
156 {
157 join => 'cds',
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' ] }
162 }
163 );
164
165 my $crs = $rs->count_rs;
166
167 is_same_sql_bind (
168 $crs->as_query,
169 '(SELECT COUNT( * )
170 FROM (
171 SELECT me.artistid, MAX( cds.year ) AS newest_cd_year
172 FROM artist me
173 LEFT JOIN cd cds ON cds.artist = me.artistid
174 GROUP BY me.artistid
175 HAVING newest_cd_year = ? OR newest_cd_year = ?
176 ) me
177 )',
178 [
179 [ { dbic_colname => 'newest_cd_year' }
180 => '1998' ],
181 [ { dbic_colname => 'newest_cd_year' }
182 => '2001' ],
183 ],
184 'count with having clause keeps sql as alias',
185 );
186
187 is ($crs->next, 3, 'Correct artist count (each with one 1998 or 2001 cd)');
188}
189
e493ecb2 190done_testing;