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