7 use DBIC::SqlMakerTest;
9 my $schema = DBICTest->init_schema;
12 delete $schema->storage->_sql_maker->{_cached_syntax};
13 $schema->storage->_sql_maker->limit_dialect ('FetchFirst');
15 my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, {
16 prefetch => 'owner', rows => 2, offset => 3,
17 columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ],
27 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
30 '(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
32 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name
34 JOIN owners owner ON owner.id = me.owner
37 FETCH FIRST 5 ROWS ONLY
40 FETCH FIRST 2 ROWS ONLY
42 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
50 order_by => \'title DESC',
51 order_inner => 'title DESC',
52 order_outer => 'ORDER__BY__001',
53 order_req => 'ORDER__BY__001 DESC',
54 exselect_outer => 'ORDER__BY__001',
55 exselect_inner => 'title AS ORDER__BY__001',
58 order_by => { -asc => 'title' },
59 order_inner => 'title',
60 order_outer => 'ORDER__BY__001 DESC',
61 order_req => 'ORDER__BY__001',
62 exselect_outer => 'ORDER__BY__001',
63 exselect_inner => 'title AS ORDER__BY__001',
66 order_by => { -desc => 'title' },
67 order_inner => 'title DESC',
68 order_outer => 'ORDER__BY__001',
69 order_req => 'ORDER__BY__001 DESC',
70 exselect_outer => 'ORDER__BY__001',
71 exselect_inner => 'title AS ORDER__BY__001',
75 order_inner => 'title',
76 order_outer => 'ORDER__BY__001 DESC',
77 order_req => 'ORDER__BY__001',
78 exselect_outer => 'ORDER__BY__001',
79 exselect_inner => 'title AS ORDER__BY__001',
82 order_by => [ qw{ title me.owner} ],
83 order_inner => 'title, me.owner',
84 order_outer => 'ORDER__BY__001 DESC, me.owner DESC',
85 order_req => 'ORDER__BY__001, me.owner',
86 exselect_outer => 'ORDER__BY__001',
87 exselect_inner => 'title AS ORDER__BY__001',
90 order_by => ['title', { -desc => 'bar' } ],
91 order_inner => 'title, bar DESC',
92 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002',
93 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC',
94 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
95 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
98 order_by => { -asc => [qw{ title bar }] },
99 order_inner => 'title, bar',
100 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC',
101 order_req => 'ORDER__BY__001, ORDER__BY__002',
102 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
103 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
108 { -desc => [qw{bar}] },
109 { -asc => [qw{me.owner sensors}]},
111 order_inner => 'title, bar DESC, me.owner, sensors',
112 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002, me.owner DESC, ORDER__BY__003 DESC',
113 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner, ORDER__BY__003',
114 exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003',
115 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003',
118 my $o_sel = $ord_set->{exselect_outer}
119 ? ', ' . $ord_set->{exselect_outer}
122 my $i_sel = $ord_set->{exselect_inner}
123 ? ', ' . $ord_set->{exselect_inner}
128 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
129 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
131 SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
133 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
135 JOIN owners owner ON owner.id = me.owner
137 ORDER BY $ord_set->{order_inner}
138 FETCH FIRST 5 ROWS ONLY
140 ORDER BY $ord_set->{order_outer}
141 FETCH FIRST 2 ROWS ONLY
143 ORDER BY $ord_set->{order_req}
145 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
152 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
153 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
155 SELECT me.id, me.source, me.owner, me.price, me.title
157 SELECT me.id, me.source, me.owner, me.price, me.title
159 SELECT me.id, me.source, me.owner, me.price, me.title
161 JOIN owners owner ON owner.id = me.owner
165 FETCH FIRST 5 ROWS ONLY
167 ORDER BY me.title DESC
168 FETCH FIRST 2 ROWS ONLY
172 JOIN owners owner ON owner.id = me.owner
177 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
182 # test deprecated column mixing over join boundaries
183 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
184 '+select' => ['owner.name'],
185 '+as' => ['owner_name'],
190 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
192 me.id, me.source, me.owner, me.title, me.price, owner.name
194 JOIN owners owner ON owner.id = me.owner
196 FETCH FIRST 1 ROWS ONLY
198 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
203 my $rs = $schema->resultset('Artist')->search({}, {
204 columns => 'artistid',
206 order_by => 'artistid',
208 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
211 ${$rs->as_query}->[0],
212 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
213 'Newlines/spaces preserved in final sql',