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}});
137 # query actually works
138 ok( defined $rs->count, 'Query actually works' );
142 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
144 SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
146 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
148 JOIN owners owner ON owner.id = me.owner
150 ORDER BY $ord_set->{order_inner}
151 FETCH FIRST 5 ROWS ONLY
153 ORDER BY $ord_set->{order_outer}
154 FETCH FIRST 2 ROWS ONLY
156 ORDER BY $ord_set->{order_req}
158 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
166 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
167 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
169 SELECT me.id, me.source, me.owner, me.price, me.title
171 SELECT me.id, me.source, me.owner, me.price, me.title
173 SELECT me.id, me.source, me.owner, me.price, me.title
175 JOIN owners owner ON owner.id = me.owner
179 FETCH FIRST 5 ROWS ONLY
182 FETCH FIRST 2 ROWS ONLY
186 JOIN owners owner ON owner.id = me.owner
191 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
196 # test deprecated column mixing over join boundaries
197 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
198 '+select' => ['owner.name'],
199 '+as' => ['owner_name'],
204 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
206 me.id, me.source, me.owner, me.title, me.price, owner.name
208 JOIN owners owner ON owner.id = me.owner
210 FETCH FIRST 1 ROWS ONLY
212 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
217 my $rs = $schema->resultset('Artist')->search({}, {
218 columns => 'artistid',
220 order_by => 'artistid',
222 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
225 ${$rs->as_query}->[0],
226 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
227 'Newlines/spaces preserved in final sql',