fixed sql mistakes from hierarchical query tests
[dbsrgits/DBIx-Class.git] / t / from_subquery.t
CommitLineData
2a2ca43f 1use strict;
be83e9ec 2use warnings;
2a2ca43f 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,
d0e58487 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,
d0e58487 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)",
866557f9 79 [
80 [ 'id', 20 ]
81 ],
2a2ca43f 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
2a2ca43f 94 is_same_sql_bind(
af6aac2d 95 $art_rs2->as_query,
866557f9 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 []
2a2ca43f 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
2a2ca43f 122 is_same_sql_bind(
af6aac2d 123 $rs->as_query,
d0e58487 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
2a2ca43f 129 FROM cd me WHERE ( id < ? ) ) cd3
866557f9 130 WHERE ( id > ? ) ) cd2)",
131 [
132 [ 'id', 40 ],
133 [ 'id', 20 ]
134 ],
2a2ca43f 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 });
2a2ca43f 148 is_same_sql_bind(
af6aac2d 149 $rs->as_query,
866557f9 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))",
2a2ca43f 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
2a2ca43f 166 is_same_sql_bind(
af6aac2d 167 $rs->as_query,
d0e58487 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)",
2a2ca43f 171 [ [ 'title', 'Thriller' ] ],
172 );
173}