Commit | Line | Data |
0c5ea449 |
1 | use strict; |
2 | use warnings; |
6841b059 |
3 | |
0c5ea449 |
4 | use Test::More; |
5 | |
6 | use lib qw(t/lib); |
7 | use DBICTest; |
8 | use DBIC::SqlMakerTest; |
fcb7fcbb |
9 | use DBIx::Class::SQLMaker::LimitDialects; |
10 | |
11 | my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype; |
0c5ea449 |
12 | |
0c5ea449 |
13 | my $schema = DBICTest->init_schema(); |
a2287768 |
14 | my $sdebug = $schema->storage->debug; |
0c5ea449 |
15 | |
16 | my $cd_rs = $schema->resultset('CD')->search ( |
17 | { 'tracks.cd' => { '!=', undef } }, |
18 | { prefetch => 'tracks' }, |
19 | ); |
20 | |
21 | # Database sanity check |
22 | is($cd_rs->count, 5, 'CDs with tracks count'); |
23 | for ($cd_rs->all) { |
24 | is ($_->tracks->count, 3, '3 tracks for CD' . $_->id ); |
25 | } |
26 | |
27 | # Test a belongs_to prefetch of a has_many |
28 | { |
29 | my $track_rs = $schema->resultset ('Track')->search ( |
30 | { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } }, |
31 | { |
0c5ea449 |
32 | select => [ |
33 | 'me.cd', |
0c5ea449 |
34 | { count => 'me.trackid' }, |
35 | ], |
36 | as => [qw/ |
37 | cd |
38 | track_count |
0c5ea449 |
39 | /], |
40 | group_by => [qw/me.cd/], |
41 | prefetch => 'cd', |
42 | }, |
43 | ); |
44 | |
22ed9526 |
45 | # this used to fuck up ->all, do not remove! |
46 | ok ($track_rs->first, 'There is stuff in the rs'); |
47 | |
0c5ea449 |
48 | is($track_rs->count, 5, 'Prefetched count with groupby'); |
49 | is($track_rs->all, 5, 'Prefetched objects with groupby'); |
50 | |
51 | { |
52 | my $query_cnt = 0; |
53 | $schema->storage->debugcb ( sub { $query_cnt++ } ); |
a2287768 |
54 | $schema->storage->debug (1); |
0c5ea449 |
55 | |
0c5ea449 |
56 | while (my $collapsed_track = $track_rs->next) { |
0c5ea449 |
57 | my $cdid = $collapsed_track->get_column('cd'); |
58 | is($collapsed_track->get_column('track_count'), 3, "Correct count of tracks for CD $cdid" ); |
59 | ok($collapsed_track->cd->title, "Prefetched title for CD $cdid" ); |
60 | } |
61 | |
22ed9526 |
62 | is ($query_cnt, 1, 'Single query on prefetched titles'); |
0c5ea449 |
63 | $schema->storage->debugcb (undef); |
a2287768 |
64 | $schema->storage->debug ($sdebug); |
0c5ea449 |
65 | } |
66 | |
67 | # Test sql by hand, as the sqlite db will simply paper over |
68 | # improper group/select combinations |
69 | # |
0c5ea449 |
70 | is_same_sql_bind ( |
71 | $track_rs->count_rs->as_query, |
72 | '( |
73 | SELECT COUNT( * ) |
74 | FROM ( |
75 | SELECT me.cd |
76 | FROM track me |
77 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
78 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
79 | GROUP BY me.cd |
80 | ) |
336feb8e |
81 | me |
0c5ea449 |
82 | )', |
0e773352 |
83 | [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' } |
84 | => $_ ] } ($cd_rs->get_column ('cdid')->all) ], |
0c5ea449 |
85 | 'count() query generated expected SQL', |
86 | ); |
87 | |
0c5ea449 |
88 | is_same_sql_bind ( |
89 | $track_rs->as_query, |
90 | '( |
5b45001f |
91 | SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track |
0c5ea449 |
92 | FROM ( |
0491b597 |
93 | SELECT me.cd, COUNT (me.trackid) AS track_count |
0c5ea449 |
94 | FROM track me |
1c1937b7 |
95 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
96 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
97 | GROUP BY me.cd |
0491b597 |
98 | ) me |
0c5ea449 |
99 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
100 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
101 | )', |
0e773352 |
102 | [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' } |
103 | => $_ ] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ], |
0c5ea449 |
104 | 'next() query generated expected SQL', |
105 | ); |
106 | |
107 | |
108 | # add an extra track to one of the cds, and then make sure we can get it on top |
109 | # (check if limit works) |
110 | my $top_cd = $cd_rs->slice (1,1)->next; |
111 | $top_cd->create_related ('tracks', { |
112 | title => 'over the top', |
113 | }); |
114 | |
115 | my $top_cd_collapsed_track = $track_rs->search ({}, { |
116 | rows => 2, |
117 | order_by => [ |
118 | { -desc => 'track_count' }, |
119 | ], |
120 | }); |
121 | |
122 | is ($top_cd_collapsed_track->count, 2); |
123 | |
124 | is ( |
125 | $top_cd->title, |
126 | $top_cd_collapsed_track->first->cd->title, |
127 | 'Correct collapsed track with prefetched CD returned on top' |
128 | ); |
129 | } |
130 | |
131 | # test a has_many/might_have prefetch at the same level |
0c5ea449 |
132 | # Note that one of the CDs now has 4 tracks instead of 3 |
133 | { |
dace9819 |
134 | my $most_tracks_rs = $schema->resultset ('CD')->search ( |
135 | { |
136 | 'me.cdid' => { '!=' => undef }, # duh - this is just to test WHERE |
137 | }, |
138 | { |
139 | prefetch => [qw/tracks liner_notes/], |
50136dd9 |
140 | select => ['me.cdid', { count => 'tracks.trackid' }, { max => 'tracks.trackid', -as => 'maxtr'} ], |
141 | as => [qw/cdid track_count max_track_id/], |
dace9819 |
142 | group_by => 'me.cdid', |
50136dd9 |
143 | order_by => [ { -desc => 'track_count' }, { -asc => 'maxtr' } ], |
dace9819 |
144 | rows => 2, |
145 | } |
146 | ); |
0c5ea449 |
147 | |
148 | is_same_sql_bind ( |
149 | $most_tracks_rs->count_rs->as_query, |
150 | '( |
151 | SELECT COUNT( * ) |
152 | FROM ( |
153 | SELECT me.cdid |
154 | FROM cd me |
dace9819 |
155 | WHERE ( me.cdid IS NOT NULL ) |
0c5ea449 |
156 | GROUP BY me.cdid |
fcb7fcbb |
157 | LIMIT ? |
336feb8e |
158 | ) me |
0c5ea449 |
159 | )', |
fcb7fcbb |
160 | [[$ROWS => 2]], |
0c5ea449 |
161 | 'count() query generated expected SQL', |
162 | ); |
163 | |
164 | is_same_sql_bind ( |
165 | $most_tracks_rs->as_query, |
166 | '( |
dc81dba3 |
167 | SELECT me.cdid, me.track_count, me.maxtr, |
3d98c75e |
168 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, |
8bc3fbf5 |
169 | liner_notes.liner_id, liner_notes.notes |
0c5ea449 |
170 | FROM ( |
dc81dba3 |
171 | SELECT me.cdid, COUNT( tracks.trackid ) AS track_count, MAX( tracks.trackid ) AS maxtr |
0c5ea449 |
172 | FROM cd me |
173 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
dace9819 |
174 | WHERE ( me.cdid IS NOT NULL ) |
0c5ea449 |
175 | GROUP BY me.cdid |
50136dd9 |
176 | ORDER BY track_count DESC, maxtr ASC |
fcb7fcbb |
177 | LIMIT ? |
0c5ea449 |
178 | ) me |
179 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
180 | LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid |
dace9819 |
181 | WHERE ( me.cdid IS NOT NULL ) |
908aa1bb |
182 | ORDER BY track_count DESC, maxtr ASC |
0c5ea449 |
183 | )', |
fcb7fcbb |
184 | [[$ROWS => 2]], |
0c5ea449 |
185 | 'next() query generated expected SQL', |
186 | ); |
187 | |
188 | is ($most_tracks_rs->count, 2, 'Limit works'); |
189 | my $top_cd = $most_tracks_rs->first; |
22ed9526 |
190 | is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier |
0c5ea449 |
191 | |
192 | my $query_cnt = 0; |
193 | $schema->storage->debugcb ( sub { $query_cnt++ } ); |
a2287768 |
194 | $schema->storage->debug (1); |
0c5ea449 |
195 | |
196 | is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly'); |
197 | is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct'); |
198 | is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct'); |
199 | is ( |
200 | $top_cd->liner_notes->notes, |
201 | 'Buy Whiskey!', |
202 | 'Correct liner pre-fetched with top cd', |
203 | ); |
204 | |
205 | is ($query_cnt, 0, 'No queries executed during prefetched data access'); |
206 | $schema->storage->debugcb (undef); |
a2287768 |
207 | $schema->storage->debug ($sdebug); |
0c5ea449 |
208 | } |
f785d72e |
209 | |
210 | # make sure that distinct still works |
211 | { |
212 | my $rs = $schema->resultset("CD")->search({}, { |
213 | prefetch => 'tags', |
214 | order_by => 'cdid', |
215 | distinct => 1, |
216 | }); |
217 | |
218 | is_same_sql_bind ( |
219 | $rs->as_query, |
220 | '( |
221 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
d59eba65 |
222 | tags.tagid, tags.cd, tags.tag |
f785d72e |
223 | FROM ( |
224 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
225 | FROM cd me |
d955e938 |
226 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
f785d72e |
227 | ORDER BY cdid |
228 | ) me |
229 | LEFT JOIN tags tags ON tags.cd = me.cdid |
908aa1bb |
230 | ORDER BY cdid |
f785d72e |
231 | )', |
232 | [], |
233 | 'Prefetch + distinct resulted in correct group_by', |
234 | ); |
235 | |
236 | is ($rs->all, 5, 'Correct number of CD objects'); |
237 | is ($rs->count, 5, 'Correct count of CDs'); |
238 | } |
6841b059 |
239 | |
ffad45f5 |
240 | # RT 47779, test group_by as a scalar ref |
241 | { |
242 | my $track_rs = $schema->resultset ('Track')->search ( |
243 | { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } }, |
244 | { |
245 | select => [ |
246 | 'me.cd', |
247 | { count => 'me.trackid' }, |
248 | ], |
249 | as => [qw/ |
250 | cd |
251 | track_count |
252 | /], |
253 | group_by => \'SUBSTR(me.cd, 1, 1)', |
254 | prefetch => 'cd', |
255 | }, |
256 | ); |
257 | |
258 | is_same_sql_bind ( |
259 | $track_rs->count_rs->as_query, |
260 | '( |
261 | SELECT COUNT( * ) |
262 | FROM ( |
efd7c5e5 |
263 | SELECT SUBSTR(me.cd, 1, 1) |
ffad45f5 |
264 | FROM track me |
265 | JOIN cd cd ON cd.cdid = me.cd |
266 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
267 | GROUP BY SUBSTR(me.cd, 1, 1) |
268 | ) |
336feb8e |
269 | me |
ffad45f5 |
270 | )', |
0e773352 |
271 | [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.cd' } |
272 | => $_ ] } ($cd_rs->get_column ('cdid')->all) ], |
ffad45f5 |
273 | 'count() query generated expected SQL', |
274 | ); |
275 | } |
276 | |
8423891f |
277 | { |
d8ce00f5 |
278 | my $cd_rs = $schema->resultset('CD')->search({}, { |
8423891f |
279 | distinct => 1, |
280 | join => [qw/ tracks /], |
281 | prefetch => [qw/ artist /], |
282 | }); |
d8ce00f5 |
283 | is($cd_rs->count, 5, 'complex prefetch + non-prefetching has_many join count correct'); |
284 | is($cd_rs->all, 5, 'complex prefetch + non-prefetching has_many join number of objects correct'); |
285 | |
7cc643b4 |
286 | # make sure join tracks was thrown out |
287 | is_same_sql_bind ( |
288 | $cd_rs->as_query, |
289 | '( |
290 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
291 | artist.artistid, artist.name, artist.rank, artist.charfield |
292 | FROM ( |
293 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
294 | FROM cd me |
295 | JOIN artist artist ON artist.artistid = me.artist |
296 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
297 | ) me |
298 | JOIN artist artist ON artist.artistid = me.artist |
4e9fc3f3 |
299 | ORDER BY me.cdid |
7cc643b4 |
300 | )', |
301 | [], |
302 | ); |
303 | |
304 | |
305 | |
d8ce00f5 |
306 | # try the same as above, but add a condition so the tracks join can not be thrown away |
307 | my $cd_rs2 = $cd_rs->search ({ 'tracks.title' => { '!=' => 'ugabuganoexist' } }); |
308 | is($cd_rs2->count, 5, 'complex prefetch + non-prefetching restricted has_many join count correct'); |
309 | is($cd_rs2->all, 5, 'complex prefetch + non-prefetching restricted has_many join number of objects correct'); |
310 | |
311 | # the outer group_by seems like a necessary evil, if someone can figure out how to take it away |
312 | # without breaking compat - be my guest |
313 | is_same_sql_bind ( |
314 | $cd_rs2->as_query, |
315 | '( |
316 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
317 | artist.artistid, artist.name, artist.rank, artist.charfield |
318 | FROM ( |
319 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
320 | FROM cd me |
321 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
322 | JOIN artist artist ON artist.artistid = me.artist |
323 | WHERE ( tracks.title != ? ) |
324 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
325 | ) me |
326 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
327 | JOIN artist artist ON artist.artistid = me.artist |
328 | WHERE ( tracks.title != ? ) |
329 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
330 | artist.artistid, artist.name, artist.rank, artist.charfield |
4e9fc3f3 |
331 | ORDER BY me.cdid |
d8ce00f5 |
332 | )', |
0e773352 |
333 | [ map { [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' } |
334 | => 'ugabuganoexist' ] } (1,2) |
335 | ], |
d8ce00f5 |
336 | ); |
8423891f |
337 | } |
338 | |
d59eba65 |
339 | { |
340 | my $rs = $schema->resultset('CD')->search({}, |
341 | { |
342 | '+select' => [{ count => 'tags.tag' }], |
343 | '+as' => ['test_count'], |
344 | prefetch => ['tags'], |
345 | distinct => 1, |
346 | order_by => {'-asc' => 'tags.tag'}, |
347 | rows => 1 |
348 | } |
349 | ); |
350 | is_same_sql_bind($rs->as_query, q{ |
886d0b49 |
351 | (SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, me.test_count, tags.tagid, tags.cd, tags.tag |
352 | FROM (SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, COUNT( tags.tag ) AS test_count |
d59eba65 |
353 | FROM cd me LEFT JOIN tags tags ON tags.cd = me.cdid |
354 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, tags.tag |
fcb7fcbb |
355 | ORDER BY tags.tag ASC LIMIT ?) |
d59eba65 |
356 | me |
886d0b49 |
357 | LEFT JOIN tags tags ON tags.cd = me.cdid |
908aa1bb |
358 | ORDER BY tags.tag ASC |
886d0b49 |
359 | ) |
fcb7fcbb |
360 | }, [[$ROWS => 1]]); |
d59eba65 |
361 | } |
362 | |
6841b059 |
363 | done_testing; |