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 ({}, { prefetch => 'owner', rows => 2, offset => 3 });
24 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
27 '(SELECT id, source, owner, title, price, owner__id, owner__name
29 SELECT me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name
31 JOIN owners owner ON owner.id = me.owner
34 FETCH FIRST 5 ROWS ONLY
37 FETCH FIRST 2 ROWS ONLY
39 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
47 order_by => \'foo DESC',
48 order_inner => 'foo DESC',
49 order_outer => 'ORDER__BY__1 ASC',
50 order_req => 'ORDER__BY__1 DESC',
51 exselect_outer => 'ORDER__BY__1',
52 exselect_inner => 'foo AS ORDER__BY__1',
55 order_by => { -asc => 'foo' },
56 order_inner => 'foo ASC',
57 order_outer => 'ORDER__BY__1 DESC',
58 order_req => 'ORDER__BY__1 ASC',
59 exselect_outer => 'ORDER__BY__1',
60 exselect_inner => 'foo AS ORDER__BY__1',
63 order_by => { -desc => 'foo' },
64 order_inner => 'foo DESC',
65 order_outer => 'ORDER__BY__1 ASC',
66 order_req => 'ORDER__BY__1 DESC',
67 exselect_outer => 'ORDER__BY__1',
68 exselect_inner => 'foo AS ORDER__BY__1',
73 order_outer => 'ORDER__BY__1 DESC',
74 order_req => 'ORDER__BY__1',
75 exselect_outer => 'ORDER__BY__1',
76 exselect_inner => 'foo AS ORDER__BY__1',
79 order_by => [ qw{ foo me.owner} ],
80 order_inner => 'foo, me.owner',
81 order_outer => 'ORDER__BY__1 DESC, me.owner DESC',
82 order_req => 'ORDER__BY__1, me.owner',
83 exselect_outer => 'ORDER__BY__1',
84 exselect_inner => 'foo AS ORDER__BY__1',
87 order_by => ['foo', { -desc => 'bar' } ],
88 order_inner => 'foo, bar DESC',
89 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC',
90 order_req => 'ORDER__BY__1, ORDER__BY__2 DESC',
91 exselect_outer => 'ORDER__BY__1, ORDER__BY__2',
92 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2',
95 order_by => { -asc => [qw{ foo bar }] },
96 order_inner => 'foo ASC, bar ASC',
97 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 DESC',
98 order_req => 'ORDER__BY__1 ASC, ORDER__BY__2 ASC',
99 exselect_outer => 'ORDER__BY__1, ORDER__BY__2',
100 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2',
105 { -desc => [qw{bar}] },
106 { -asc => [qw{me.owner sensors}]},
108 order_inner => 'foo, bar DESC, me.owner ASC, sensors ASC',
109 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC, me.owner DESC, ORDER__BY__3 DESC',
110 order_req => 'ORDER__BY__1, ORDER__BY__2 DESC, me.owner ASC, ORDER__BY__3 ASC',
111 exselect_outer => 'ORDER__BY__1, ORDER__BY__2, ORDER__BY__3',
112 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2, sensors AS ORDER__BY__3',
115 my $o_sel = $ord_set->{exselect_outer}
116 ? ', ' . $ord_set->{exselect_outer}
119 my $i_sel = $ord_set->{exselect_inner}
120 ? ', ' . $ord_set->{exselect_inner}
125 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
126 "(SELECT id, source, owner, title, price, owner__id, owner__name
128 SELECT id, source, owner, title, price, owner__id, owner__name$o_sel
130 SELECT me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
132 JOIN owners owner ON owner.id = me.owner
134 ORDER BY $ord_set->{order_inner}
135 FETCH FIRST 5 ROWS ONLY
137 ORDER BY $ord_set->{order_outer}
138 FETCH FIRST 2 ROWS ONLY
140 ORDER BY $ord_set->{order_req}
141 FETCH FIRST 2 ROWS ONLY
143 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
150 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
151 '(SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name
153 SELECT id, source, owner, title, price
155 SELECT id, source, owner, title, price
157 SELECT me.id, me.source, me.owner, me.title, me.price
159 JOIN owners owner ON owner.id = me.owner
163 FETCH FIRST 5 ROWS ONLY
166 FETCH FIRST 2 ROWS ONLY
169 FETCH FIRST 2 ROWS ONLY
171 JOIN owners owner ON owner.id = me.owner
176 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
181 # test deprecated column mixing over join boundaries
182 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
183 '+select' => ['owner.name'],
184 '+as' => ['owner_name'],
189 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
191 me.id, me.source, me.owner, me.title, me.price,
192 owner.name AS owner_name
194 JOIN owners owner ON owner.id = me.owner
197 FETCH FIRST 1 ROWS ONLY
199 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
204 my $rs = $schema->resultset('Artist')->search({}, {
209 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
212 ${$rs->as_query}->[0],
213 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
214 'Newlines/spaces preserved in final sql',