8 use DBIC::SqlMakerTest;
10 my $schema = DBICTest->init_schema();
11 my $orig_debug = $schema->storage->debug;
13 my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }});
17 siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1,
18 track_titles => [ map { $_->title } ($_->tracks->all) ],
22 my $c_rs = $cdrs->search ({}, {
24 '+columns' => { sibling_count => $cdrs->search(
26 'siblings.artist' => { -ident => 'me.artist' },
27 'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 'bogus condition'] },
28 }, { alias => 'siblings' },
29 )->count_rs->as_query,
36 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
39 WHERE siblings.artist = me.artist
40 AND siblings.cdid != me.cdid
41 AND siblings.cdid != ?
44 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
46 LEFT JOIN track tracks
47 ON tracks.cd = me.cdid
54 [ 'siblings.cdid' => 'bogus condition' ],
60 'Expected SQL on correlated realiased subquery'
64 $schema->storage->debugcb(sub { $queries++; });
65 $schema->storage->debug(1);
70 track_titles => [ map { $_->title } ($_->tracks->all) ],
71 siblings => $_->get_column ('sibling_count'),
76 'Proper information retrieved from correlated subquery'
79 is ($queries, 1, 'Only 1 query fired to retrieve everything');
81 $schema->storage->debug($orig_debug);
82 $schema->storage->debugcb(undef);
84 # try to unbalance the select
86 # first add a lone non-as-ed select
87 # it should be reordered to appear at the end without throwing prefetch/bind off
88 $c_rs = $c_rs->search({}, { '+select' => \[ 'me.cdid + ?', [ __add => 1 ] ] });
90 # now add an unbalanced select/as pair
91 $c_rs = $c_rs->search ({}, {
92 '+select' => $cdrs->search(
93 { 'siblings.artist' => { -ident => 'me.artist' } },
94 { alias => 'siblings', columns => [
95 { first_year => { min => 'year' }},
96 { last_year => { max => 'year' }},
99 '+as' => [qw/active_from active_to/],
106 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
109 WHERE siblings.artist = me.artist
110 AND siblings.cdid != me.cdid
111 AND siblings.cdid != ?
114 (SELECT MIN( year ), MAX( year )
116 WHERE siblings.artist = me.artist
119 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
122 LEFT JOIN track tracks
123 ON tracks.cd = me.cdid
130 [ 'siblings.cdid' => 'bogus condition' ],
131 [ 'me.artist' => 2 ],
134 [ 'me.artist' => 2 ],
140 [ 'me.artist' => 2 ],
142 'Expected SQL on correlated realiased subquery'