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