Commit | Line | Data |
a5f843e3 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
fcb7fcbb |
8 | use DBIx::Class::SQLMaker::LimitDialects; |
9 | |
10 | my ($TOTAL, $OFFSET) = ( |
11 | DBIx::Class::SQLMaker::LimitDialects->__total_bindtype, |
12 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
13 | ); |
a5f843e3 |
14 | |
15 | my $schema = DBICTest->init_schema; |
16 | |
a5f843e3 |
17 | $schema->storage->_sql_maker->limit_dialect ('RowNumberOver'); |
18 | |
41eac664 |
19 | my $rs_selectas_col = $schema->resultset ('BooksInLibrary')->search ({}, { |
20 | '+select' => ['owner.name'], |
21 | '+as' => ['owner.name'], |
22 | join => 'owner', |
23 | rows => 1, |
24 | }); |
a5f843e3 |
25 | |
41eac664 |
26 | is_same_sql_bind( |
27 | $rs_selectas_col->as_query, |
28 | '( |
760545fa |
29 | SELECT id, source, owner, title, price, |
30 | owner__name |
41eac664 |
31 | FROM ( |
760545fa |
32 | SELECT id, source, owner, title, price, |
33 | owner__name, |
41eac664 |
34 | ROW_NUMBER() OVER( ) AS rno__row__index |
35 | FROM ( |
36 | SELECT me.id, me.source, me.owner, me.title, me.price, |
760545fa |
37 | owner.name AS owner__name |
41eac664 |
38 | FROM books me |
39 | JOIN owners owner ON owner.id = me.owner |
40 | WHERE ( source = ? ) |
41 | ) me |
42 | ) me |
fcb7fcbb |
43 | WHERE rno__row__index >= ? AND rno__row__index <= ? |
41eac664 |
44 | )', |
fcb7fcbb |
45 | [ |
46 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
47 | [ $OFFSET => 1 ], |
48 | [ $TOTAL => 1 ], |
49 | ], |
41eac664 |
50 | ); |
51 | |
760545fa |
52 | $schema->storage->_sql_maker->quote_char ([qw/ [ ] /]); |
53 | $schema->storage->_sql_maker->name_sep ('.'); |
41eac664 |
54 | |
55 | my $rs_selectas_rel = $schema->resultset ('BooksInLibrary')->search ({}, { |
56 | '+select' => ['owner.name'], |
57 | '+as' => ['owner_name'], |
58 | join => 'owner', |
760545fa |
59 | rows => 1, |
41eac664 |
60 | }); |
61 | |
62 | is_same_sql_bind( |
63 | $rs_selectas_rel->as_query, |
64 | '( |
760545fa |
65 | SELECT [id], [source], [owner], [title], [price], |
66 | [owner_name] |
41eac664 |
67 | FROM ( |
760545fa |
68 | SELECT [id], [source], [owner], [title], [price], |
69 | [owner_name], |
70 | ROW_NUMBER() OVER( ) AS [rno__row__index] |
41eac664 |
71 | FROM ( |
760545fa |
72 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
73 | [owner].[name] AS [owner_name] |
74 | FROM [books] [me] |
75 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
76 | WHERE ( [source] = ? ) |
77 | ) [me] |
78 | ) [me] |
fcb7fcbb |
79 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
41eac664 |
80 | )', |
fcb7fcbb |
81 | [ |
82 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
83 | [ $OFFSET => 1 ], |
84 | [ $TOTAL => 1 ], |
85 | ], |
41eac664 |
86 | ); |
a5f843e3 |
87 | |
d7632687 |
88 | { |
69d3c270 |
89 | my $subq = $schema->resultset('Owners')->search({ |
90 | 'count.id' => { -ident => 'owner.id' }, |
91 | 'count.name' => 'fail', # no one would do this in real life |
92 | }, { alias => 'owner' })->count_rs; |
93 | |
94 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { |
95 | columns => [ |
96 | { owner_name => 'owner.name' }, |
97 | { owner_books => $subq->as_query }, |
98 | ], |
99 | join => 'owner', |
100 | rows => 1, |
071dea12 |
101 | order_by => 'me.id', |
69d3c270 |
102 | }); |
103 | |
071dea12 |
104 | # SELECT [owner_name], [owner_books] FROM ( |
105 | # SELECT [owner_name], [owner_books], [ORDER__BY__1], ROW_NUMBER() OVER( ORDER BY [ORDER__BY__1] ) AS [rno__row__index] FROM ( |
106 | # SELECT [owner].[name] AS [owner_name], (SELECT COUNT( * ) FROM [owners] [owner] WHERE ( ( [count].[id] = [owner].[id] AND [count].[name] = ? ) )) AS [owner_books], [me].[id] AS [ORDER__BY__1] FROM [books] [me] JOIN [owners] [owner] ON [owner].[id] = [me].[owner] WHERE ( [source] = ? ) |
107 | # ) [me] |
108 | # ) [me] WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
109 | |
69d3c270 |
110 | is_same_sql_bind( |
111 | $rs_selectas_rel->as_query, |
112 | '( |
113 | SELECT [owner_name], [owner_books] |
114 | FROM ( |
071dea12 |
115 | SELECT [owner_name], [owner_books], ROW_NUMBER() OVER( ORDER BY [ORDER__BY__1] ) AS [rno__row__index] |
69d3c270 |
116 | FROM ( |
117 | SELECT [owner].[name] AS [owner_name], |
118 | ( SELECT COUNT( * ) FROM [owners] [owner] |
071dea12 |
119 | WHERE [count].[id] = [owner].[id] and [count].[name] = ? ) AS [owner_books], |
120 | [me].[id] AS [ORDER__BY__1] |
121 | FROM [books] [me] |
122 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
69d3c270 |
123 | WHERE ( [source] = ? ) |
124 | ) [me] |
125 | ) [me] |
126 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
127 | )', |
128 | [ |
129 | [ { dbic_colname => 'count.name' } => 'fail' ], |
130 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
131 | [ $OFFSET => 1 ], |
132 | [ $TOTAL => 1 ], |
133 | ], |
134 | ); |
135 | |
136 | }{ |
137 | my $subq = $schema->resultset('Owners')->search({ |
138 | 'count.id' => { -ident => 'owner.id' }, |
139 | }, { alias => 'owner' })->count_rs; |
140 | |
141 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { |
142 | columns => [ |
143 | { owner_name => 'owner.name' }, |
144 | { owner_books => $subq->as_query }, |
145 | ], |
146 | join => 'owner', |
147 | rows => 1, |
148 | }); |
149 | |
150 | is_same_sql_bind( |
151 | $rs_selectas_rel->as_query, |
152 | '( |
153 | SELECT [owner_name], [owner_books] |
154 | FROM ( |
155 | SELECT [owner_name], [owner_books], ROW_NUMBER() OVER( ) AS [rno__row__index] |
156 | FROM ( |
157 | SELECT [owner].[name] AS [owner_name], |
158 | ( SELECT COUNT( * ) FROM [owners] [owner] WHERE [count].[id] = [owner].[id] ) AS [owner_books] |
159 | FROM [books] [me] |
160 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
161 | WHERE ( [source] = ? ) |
162 | ) [me] |
163 | ) [me] |
164 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
165 | )', |
166 | [ |
167 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
168 | => 'Library' ], |
169 | [ $OFFSET => 1 ], |
170 | [ $TOTAL => 1 ], |
171 | ], |
172 | ); |
173 | |
174 | } |
175 | |
176 | { |
d7632687 |
177 | my $rs = $schema->resultset('Artist')->search({}, { |
178 | columns => 'name', |
179 | offset => 1, |
180 | order_by => 'name', |
181 | }); |
182 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
183 | |
184 | like ( |
185 | ${$rs->as_query}->[0], |
186 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
187 | 'Newlines/spaces preserved in final sql', |
188 | ); |
189 | } |
190 | |
f74d22e2 |
191 | { |
192 | my $subq = $schema->resultset('Owners')->search({ |
193 | 'books.owner' => { -ident => 'owner.id' }, |
194 | }, { alias => 'owner', select => ['id'] } )->count_rs; |
195 | |
196 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } ); |
197 | |
198 | is_same_sql_bind( |
199 | $rs_selectas_rel->as_query, |
200 | '( |
201 | SELECT [id], [owner] FROM ( |
202 | SELECT [id], [owner], ROW_NUMBER() OVER( ) AS [rno__row__index] FROM ( |
203 | SELECT [me].[id], [me].[owner] |
204 | FROM [books] [me] |
205 | WHERE ( ( (EXISTS ( |
206 | SELECT COUNT( * ) FROM [owners] [owner] WHERE ( [books].[owner] = [owner].[id] ) |
207 | )) AND [source] = ? ) ) |
208 | ) [me] |
209 | ) [me] WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
210 | )', |
211 | [ |
212 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
213 | [ $OFFSET => 1 ], |
214 | [ $TOTAL => 1 ], |
215 | ], |
216 | 'Pagination with sub-query in WHERE works' |
217 | ); |
218 | |
219 | } |
220 | |
d7632687 |
221 | |
a5f843e3 |
222 | done_testing; |