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