1use strict; 2use warnings; 3 4use Test::More; 5 6use lib qw(t/lib); 7use DBICTest; 8use DBIC::SqlMakerTest; 9 10plan tests => 8; 11 12my $schema = DBICTest->init_schema(); 13my $art_rs = $schema->resultset('Artist'); 14my $cdrs = $schema->resultset('CD'); 15 16{ 17 my $cdrs2 = $cdrs->search({ 18 artist_id => { 'in' => $art_rs->search({}, { rows => 1 })->get_column( 'id' )->as_query }, 19 }); 20 21 is_same_sql_bind( 22 $cdrs2->as_query, 23 "(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 ))", 24 [], 25 ); 26} 27 28{ 29 my $rs = $art_rs->search( 30 {}, 31 { 32 'select' => [ 33 $cdrs->search({}, { rows => 1 })->get_column('id')->as_query, 34 ], 35 }, 36 ); 37 38 is_same_sql_bind( 39 $rs->as_query, 40 "(SELECT (SELECT id FROM cd me LIMIT 1) FROM artist me)", 41 [], 42 ); 43} 44 45{ 46 my $rs = $art_rs->search( 47 {}, 48 { 49 '+select' => [ 50 $cdrs->search({}, { rows => 1 })->get_column('id')->as_query, 51 ], 52 }, 53 ); 54 55 is_same_sql_bind( 56 $rs->as_query, 57 "(SELECT me.artistid, me.name, me.rank, me.charfield, (SELECT id FROM cd me LIMIT 1) FROM artist me)", 58 [], 59 ); 60} 61 62# simple from 63{ 64 my $rs = $cdrs->search( 65 {}, 66 { 67 alias => 'cd2', 68 from => [ 69 { cd2 => $cdrs->search({ id => { '>' => 20 } })->as_query }, 70 ], 71 }, 72 ); 73 74 is_same_sql_bind( 75 $rs->as_query, 76 "(SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track FROM ( 77 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE ( id > ? ) 78 ) cd2)", 79 [ 80 [ 'id', 20 ] 81 ], 82 ); 83} 84 85# nested from 86{ 87 my $art_rs2 = $schema->resultset('Artist')->search({}, 88 { 89 from => [ { 'me' => 'artist' }, 90 [ { 'cds' => $cdrs->search({},{ 'select' => [\'me.artist as cds_artist' ]})->as_query }, 91 { 'me.artistid' => 'cds_artist' } ] ] 92 }); 93 94 is_same_sql_bind( 95 $art_rs2->as_query, 96 "(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)", 97 [] 98 ); 99 100 101} 102 103# nested subquery in from 104{ 105 my $rs = $cdrs->search( 106 {}, 107 { 108 alias => 'cd2', 109 from => [ 110 { cd2 => $cdrs->search( 111 { id => { '>' => 20 } }, 112 { 113 alias => 'cd3', 114 from => [ 115 { cd3 => $cdrs->search( { id => { '<' => 40 } } )->as_query } 116 ], 117 }, )->as_query }, 118 ], 119 }, 120 ); 121 122 is_same_sql_bind( 123 $rs->as_query, 124 "(SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track 125 FROM 126 (SELECT cd3.cdid, cd3.artist, cd3.title, cd3.year, cd3.genreid, cd3.single_track 127 FROM 128 (SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track 129 FROM cd me WHERE ( id < ? ) ) cd3 130 WHERE ( id > ? ) ) cd2)", 131 [ 132 [ 'id', 40 ], 133 [ 'id', 20 ] 134 ], 135 ); 136 137} 138 139{ 140 my $rs = $cdrs->search({ 141 year => { 142 '=' => $cdrs->search( 143 { artistid => { '=' => \'me.artistid' } }, 144 { alias => 'inner' } 145 )->get_column('year')->max_rs->as_query, 146 }, 147 }); 148 is_same_sql_bind( 149 $rs->as_query, 150 "(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))", 151 [], 152 ); 153} 154 155{ 156 my $rs = $cdrs->search( 157 {}, 158 { 159 alias => 'cd2', 160 from => [ 161 { cd2 => $cdrs->search({ title => 'Thriller' })->as_query }, 162 ], 163 }, 164 ); 165 166 is_same_sql_bind( 167 $rs->as_query, 168 "(SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track FROM ( 169 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE ( title = ? ) 170 ) cd2)", 171 [ [ 'title', 'Thriller' ] ], 172 ); 173} 174