7 use DBIC::SqlMakerTest;
9 my $schema = DBICTest->init_schema();
10 my $orig_debug = $schema->storage->debug;
12 my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }});
16 siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1,
17 track_titles => [ map { $_->title } ($_->tracks->all) ],
21 my $c_rs = $cdrs->search ({}, {
23 '+columns' => { sibling_count => $cdrs->search(
25 'siblings.artist' => { -ident => 'me.artist' },
26 'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 23414] },
27 }, { alias => 'siblings' },
28 )->count_rs->as_query,
35 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
38 WHERE siblings.artist = me.artist
39 AND siblings.cdid != me.cdid
40 AND siblings.cdid != ?
43 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
45 LEFT JOIN track tracks
46 ON tracks.cd = me.cdid
52 [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
55 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
59 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
62 'Expected SQL on correlated realiased subquery'
66 $schema->storage->debugcb(sub { $queries++; });
67 $schema->storage->debug(1);
72 track_titles => [ map { $_->title } ($_->tracks->all) ],
73 siblings => $_->get_column ('sibling_count'),
78 'Proper information retrieved from correlated subquery'
81 is ($queries, 1, 'Only 1 query fired to retrieve everything');
83 $schema->storage->debug($orig_debug);
84 $schema->storage->debugcb(undef);
86 # now add an unbalanced select/as pair
87 $c_rs = $c_rs->search ({}, {
88 '+select' => $cdrs->search(
89 { 'siblings.artist' => { -ident => 'me.artist' } },
90 { alias => 'siblings', columns => [
91 { first_year => { min => 'year' }},
92 { last_year => { max => 'year' }},
95 '+as' => [qw/active_from active_to/],
101 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
104 WHERE siblings.artist = me.artist
105 AND siblings.cdid != me.cdid
106 AND siblings.cdid != ?
109 (SELECT MIN( year ), MAX( year )
111 WHERE siblings.artist = me.artist
114 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
116 LEFT JOIN track tracks
117 ON tracks.cd = me.cdid
123 [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
126 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
130 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
134 [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
137 'Expected SQL on correlated realiased subquery'