Commit | Line | Data |
0c5ea449 |
1 | use strict; |
2 | use warnings; |
6841b059 |
3 | |
0c5ea449 |
4 | use Test::More; |
6841b059 |
5 | use Test::Exception; |
0c5ea449 |
6 | |
7 | use lib qw(t/lib); |
8 | use DBICTest; |
9 | use DBIC::SqlMakerTest; |
10 | |
0c5ea449 |
11 | my $schema = DBICTest->init_schema(); |
a2287768 |
12 | my $sdebug = $schema->storage->debug; |
0c5ea449 |
13 | |
14 | my $cd_rs = $schema->resultset('CD')->search ( |
15 | { 'tracks.cd' => { '!=', undef } }, |
16 | { prefetch => 'tracks' }, |
17 | ); |
18 | |
19 | # Database sanity check |
20 | is($cd_rs->count, 5, 'CDs with tracks count'); |
21 | for ($cd_rs->all) { |
22 | is ($_->tracks->count, 3, '3 tracks for CD' . $_->id ); |
23 | } |
24 | |
25 | # Test a belongs_to prefetch of a has_many |
26 | { |
27 | my $track_rs = $schema->resultset ('Track')->search ( |
28 | { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } }, |
29 | { |
0c5ea449 |
30 | select => [ |
31 | 'me.cd', |
0c5ea449 |
32 | { count => 'me.trackid' }, |
33 | ], |
34 | as => [qw/ |
35 | cd |
36 | track_count |
0c5ea449 |
37 | /], |
38 | group_by => [qw/me.cd/], |
39 | prefetch => 'cd', |
40 | }, |
41 | ); |
42 | |
22ed9526 |
43 | # this used to fuck up ->all, do not remove! |
44 | ok ($track_rs->first, 'There is stuff in the rs'); |
45 | |
0c5ea449 |
46 | is($track_rs->count, 5, 'Prefetched count with groupby'); |
47 | is($track_rs->all, 5, 'Prefetched objects with groupby'); |
48 | |
49 | { |
50 | my $query_cnt = 0; |
51 | $schema->storage->debugcb ( sub { $query_cnt++ } ); |
a2287768 |
52 | $schema->storage->debug (1); |
0c5ea449 |
53 | |
0c5ea449 |
54 | while (my $collapsed_track = $track_rs->next) { |
0c5ea449 |
55 | my $cdid = $collapsed_track->get_column('cd'); |
56 | is($collapsed_track->get_column('track_count'), 3, "Correct count of tracks for CD $cdid" ); |
57 | ok($collapsed_track->cd->title, "Prefetched title for CD $cdid" ); |
58 | } |
59 | |
22ed9526 |
60 | is ($query_cnt, 1, 'Single query on prefetched titles'); |
0c5ea449 |
61 | $schema->storage->debugcb (undef); |
a2287768 |
62 | $schema->storage->debug ($sdebug); |
0c5ea449 |
63 | } |
64 | |
65 | # Test sql by hand, as the sqlite db will simply paper over |
66 | # improper group/select combinations |
67 | # |
0c5ea449 |
68 | is_same_sql_bind ( |
69 | $track_rs->count_rs->as_query, |
70 | '( |
71 | SELECT COUNT( * ) |
72 | FROM ( |
73 | SELECT me.cd |
74 | FROM track me |
75 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
76 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
77 | GROUP BY me.cd |
78 | ) |
79 | count_subq |
80 | )', |
81 | [ map { [ 'me.cd' => $_] } ($cd_rs->get_column ('cdid')->all) ], |
82 | 'count() query generated expected SQL', |
83 | ); |
84 | |
0c5ea449 |
85 | is_same_sql_bind ( |
86 | $track_rs->as_query, |
87 | '( |
5b45001f |
88 | SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track |
0c5ea449 |
89 | FROM ( |
5b45001f |
90 | SELECT me.cd, COUNT (me.trackid) AS track_count, |
0c5ea449 |
91 | FROM track me |
1c1937b7 |
92 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
93 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
94 | GROUP BY me.cd |
95 | ) as me |
96 | JOIN cd cd ON cd.cdid = me.cd |
d8dbe471 |
97 | WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) ) |
0c5ea449 |
98 | )', |
0bdff769 |
99 | [ map { [ 'me.cd' => $_] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ], |
0c5ea449 |
100 | 'next() query generated expected SQL', |
101 | ); |
102 | |
103 | |
104 | # add an extra track to one of the cds, and then make sure we can get it on top |
105 | # (check if limit works) |
106 | my $top_cd = $cd_rs->slice (1,1)->next; |
107 | $top_cd->create_related ('tracks', { |
108 | title => 'over the top', |
109 | }); |
110 | |
111 | my $top_cd_collapsed_track = $track_rs->search ({}, { |
112 | rows => 2, |
113 | order_by => [ |
114 | { -desc => 'track_count' }, |
115 | ], |
116 | }); |
117 | |
118 | is ($top_cd_collapsed_track->count, 2); |
119 | |
120 | is ( |
121 | $top_cd->title, |
122 | $top_cd_collapsed_track->first->cd->title, |
123 | 'Correct collapsed track with prefetched CD returned on top' |
124 | ); |
125 | } |
126 | |
127 | # test a has_many/might_have prefetch at the same level |
0c5ea449 |
128 | # Note that one of the CDs now has 4 tracks instead of 3 |
129 | { |
dace9819 |
130 | my $most_tracks_rs = $schema->resultset ('CD')->search ( |
131 | { |
132 | 'me.cdid' => { '!=' => undef }, # duh - this is just to test WHERE |
133 | }, |
134 | { |
135 | prefetch => [qw/tracks liner_notes/], |
136 | select => ['me.cdid', { count => 'tracks.trackid' } ], |
137 | as => [qw/cdid track_count/], |
138 | group_by => 'me.cdid', |
139 | order_by => { -desc => 'track_count' }, |
140 | rows => 2, |
141 | } |
142 | ); |
0c5ea449 |
143 | |
144 | is_same_sql_bind ( |
145 | $most_tracks_rs->count_rs->as_query, |
146 | '( |
147 | SELECT COUNT( * ) |
148 | FROM ( |
149 | SELECT me.cdid |
150 | FROM cd me |
151 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
152 | LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid |
dace9819 |
153 | WHERE ( me.cdid IS NOT NULL ) |
0c5ea449 |
154 | GROUP BY me.cdid |
155 | LIMIT 2 |
156 | ) count_subq |
157 | )', |
158 | [], |
159 | 'count() query generated expected SQL', |
160 | ); |
161 | |
162 | is_same_sql_bind ( |
163 | $most_tracks_rs->as_query, |
164 | '( |
1c1937b7 |
165 | SELECT me.cdid, me.track_count, tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, liner_notes.liner_id, liner_notes.notes |
0c5ea449 |
166 | FROM ( |
a57827b6 |
167 | SELECT me.cdid, COUNT( tracks.trackid ) AS track_count |
0c5ea449 |
168 | FROM cd me |
169 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
dace9819 |
170 | WHERE ( me.cdid IS NOT NULL ) |
0c5ea449 |
171 | GROUP BY me.cdid |
1c1937b7 |
172 | ORDER BY track_count DESC |
0c5ea449 |
173 | LIMIT 2 |
174 | ) me |
175 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
176 | LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid |
dace9819 |
177 | WHERE ( me.cdid IS NOT NULL ) |
1c1937b7 |
178 | ORDER BY track_count DESC, tracks.cd |
0c5ea449 |
179 | )', |
180 | [], |
181 | 'next() query generated expected SQL', |
182 | ); |
183 | |
184 | is ($most_tracks_rs->count, 2, 'Limit works'); |
185 | my $top_cd = $most_tracks_rs->first; |
22ed9526 |
186 | is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier |
0c5ea449 |
187 | |
188 | my $query_cnt = 0; |
189 | $schema->storage->debugcb ( sub { $query_cnt++ } ); |
a2287768 |
190 | $schema->storage->debug (1); |
0c5ea449 |
191 | |
192 | is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly'); |
193 | is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct'); |
194 | is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct'); |
195 | is ( |
196 | $top_cd->liner_notes->notes, |
197 | 'Buy Whiskey!', |
198 | 'Correct liner pre-fetched with top cd', |
199 | ); |
200 | |
201 | is ($query_cnt, 0, 'No queries executed during prefetched data access'); |
202 | $schema->storage->debugcb (undef); |
a2287768 |
203 | $schema->storage->debug ($sdebug); |
0c5ea449 |
204 | } |
f785d72e |
205 | |
206 | # make sure that distinct still works |
207 | { |
208 | my $rs = $schema->resultset("CD")->search({}, { |
209 | prefetch => 'tags', |
210 | order_by => 'cdid', |
211 | distinct => 1, |
212 | }); |
213 | |
214 | is_same_sql_bind ( |
215 | $rs->as_query, |
216 | '( |
217 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
218 | tags.tagid, tags.cd, tags.tag |
219 | FROM ( |
220 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
221 | FROM cd me |
222 | GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
223 | ORDER BY cdid |
224 | ) me |
225 | LEFT JOIN tags tags ON tags.cd = me.cdid |
226 | ORDER BY cdid, tags.cd, tags.tag |
227 | )', |
228 | [], |
229 | 'Prefetch + distinct resulted in correct group_by', |
230 | ); |
231 | |
232 | is ($rs->all, 5, 'Correct number of CD objects'); |
233 | is ($rs->count, 5, 'Correct count of CDs'); |
234 | } |
6841b059 |
235 | |
236 | lives_ok (sub { |
237 | my $rs = $schema->resultset("Artwork")->search(undef, {distinct => 1}) |
238 | ->search_related('artwork_to_artist')->search_related('artist', |
239 | undef, |
240 | { prefetch => q(cds) }, |
241 | ); |
242 | is($rs->all, 0, 'prefetch without WHERE'); |
243 | |
244 | $rs = $schema->resultset("Artwork")->search(undef, {distinct => 1}) |
245 | ->search_related('artwork_to_artist')->search_related('artist', |
246 | { 'cds.title' => 'foo' }, |
247 | { prefetch => q(cds) }, |
248 | ); |
249 | is($rs->all, 0, 'prefetch with WHERE'); |
250 | |
251 | |
252 | # different case |
253 | $rs = $schema->resultset("Artist")->search(undef) |
254 | ->search_related('cds')->search_related('genre', |
255 | { 'genre.name' => 'foo' }, |
256 | { prefetch => q(cds) }, |
257 | ); |
258 | is($rs->all, 0, 'prefetch without distinct'); |
259 | |
260 | $rs = $schema->resultset("Artist")->search(undef, {distinct => 1}) |
261 | ->search_related('cds')->search_related('genre', |
262 | { 'genre.name' => 'foo' }, |
263 | ); |
264 | is($rs->all, 0, 'distinct without prefetch'); |
265 | |
266 | $rs = $schema->resultset("Artist")->search(undef, {distinct => 1}) |
267 | ->search_related('cds')->search_related('genre', |
268 | { 'genre.name' => 'foo' }, |
269 | { prefetch => q(cds) }, |
270 | ); |
271 | is($rs->all, 0, 'prefetch with distinct'); |
272 | }, 'distinct generally works with prefetch'); |
273 | |
274 | done_testing; |