4 use warnings FATAL => 'all';
14 use DBIC::SqlMakerTest;
16 my $schema = DBICTest->init_schema();
17 my $art_rs = $schema->resultset('Artist');
18 my $cdrs = $schema->resultset('CD');
21 my $cdrs2 = $cdrs->search({
22 artist_id => { 'in' => $art_rs->search({}, { rows => 1 })->get_column( 'id' )->as_query },
25 my $arr = $cdrs2->as_query;
26 my ($query, @bind) = @{$$arr};
29 "( SELECT me.cdid,me.artist,me.title,me.year,me.genreid,me.single_track FROM cd me WHERE artist_id IN ( SELECT id FROM artist me LIMIT 1 ) )",
35 my $rs = $art_rs->search(
39 $cdrs->search({}, { rows => 1 })->get_column('id')->as_query,
44 my $arr = $rs->as_query;
45 my ($query, @bind) = @{$$arr};
48 "( SELECT (SELECT id FROM cd me LIMIT 1) FROM artist me )",
54 my $rs = $art_rs->search(
58 $cdrs->search({}, { rows => 1 })->get_column('id')->as_query,
63 my $arr = $rs->as_query;
64 my ($query, @bind) = @{$$arr};
67 "( SELECT me.artistid, me.name, me.rank, me.charfield, (SELECT id FROM cd me LIMIT 1) FROM artist me )",
74 my $rs = $cdrs->search(
79 { cd2 => $cdrs->search({ id => { '>' => 20 } })->as_query },
84 my $arr = $rs->as_query;
85 my ($query, @bind) = @{$$arr};
88 "( SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track FROM (SELECT me.cdid,me.artist,me.title,me.year,me.genreid,me.single_track FROM cd me WHERE id > 20) cd2 )",
95 my $art_rs2 = $schema->resultset('Artist')->search({},
97 from => [ { 'me' => 'artist' },
98 [ { 'cds' => $cdrs->search({},{ 'select' => [\'me.artist as cds_artist' ]})->as_query },
99 { 'me.artistid' => 'cds_artist' } ] ]
102 my $arr = $art_rs2->as_query;
103 my ($query, @bind) = @{$$arr};
106 "( SELECT me.artistid, me.name, me.rank, me.charfield FROM artist me JOIN (SELECT me.artist as cds_artist FROM cd me) cds ON me.artistid = cds_artist )", []
112 # nested subquery in from
114 my $rs = $cdrs->search(
119 { cd2 => $cdrs->search(
120 { id => { '>' => 20 } },
124 { cd3 => $cdrs->search( { id => { '<' => 40 } } )->as_query }
131 my $arr = $rs->as_query;
132 my ($query, @bind) = @{$$arr};
135 "( SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track
137 (SELECT cd3.cdid,cd3.artist,cd3.title,cd3.year,cd3.genreid,cd3.single_track
139 (SELECT me.cdid,me.artist,me.title,me.year,me.genreid,me.single_track
140 FROM cd me WHERE id < 40) cd3
149 my $rs = $cdrs->search({
151 '=' => $cdrs->search(
152 { artistid => { '=' => \'me.artistid' } },
154 )->get_column('year')->max_rs->as_query,
157 my $arr = $rs->as_query;
158 my ($query, @bind) = @{$$arr};
161 "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE year = (SELECT MAX(inner.year) FROM cd inner WHERE artistid = me.artistid) )",