6 use DBICTest ':DiffSQL';
8 my $schema = DBICTest->init_schema;
11 delete $schema->storage->_sql_maker->{_cached_syntax};
12 $schema->storage->_sql_maker->limit_dialect ('FetchFirst');
14 my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, {
15 prefetch => 'owner', rows => 2, offset => 3,
16 columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ],
26 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
29 '(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
31 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name
33 JOIN owners owner ON owner.id = me.owner
36 FETCH FIRST 5 ROWS ONLY
39 FETCH FIRST 2 ROWS ONLY
41 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
49 order_by => \'title DESC',
50 order_inner => 'title DESC',
51 order_outer => 'ORDER__BY__001 ASC',
52 order_req => 'ORDER__BY__001 DESC',
53 exselect_outer => 'ORDER__BY__001',
54 exselect_inner => 'title AS ORDER__BY__001',
57 order_by => { -asc => 'title' },
58 order_inner => 'title ASC',
59 order_outer => 'ORDER__BY__001 DESC',
60 order_req => 'ORDER__BY__001 ASC',
61 exselect_outer => 'ORDER__BY__001',
62 exselect_inner => 'title AS ORDER__BY__001',
65 order_by => { -desc => 'title' },
66 order_inner => 'title DESC',
67 order_outer => 'ORDER__BY__001 ASC',
68 order_req => 'ORDER__BY__001 DESC',
69 exselect_outer => 'ORDER__BY__001',
70 exselect_inner => 'title AS ORDER__BY__001',
74 order_inner => 'title',
75 order_outer => 'ORDER__BY__001 DESC',
76 order_req => 'ORDER__BY__001',
77 exselect_outer => 'ORDER__BY__001',
78 exselect_inner => 'title AS ORDER__BY__001',
81 order_by => [ qw{ title me.owner} ],
82 order_inner => 'title, me.owner',
83 order_outer => 'ORDER__BY__001 DESC, me.owner DESC',
84 order_req => 'ORDER__BY__001, me.owner',
85 exselect_outer => 'ORDER__BY__001',
86 exselect_inner => 'title AS ORDER__BY__001',
89 order_by => ['title', { -desc => 'bar' } ],
90 order_inner => 'title, bar DESC',
91 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC',
92 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC',
93 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
94 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
97 order_by => { -asc => [qw{ title bar }] },
98 order_inner => 'title ASC, bar ASC',
99 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC',
100 order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC',
101 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
102 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
107 { -desc => [qw{bar}] },
108 { -asc => [qw{me.owner sensors}]},
110 order_inner => 'title, bar DESC, me.owner ASC, sensors ASC',
111 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC',
112 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC',
113 exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003',
114 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003',
121 order_inner => 'name',
122 order_outer => 'name DESC',
126 my $o_sel = $ord_set->{exselect_outer}
127 ? ', ' . $ord_set->{exselect_outer}
130 my $i_sel = $ord_set->{exselect_inner}
131 ? ', ' . $ord_set->{exselect_inner}
135 my $rs = $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}});
139 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
141 SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
143 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
145 JOIN owners owner ON owner.id = me.owner
147 ORDER BY $ord_set->{order_inner}
148 FETCH FIRST 5 ROWS ONLY
150 ORDER BY $ord_set->{order_outer}
151 FETCH FIRST 2 ROWS ONLY
153 ORDER BY $ord_set->{order_req}
155 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
163 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
164 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
166 SELECT me.id, me.source, me.owner, me.price, me.title
168 SELECT me.id, me.source, me.owner, me.price, me.title
170 SELECT me.id, me.source, me.owner, me.price, me.title
172 JOIN owners owner ON owner.id = me.owner
176 FETCH FIRST 5 ROWS ONLY
179 FETCH FIRST 2 ROWS ONLY
183 JOIN owners owner ON owner.id = me.owner
188 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
193 # test deprecated column mixing over join boundaries
194 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
195 '+select' => ['owner.name'],
196 '+as' => ['owner_name'],
201 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
203 me.id, me.source, me.owner, me.title, me.price, owner.name
205 JOIN owners owner ON owner.id = me.owner
207 FETCH FIRST 1 ROWS ONLY
209 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
214 my $rs = $schema->resultset('Artist')->search({}, {
215 columns => 'artistid',
217 order_by => 'artistid',
219 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
222 ${$rs->as_query}->[0],
223 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
224 'Newlines/spaces preserved in final sql',