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 ASC',
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 ASC',
60 order_outer => 'ORDER__BY__001 DESC',
61 order_req => 'ORDER__BY__001 ASC',
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 ASC',
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 ASC',
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 ASC, bar ASC',
100 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC',
101 order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC',
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 ASC, sensors ASC',
112 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC',
113 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC',
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',
122 order_inner => 'name',
123 order_outer => 'name DESC',
127 my $o_sel = $ord_set->{exselect_outer}
128 ? ', ' . $ord_set->{exselect_outer}
131 my $i_sel = $ord_set->{exselect_inner}
132 ? ', ' . $ord_set->{exselect_inner}
136 my $rs = $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}});
138 # query actually works
139 ok( defined $rs->count, 'Query actually works' );
143 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
145 SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
147 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
149 JOIN owners owner ON owner.id = me.owner
151 ORDER BY $ord_set->{order_inner}
152 FETCH FIRST 5 ROWS ONLY
154 ORDER BY $ord_set->{order_outer}
155 FETCH FIRST 2 ROWS ONLY
157 ORDER BY $ord_set->{order_req}
159 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
167 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
168 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
170 SELECT me.id, me.source, me.owner, me.price, me.title
172 SELECT me.id, me.source, me.owner, me.price, me.title
174 SELECT me.id, me.source, me.owner, me.price, me.title
176 JOIN owners owner ON owner.id = me.owner
180 FETCH FIRST 5 ROWS ONLY
182 ORDER BY me.title DESC
183 FETCH FIRST 2 ROWS ONLY
187 JOIN owners owner ON owner.id = me.owner
192 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
197 # test deprecated column mixing over join boundaries
198 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
199 '+select' => ['owner.name'],
200 '+as' => ['owner_name'],
205 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
207 me.id, me.source, me.owner, me.title, me.price, owner.name
209 JOIN owners owner ON owner.id = me.owner
211 FETCH FIRST 1 ROWS ONLY
213 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
218 my $rs = $schema->resultset('Artist')->search({}, {
219 columns => 'artistid',
221 order_by => 'artistid',
223 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
226 ${$rs->as_query}->[0],
227 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
228 'Newlines/spaces preserved in final sql',