Commit | Line | Data |
0c5ea449 |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
4 | |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
8 | |
9 | #plan tests => 6; |
10 | plan 'no_plan'; |
11 | |
12 | my $schema = DBICTest->init_schema(); |
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 | { |
30 | # the select/as is deliberately silly to test both funcs and refs below |
31 | select => [ |
32 | 'me.cd', |
0c5ea449 |
33 | { count => 'me.trackid' }, |
34 | ], |
35 | as => [qw/ |
36 | cd |
37 | track_count |
0c5ea449 |
38 | /], |
39 | group_by => [qw/me.cd/], |
40 | prefetch => 'cd', |
41 | }, |
42 | ); |
43 | |
44 | is($track_rs->count, 5, 'Prefetched count with groupby'); |
45 | is($track_rs->all, 5, 'Prefetched objects with groupby'); |
46 | |
47 | { |
48 | my $query_cnt = 0; |
49 | $schema->storage->debugcb ( sub { $query_cnt++ } ); |
50 | |
51 | $track_rs->reset; |
52 | while (my $collapsed_track = $track_rs->next) { |
0c5ea449 |
53 | my $cdid = $collapsed_track->get_column('cd'); |
54 | is($collapsed_track->get_column('track_count'), 3, "Correct count of tracks for CD $cdid" ); |
55 | ok($collapsed_track->cd->title, "Prefetched title for CD $cdid" ); |
56 | } |
57 | |
58 | is ($query_cnt, 0, 'No queries on prefetched titles'); |
59 | $schema->storage->debugcb (undef); |
60 | } |
61 | |
62 | # Test sql by hand, as the sqlite db will simply paper over |
63 | # improper group/select combinations |
64 | # |
65 | # the exploded IN needs fixing below, coming in another branch |
66 | # |
67 | is_same_sql_bind ( |
68 | $track_rs->count_rs->as_query, |
69 | '( |
70 | SELECT COUNT( * ) |
71 | FROM ( |
72 | SELECT me.cd |
73 | FROM track me |
74 | JOIN cd cd ON cd.cdid = me.cd |
75 | WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ) |
76 | GROUP BY me.cd |
77 | ) |
78 | count_subq |
79 | )', |
80 | [ map { [ 'me.cd' => $_] } ($cd_rs->get_column ('cdid')->all) ], |
81 | 'count() query generated expected SQL', |
82 | ); |
83 | |
0c5ea449 |
84 | is_same_sql_bind ( |
85 | $track_rs->as_query, |
86 | '( |
5b45001f |
87 | SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track |
0c5ea449 |
88 | FROM ( |
5b45001f |
89 | SELECT me.cd, COUNT (me.trackid) AS track_count, |
0c5ea449 |
90 | FROM track me |
1c1937b7 |
91 | JOIN cd cd ON cd.cdid = me.cd |
92 | WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ) |
0c5ea449 |
93 | GROUP BY me.cd |
94 | ) as me |
95 | JOIN cd cd ON cd.cdid = me.cd |
96 | WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ) |
97 | )', |
1c1937b7 |
98 | [ map |
99 | { [ 'me.cd' => $_] } |
100 | ( $cd_rs->get_column ('cdid')->all, $cd_rs->get_column ('cdid')->all ) |
101 | ], |
0c5ea449 |
102 | 'next() query generated expected SQL', |
103 | ); |
104 | |
105 | |
106 | # add an extra track to one of the cds, and then make sure we can get it on top |
107 | # (check if limit works) |
108 | my $top_cd = $cd_rs->slice (1,1)->next; |
109 | $top_cd->create_related ('tracks', { |
110 | title => 'over the top', |
111 | }); |
112 | |
113 | my $top_cd_collapsed_track = $track_rs->search ({}, { |
114 | rows => 2, |
115 | order_by => [ |
116 | { -desc => 'track_count' }, |
117 | ], |
118 | }); |
119 | |
120 | is ($top_cd_collapsed_track->count, 2); |
121 | |
122 | is ( |
123 | $top_cd->title, |
124 | $top_cd_collapsed_track->first->cd->title, |
125 | 'Correct collapsed track with prefetched CD returned on top' |
126 | ); |
127 | } |
128 | |
129 | # test a has_many/might_have prefetch at the same level |
0c5ea449 |
130 | # Note that one of the CDs now has 4 tracks instead of 3 |
131 | { |
132 | my $most_tracks_rs = $cd_rs->search ({}, { |
133 | prefetch => 'liner_notes', # tracks are alredy prefetched |
134 | select => ['me.cdid', { count => 'tracks.trackid' } ], |
135 | as => [qw/cdid track_count/], |
136 | group_by => 'me.cdid', |
5b45001f |
137 | order_by => { -desc => 'track_count' }, |
0c5ea449 |
138 | rows => 2, |
139 | }); |
140 | |
141 | is_same_sql_bind ( |
142 | $most_tracks_rs->count_rs->as_query, |
143 | '( |
144 | SELECT COUNT( * ) |
145 | FROM ( |
146 | SELECT me.cdid |
147 | FROM cd me |
148 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
149 | LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid |
150 | WHERE ( tracks.cd IS NOT NULL ) |
151 | GROUP BY me.cdid |
152 | LIMIT 2 |
153 | ) count_subq |
154 | )', |
155 | [], |
156 | 'count() query generated expected SQL', |
157 | ); |
158 | |
159 | is_same_sql_bind ( |
160 | $most_tracks_rs->as_query, |
161 | '( |
1c1937b7 |
162 | 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 |
163 | FROM ( |
a57827b6 |
164 | SELECT me.cdid, COUNT( tracks.trackid ) AS track_count |
0c5ea449 |
165 | FROM cd me |
166 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
167 | WHERE ( tracks.cd IS NOT NULL ) |
168 | GROUP BY me.cdid |
1c1937b7 |
169 | ORDER BY track_count DESC |
0c5ea449 |
170 | LIMIT 2 |
171 | ) me |
172 | LEFT JOIN track tracks ON tracks.cd = me.cdid |
173 | LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid |
174 | WHERE ( tracks.cd IS NOT NULL ) |
1c1937b7 |
175 | ORDER BY track_count DESC, tracks.cd |
0c5ea449 |
176 | )', |
177 | [], |
178 | 'next() query generated expected SQL', |
179 | ); |
180 | |
181 | is ($most_tracks_rs->count, 2, 'Limit works'); |
182 | my $top_cd = $most_tracks_rs->first; |
183 | is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) above |
184 | |
185 | my $query_cnt = 0; |
186 | $schema->storage->debugcb ( sub { $query_cnt++ } ); |
187 | |
188 | is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly'); |
189 | is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct'); |
190 | is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct'); |
191 | is ( |
192 | $top_cd->liner_notes->notes, |
193 | 'Buy Whiskey!', |
194 | 'Correct liner pre-fetched with top cd', |
195 | ); |
196 | |
197 | is ($query_cnt, 0, 'No queries executed during prefetched data access'); |
198 | $schema->storage->debugcb (undef); |
199 | } |