Commit | Line | Data |
a04af85f |
1 | # Test to ensure we get a consistent result set wether or not we use the |
2 | # prefetch option in combination rows (LIMIT). |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
61fd5dfc |
7 | use Test::Exception; |
a04af85f |
8 | use lib qw(t/lib); |
9 | use DBICTest; |
53c29913 |
10 | use DBIC::SqlMakerTest; |
a04af85f |
11 | |
a04af85f |
12 | my $schema = DBICTest->init_schema(); |
51a296b4 |
13 | |
14 | |
a04af85f |
15 | my $no_prefetch = $schema->resultset('Artist')->search( |
51a296b4 |
16 | [ # search deliberately contrived |
17 | { 'artwork.cd_id' => undef }, |
18 | { 'tracks.title' => { '!=' => 'blah-blah-1234568' }} |
19 | ], |
96faafb4 |
20 | { rows => 3, join => { cds => [qw/artwork tracks/] }, |
21 | } |
22 | ); |
23 | |
24 | my $use_prefetch = $no_prefetch->search( |
25 | {}, |
25cac750 |
26 | { |
c9d29bb2 |
27 | select => ['me.artistid', 'me.name'], |
28 | as => ['artistid', 'name'], |
25cac750 |
29 | prefetch => 'cds', |
51a296b4 |
30 | order_by => { -desc => 'name' }, |
25cac750 |
31 | } |
a04af85f |
32 | ); |
33 | |
0e773352 |
34 | # add a floating +select to make sure it does not throw things off |
36fd7f07 |
35 | # we also expect it to appear in both selectors, as we can not know |
36 | # for sure which part of the query it applies to (may be order_by, |
37 | # maybe something else) |
38 | # |
39 | # we use a reference to the same array in bind vals, because |
40 | # is_deeply picks up this difference too (not sure if bug or |
41 | # feature) |
36fd7f07 |
42 | $use_prefetch = $use_prefetch->search({}, { |
0e773352 |
43 | '+select' => \[ 'me.artistid + ?', [ \ 'inTEger' => 1 ] ], |
36fd7f07 |
44 | }); |
45 | |
0e773352 |
46 | my $bind_int_resolved = sub { [ { sqlt_datatype => 'inTEger' } => 1 ] }; |
47 | my $bind_vc_resolved = sub { [ |
48 | { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' } |
49 | => 'blah-blah-1234568' |
50 | ] }; |
36fd7f07 |
51 | is_same_sql_bind ( |
52 | $use_prefetch->as_query, |
53 | '( |
54 | SELECT me.artistid, me.name, |
55 | cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track, |
56 | me.artistid + ? |
57 | FROM ( |
58 | SELECT me.artistid, me.name, |
59 | me.artistid + ? |
60 | FROM artist me |
61 | LEFT JOIN cd cds |
62 | ON cds.artist = me.artistid |
63 | LEFT JOIN cd_artwork artwork |
64 | ON artwork.cd_id = cds.cdid |
65 | LEFT JOIN track tracks |
66 | ON tracks.cd = cds.cdid |
67 | WHERE artwork.cd_id IS NULL |
68 | OR tracks.title != ? |
69 | GROUP BY me.artistid, me.name, me.artistid + ? |
70 | ORDER BY name DESC LIMIT 3 |
71 | ) me |
72 | LEFT JOIN cd cds |
73 | ON cds.artist = me.artistid |
74 | LEFT JOIN cd_artwork artwork |
75 | ON artwork.cd_id = cds.cdid |
76 | LEFT JOIN track tracks |
77 | ON tracks.cd = cds.cdid |
78 | WHERE artwork.cd_id IS NULL |
79 | OR tracks.title != ? |
80 | GROUP BY me.artistid, me.name, cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track, me.artistid + ? |
81 | ORDER BY name DESC, cds.artist, cds.year ASC |
82 | )', |
83 | [ |
0e773352 |
84 | $bind_int_resolved->(), # outer select |
85 | $bind_int_resolved->(), # inner select |
86 | $bind_vc_resolved->(), # inner where |
87 | $bind_int_resolved->(), # inner group_by |
88 | $bind_vc_resolved->(), # outer where |
89 | $bind_int_resolved->(), # outer group_by |
36fd7f07 |
90 | ], |
91 | 'Expected SQL on complex limited prefetch' |
92 | ); |
93 | |
a04af85f |
94 | is($no_prefetch->count, $use_prefetch->count, '$no_prefetch->count == $use_prefetch->count'); |
5624ba1f |
95 | is( |
9117ccfb |
96 | scalar ($no_prefetch->all), |
97 | scalar ($use_prefetch->all), |
98 | "Amount of returned rows is right" |
5624ba1f |
99 | ); |
100 | |
9117ccfb |
101 | my $artist_many_cds = $schema->resultset('Artist')->search ( {}, { |
102 | join => 'cds', |
103 | group_by => 'me.artistid', |
104 | having => \ 'count(cds.cdid) > 1', |
105 | })->first; |
106 | |
107 | |
5624ba1f |
108 | $no_prefetch = $schema->resultset('Artist')->search( |
9117ccfb |
109 | { artistid => $artist_many_cds->id }, |
5624ba1f |
110 | { rows => 1 } |
111 | ); |
112 | |
61fd5dfc |
113 | $use_prefetch = $no_prefetch->search ({}, { prefetch => 'cds' }); |
5624ba1f |
114 | |
61fd5dfc |
115 | my $normal_artist = $no_prefetch->single; |
116 | my $prefetch_artist = $use_prefetch->find({ name => $artist_many_cds->name }); |
117 | my $prefetch2_artist = $use_prefetch->first; |
5624ba1f |
118 | |
119 | is( |
120 | $prefetch_artist->cds->count, |
121 | $normal_artist->cds->count, |
61fd5dfc |
122 | "Count of child rel with prefetch + rows => 1 is right (find)" |
123 | ); |
124 | is( |
125 | $prefetch2_artist->cds->count, |
126 | $normal_artist->cds->count, |
127 | "Count of child rel with prefetch + rows => 1 is right (first)" |
5624ba1f |
128 | ); |
61fd5dfc |
129 | |
9117ccfb |
130 | is ( |
131 | scalar ($prefetch_artist->cds->all), |
132 | scalar ($normal_artist->cds->all), |
61fd5dfc |
133 | "Amount of child rel rows with prefetch + rows => 1 is right (find)" |
134 | ); |
135 | is ( |
136 | scalar ($prefetch2_artist->cds->all), |
137 | scalar ($normal_artist->cds->all), |
138 | "Amount of child rel rows with prefetch + rows => 1 is right (first)" |
139 | ); |
140 | |
141 | throws_ok ( |
142 | sub { $use_prefetch->single }, |
143 | qr/resultsets prefetching has_many/, |
144 | 'single() with multiprefetch is illegal', |
9117ccfb |
145 | ); |
01c781fe |
146 | |
14e26c5f |
147 | throws_ok ( |
148 | sub { |
149 | $use_prefetch->search( |
150 | {'tracks.title' => { '!=' => 'foo' }}, |
151 | { order_by => \ 'some oddball literal sql', join => { cds => 'tracks' } } |
152 | )->next |
153 | }, qr/A required group_by clause could not be constructed automatically/, |
154 | ); |
155 | |
09707a31 |
156 | my $artist = $use_prefetch->search({'cds.title' => $artist_many_cds->cds->first->title })->next; |
01c781fe |
157 | is($artist->cds->count, 1, "count on search limiting prefetched has_many"); |
158 | |
a3683eae |
159 | # try with double limit |
160 | my $artist2 = $use_prefetch->search({'cds.title' => { '!=' => $artist_many_cds->cds->first->title } })->slice (0,0)->next; |
161 | is($artist2->cds->count, 2, "count on search limiting prefetched has_many"); |
162 | |
53c29913 |
163 | # make sure 1:1 joins do not force a subquery (no point to exercise the optimizer, if at all available) |
164 | # get cd's that have any tracks and their artists |
165 | my $single_prefetch_rs = $schema->resultset ('CD')->search ( |
166 | { 'me.year' => 2010, 'artist.name' => 'foo' }, |
167 | { prefetch => ['tracks', 'artist'], rows => 15 }, |
168 | ); |
169 | is_same_sql_bind ( |
170 | $single_prefetch_rs->as_query, |
171 | '( |
172 | SELECT |
173 | me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
3d98c75e |
174 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, |
53c29913 |
175 | artist.artistid, artist.name, artist.rank, artist.charfield |
176 | FROM ( |
177 | SELECT |
178 | me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track |
179 | FROM cd me |
180 | JOIN artist artist ON artist.artistid = me.artist |
181 | WHERE ( ( artist.name = ? AND me.year = ? ) ) |
182 | LIMIT 15 |
183 | ) me |
184 | LEFT JOIN track tracks |
185 | ON tracks.cd = me.cdid |
186 | JOIN artist artist |
187 | ON artist.artistid = me.artist |
188 | WHERE ( ( artist.name = ? AND me.year = ? ) ) |
189 | ORDER BY tracks.cd |
190 | )', |
0e773352 |
191 | [ map { |
192 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } |
193 | => 'foo' ], |
194 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } |
195 | => 2010 ], |
196 | } (1,2)], |
53c29913 |
197 | 'No grouping of non-multiplying resultsets', |
198 | ); |
199 | |
c9d29bb2 |
200 | done_testing; |