Some cleanup
[dbsrgits/DBIx-Class.git] / t / from_subquery.t
CommitLineData
2a2ca43f 1use strict;
2use warnings FATAL => 'all';
3
4use Test::More;
5
2a2ca43f 6use lib qw(t/lib);
7use DBICTest;
8use DBIC::SqlMakerTest;
9
ac45262b 10plan tests => 8;
11
2a2ca43f 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
2a2ca43f 21 is_same_sql_bind(
af6aac2d 22 $cdrs2->as_query,
866557f9 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 ))",
2a2ca43f 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
2a2ca43f 38 is_same_sql_bind(
af6aac2d 39 $rs->as_query,
866557f9 40 "(SELECT (SELECT id FROM cd me LIMIT 1) FROM artist me)",
2a2ca43f 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
2a2ca43f 55 is_same_sql_bind(
af6aac2d 56 $rs->as_query,
866557f9 57 "(SELECT me.artistid, me.name, me.rank, me.charfield, (SELECT id FROM cd me LIMIT 1) FROM artist me)",
2a2ca43f 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
2a2ca43f 74 is_same_sql_bind(
af6aac2d 75 $rs->as_query,
866557f9 76 "(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 > ? ) ) cd2)",
77 [
78 [ 'id', 20 ]
79 ],
2a2ca43f 80 );
81}
82
83# nested from
84{
85 my $art_rs2 = $schema->resultset('Artist')->search({},
86 {
87 from => [ { 'me' => 'artist' },
88 [ { 'cds' => $cdrs->search({},{ 'select' => [\'me.artist as cds_artist' ]})->as_query },
89 { 'me.artistid' => 'cds_artist' } ] ]
90 });
91
2a2ca43f 92 is_same_sql_bind(
af6aac2d 93 $art_rs2->as_query,
866557f9 94 "(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)",
95 []
2a2ca43f 96 );
97
98
99}
100
101# nested subquery in from
102{
103 my $rs = $cdrs->search(
104 {},
105 {
106 alias => 'cd2',
107 from => [
108 { cd2 => $cdrs->search(
109 { id => { '>' => 20 } },
110 {
111 alias => 'cd3',
112 from => [
113 { cd3 => $cdrs->search( { id => { '<' => 40 } } )->as_query }
114 ],
115 }, )->as_query },
116 ],
117 },
118 );
119
2a2ca43f 120 is_same_sql_bind(
af6aac2d 121 $rs->as_query,
866557f9 122 "(SELECT cd2.cdid, cd2.artist, cd2.title, cd2.year, cd2.genreid, cd2.single_track
2a2ca43f 123 FROM
124 (SELECT cd3.cdid,cd3.artist,cd3.title,cd3.year,cd3.genreid,cd3.single_track
125 FROM
126 (SELECT me.cdid,me.artist,me.title,me.year,me.genreid,me.single_track
127 FROM cd me WHERE ( id < ? ) ) cd3
866557f9 128 WHERE ( id > ? ) ) cd2)",
129 [
130 [ 'id', 40 ],
131 [ 'id', 20 ]
132 ],
2a2ca43f 133 );
134
135}
136
137{
138 my $rs = $cdrs->search({
139 year => {
140 '=' => $cdrs->search(
141 { artistid => { '=' => \'me.artistid' } },
142 { alias => 'inner' }
143 )->get_column('year')->max_rs->as_query,
144 },
145 });
2a2ca43f 146 is_same_sql_bind(
af6aac2d 147 $rs->as_query,
866557f9 148 "(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))",
2a2ca43f 149 [],
150 );
151}
152
153{
154 my $rs = $cdrs->search(
155 {},
156 {
157 alias => 'cd2',
158 from => [
159 { cd2 => $cdrs->search({ title => 'Thriller' })->as_query },
160 ],
161 },
162 );
163
2a2ca43f 164 is_same_sql_bind(
af6aac2d 165 $rs->as_query,
866557f9 166 "(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 ( title = ? ) ) cd2)",
2a2ca43f 167 [ [ 'title', 'Thriller' ] ],
168 );
169}