Last bit
[dbsrgits/DBIx-Class-Historic.git] / t / prefetch / grouped.t
CommitLineData
0c5ea449 1use strict;
2use warnings;
6841b059 3
0c5ea449 4use Test::More;
6841b059 5use Test::Exception;
0c5ea449 6
7use lib qw(t/lib);
8use DBICTest;
9use DBIC::SqlMakerTest;
10
0c5ea449 11my $schema = DBICTest->init_schema();
a2287768 12my $sdebug = $schema->storage->debug;
0c5ea449 13
14my $cd_rs = $schema->resultset('CD')->search (
15 { 'tracks.cd' => { '!=', undef } },
16 { prefetch => 'tracks' },
17);
18
19# Database sanity check
20is($cd_rs->count, 5, 'CDs with tracks count');
21for ($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 '(
8bc3fbf5 165 SELECT me.cdid, me.track_count,
e6d62860 166 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, tracks.small_dt,
8bc3fbf5 167 liner_notes.liner_id, liner_notes.notes
0c5ea449 168 FROM (
a57827b6 169 SELECT me.cdid, COUNT( tracks.trackid ) AS track_count
0c5ea449 170 FROM cd me
171 LEFT JOIN track tracks ON tracks.cd = me.cdid
dace9819 172 WHERE ( me.cdid IS NOT NULL )
0c5ea449 173 GROUP BY me.cdid
1c1937b7 174 ORDER BY track_count DESC
0c5ea449 175 LIMIT 2
176 ) me
177 LEFT JOIN track tracks ON tracks.cd = me.cdid
178 LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
dace9819 179 WHERE ( me.cdid IS NOT NULL )
1c1937b7 180 ORDER BY track_count DESC, tracks.cd
0c5ea449 181 )',
182 [],
183 'next() query generated expected SQL',
184 );
185
186 is ($most_tracks_rs->count, 2, 'Limit works');
187 my $top_cd = $most_tracks_rs->first;
22ed9526 188 is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) earlier
0c5ea449 189
190 my $query_cnt = 0;
191 $schema->storage->debugcb ( sub { $query_cnt++ } );
a2287768 192 $schema->storage->debug (1);
0c5ea449 193
194 is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
195 is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct');
196 is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct');
197 is (
198 $top_cd->liner_notes->notes,
199 'Buy Whiskey!',
200 'Correct liner pre-fetched with top cd',
201 );
202
203 is ($query_cnt, 0, 'No queries executed during prefetched data access');
204 $schema->storage->debugcb (undef);
a2287768 205 $schema->storage->debug ($sdebug);
0c5ea449 206}
f785d72e 207
208# make sure that distinct still works
209{
210 my $rs = $schema->resultset("CD")->search({}, {
211 prefetch => 'tags',
212 order_by => 'cdid',
213 distinct => 1,
214 });
215
216 is_same_sql_bind (
217 $rs->as_query,
218 '(
219 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
220 tags.tagid, tags.cd, tags.tag
221 FROM (
222 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
223 FROM cd me
224 GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
225 ORDER BY cdid
226 ) me
227 LEFT JOIN tags tags ON tags.cd = me.cdid
228 ORDER BY cdid, tags.cd, tags.tag
229 )',
230 [],
231 'Prefetch + distinct resulted in correct group_by',
232 );
233
234 is ($rs->all, 5, 'Correct number of CD objects');
235 is ($rs->count, 5, 'Correct count of CDs');
236}
6841b059 237
ffad45f5 238# RT 47779, test group_by as a scalar ref
239{
240 my $track_rs = $schema->resultset ('Track')->search (
241 { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
242 {
243 select => [
244 'me.cd',
245 { count => 'me.trackid' },
246 ],
247 as => [qw/
248 cd
249 track_count
250 /],
251 group_by => \'SUBSTR(me.cd, 1, 1)',
252 prefetch => 'cd',
253 },
254 );
255
256 is_same_sql_bind (
257 $track_rs->count_rs->as_query,
258 '(
259 SELECT COUNT( * )
260 FROM (
efd7c5e5 261 SELECT SUBSTR(me.cd, 1, 1)
ffad45f5 262 FROM track me
263 JOIN cd cd ON cd.cdid = me.cd
264 WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
265 GROUP BY SUBSTR(me.cd, 1, 1)
266 )
267 count_subq
268 )',
269 [ map { [ 'me.cd' => $_] } ($cd_rs->get_column ('cdid')->all) ],
270 'count() query generated expected SQL',
271 );
272}
273
6841b059 274done_testing;