1 # Test to ensure we get a consistent result set wether or not we use the
2 # prefetch option in combination rows (LIMIT).
10 use DBIC::SqlMakerTest;
12 my $schema = DBICTest->init_schema();
15 my $no_prefetch = $schema->resultset('Artist')->search(
16 [ # search deliberately contrived
17 { 'artwork.cd_id' => undef },
18 { 'tracks.title' => { '!=' => 'blah-blah-1234568' }}
20 { rows => 3, join => { cds => [qw/artwork tracks/] },
24 my $use_prefetch = $no_prefetch->search(
27 select => ['me.artistid', 'me.name'],
28 as => ['artistid', 'name'],
30 order_by => { -desc => 'name' },
34 # add a floating +select to make sure it does nto throw things off
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)
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
42 my $bind_one = [ __add => 1 ];
43 $use_prefetch = $use_prefetch->search({}, {
44 '+select' => \[ 'me.artistid + ?', $bind_one ],
48 $use_prefetch->as_query,
50 SELECT me.artistid, me.name,
51 cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track,
54 SELECT me.artistid, me.name,
58 ON cds.artist = me.artistid
59 LEFT JOIN cd_artwork artwork
60 ON artwork.cd_id = cds.cdid
61 LEFT JOIN track tracks
62 ON tracks.cd = cds.cdid
63 WHERE artwork.cd_id IS NULL
65 GROUP BY me.artistid, me.name, me.artistid + ?
66 ORDER BY name DESC LIMIT 3
69 ON cds.artist = me.artistid
70 LEFT JOIN cd_artwork artwork
71 ON artwork.cd_id = cds.cdid
72 LEFT JOIN track tracks
73 ON tracks.cd = cds.cdid
74 WHERE artwork.cd_id IS NULL
76 GROUP BY me.artistid, me.name, cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track, me.artistid + ?
77 ORDER BY name DESC, cds.artist, cds.year ASC
80 $bind_one, # outer select
81 $bind_one, # inner select
82 [ 'tracks.title' => 'blah-blah-1234568' ], # inner where
83 $bind_one, # inner group_by
84 [ 'tracks.title' => 'blah-blah-1234568' ], # outer where
85 $bind_one, # outer group_by
87 'Expected SQL on complex limited prefetch'
90 is($no_prefetch->count, $use_prefetch->count, '$no_prefetch->count == $use_prefetch->count');
92 scalar ($no_prefetch->all),
93 scalar ($use_prefetch->all),
94 "Amount of returned rows is right"
97 my $artist_many_cds = $schema->resultset('Artist')->search ( {}, {
99 group_by => 'me.artistid',
100 having => \ 'count(cds.cdid) > 1',
104 $no_prefetch = $schema->resultset('Artist')->search(
105 { artistid => $artist_many_cds->id },
109 $use_prefetch = $no_prefetch->search ({}, { prefetch => 'cds' });
111 my $normal_artist = $no_prefetch->single;
112 my $prefetch_artist = $use_prefetch->find({ name => $artist_many_cds->name });
113 my $prefetch2_artist = $use_prefetch->first;
116 $prefetch_artist->cds->count,
117 $normal_artist->cds->count,
118 "Count of child rel with prefetch + rows => 1 is right (find)"
121 $prefetch2_artist->cds->count,
122 $normal_artist->cds->count,
123 "Count of child rel with prefetch + rows => 1 is right (first)"
127 scalar ($prefetch_artist->cds->all),
128 scalar ($normal_artist->cds->all),
129 "Amount of child rel rows with prefetch + rows => 1 is right (find)"
132 scalar ($prefetch2_artist->cds->all),
133 scalar ($normal_artist->cds->all),
134 "Amount of child rel rows with prefetch + rows => 1 is right (first)"
138 sub { $use_prefetch->single },
139 qr/resultsets prefetching has_many/,
140 'single() with multiprefetch is illegal',
145 $use_prefetch->search(
146 {'tracks.title' => { '!=' => 'foo' }},
147 { order_by => \ 'some oddball literal sql', join => { cds => 'tracks' } }
149 }, qr/A required group_by clause could not be constructed automatically/,
152 my $artist = $use_prefetch->search({'cds.title' => $artist_many_cds->cds->first->title })->next;
153 is($artist->cds->count, 1, "count on search limiting prefetched has_many");
155 # try with double limit
156 my $artist2 = $use_prefetch->search({'cds.title' => { '!=' => $artist_many_cds->cds->first->title } })->slice (0,0)->next;
157 is($artist2->cds->count, 2, "count on search limiting prefetched has_many");
159 # make sure 1:1 joins do not force a subquery (no point to exercise the optimizer, if at all available)
160 # get cd's that have any tracks and their artists
161 my $single_prefetch_rs = $schema->resultset ('CD')->search (
162 { 'me.year' => 2010, 'artist.name' => 'foo' },
163 { prefetch => ['tracks', 'artist'], rows => 15 },
166 $single_prefetch_rs->as_query,
169 me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
170 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
171 artist.artistid, artist.name, artist.rank, artist.charfield
174 me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
176 JOIN artist artist ON artist.artistid = me.artist
177 WHERE ( ( artist.name = ? AND me.year = ? ) )
180 LEFT JOIN track tracks
181 ON tracks.cd = me.cdid
183 ON artist.artistid = me.artist
184 WHERE ( ( artist.name = ? AND me.year = ? ) )
188 [ 'artist.name' => 'foo' ],
189 [ 'me.year' => 2010 ],
190 [ 'artist.name' => 'foo' ],
191 [ 'me.year' => 2010 ],
193 'No grouping of non-multiplying resultsets',