1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
8 use DBICTest ':DiffSQL';
10 my $schema = DBICTest->init_schema;
13 delete $schema->storage->_sql_maker->{_cached_syntax};
14 $schema->storage->_sql_maker->limit_dialect ('FetchFirst');
16 my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, {
17 prefetch => 'owner', rows => 2, offset => 3,
18 columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ],
28 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
31 '(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
33 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name
35 JOIN owners owner ON owner.id = me.owner
38 FETCH FIRST 5 ROWS ONLY
41 FETCH FIRST 2 ROWS ONLY
43 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
51 order_by => \'title DESC',
52 order_inner => 'title DESC',
53 order_outer => 'ORDER__BY__001 ASC',
54 order_req => 'ORDER__BY__001 DESC',
55 exselect_outer => 'ORDER__BY__001',
56 exselect_inner => 'title AS ORDER__BY__001',
59 order_by => { -asc => 'title' },
60 order_inner => 'title ASC',
61 order_outer => 'ORDER__BY__001 DESC',
62 order_req => 'ORDER__BY__001 ASC',
63 exselect_outer => 'ORDER__BY__001',
64 exselect_inner => 'title AS ORDER__BY__001',
67 order_by => { -desc => 'title' },
68 order_inner => 'title DESC',
69 order_outer => 'ORDER__BY__001 ASC',
70 order_req => 'ORDER__BY__001 DESC',
71 exselect_outer => 'ORDER__BY__001',
72 exselect_inner => 'title AS ORDER__BY__001',
76 order_inner => 'title',
77 order_outer => 'ORDER__BY__001 DESC',
78 order_req => 'ORDER__BY__001',
79 exselect_outer => 'ORDER__BY__001',
80 exselect_inner => 'title AS ORDER__BY__001',
83 order_by => [ qw{ title me.owner} ],
84 order_inner => 'title, me.owner',
85 order_outer => 'ORDER__BY__001 DESC, me.owner DESC',
86 order_req => 'ORDER__BY__001, me.owner',
87 exselect_outer => 'ORDER__BY__001',
88 exselect_inner => 'title AS ORDER__BY__001',
91 order_by => ['title', { -desc => 'bar' } ],
92 order_inner => 'title, bar DESC',
93 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC',
94 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC',
95 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
96 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
99 order_by => { -asc => [qw{ title bar }] },
100 order_inner => 'title ASC, bar ASC',
101 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC',
102 order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC',
103 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
104 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
109 { -desc => [qw{bar}] },
110 { -asc => [qw{me.owner sensors}]},
112 order_inner => 'title, bar DESC, me.owner ASC, sensors ASC',
113 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC',
114 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC',
115 exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003',
116 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003',
123 order_inner => 'name',
124 order_outer => 'name DESC',
129 { -asc => 'title', -nulls => 'first' },
130 { -desc => 'bar', -nulls => 'last' },
132 order_inner => 'title ASC NULLS FIRST, bar DESC NULLS LAST',
133 order_outer => 'ORDER__BY__001 DESC NULLS LAST, ORDER__BY__002 ASC NULLS FIRST',
134 order_req => 'ORDER__BY__001 ASC NULLS FIRST, ORDER__BY__002 DESC NULLS LAST',
135 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
136 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
139 my $o_sel = $ord_set->{exselect_outer}
140 ? ', ' . $ord_set->{exselect_outer}
143 my $i_sel = $ord_set->{exselect_inner}
144 ? ', ' . $ord_set->{exselect_inner}
148 my $rs = $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}});
150 # query actually works
151 ok( defined $rs->count, 'Query actually works' );
155 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
157 SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
159 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
161 JOIN owners owner ON owner.id = me.owner
163 ORDER BY $ord_set->{order_inner}
164 FETCH FIRST 5 ROWS ONLY
166 ORDER BY $ord_set->{order_outer}
167 FETCH FIRST 2 ROWS ONLY
169 ORDER BY $ord_set->{order_req}
171 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
179 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
180 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
182 SELECT me.id, me.source, me.owner, me.price, me.title
184 SELECT me.id, me.source, me.owner, me.price, me.title
186 SELECT me.id, me.source, me.owner, me.price, me.title
188 JOIN owners owner ON owner.id = me.owner
192 FETCH FIRST 5 ROWS ONLY
195 FETCH FIRST 2 ROWS ONLY
199 JOIN owners owner ON owner.id = me.owner
204 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
209 # test deprecated column mixing over join boundaries
210 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
211 '+select' => ['owner.name'],
212 '+as' => ['owner_name'],
217 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
219 me.id, me.source, me.owner, me.title, me.price, owner.name
221 JOIN owners owner ON owner.id = me.owner
223 FETCH FIRST 1 ROWS ONLY
225 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
230 my $rs = $schema->resultset('Artist')->search({}, {
231 columns => 'artistid',
233 order_by => 'artistid',
235 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
238 ${$rs->as_query}->[0],
239 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
240 'Newlines/spaces preserved in final sql',