6 use DBICTest ':DiffSQL';
8 my $schema = DBICTest->init_schema;
10 # Trick the sqlite DB to use Top limit emulation
11 # We could test all of this via $sq->$op directly,
12 # but some conditions need a $rsrc
13 delete $schema->storage->_sql_maker->{_cached_syntax};
14 $schema->storage->_sql_maker->limit_dialect ('Top');
16 my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, {
17 prefetch => 'owner', rows => 2, offset => 3,
18 columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ],
28 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
32 me.id, me.source, me.owner, me.price, owner__id, owner__name
35 me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name
37 JOIN owners owner ON owner.id = me.owner
43 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
49 my $subq = $schema->resultset('Owners')->search({
50 'count.id' => { -ident => 'owner.id' },
51 }, { alias => 'owner' })->count_rs;
53 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, {
55 { owner_name => 'owner.name' },
56 { owner_books => $subq->as_query },
64 $rs_selectas_rel->as_query,
66 SELECT TOP 2 owner_name, owner_books
68 SELECT TOP 5 owner.name AS owner_name,
71 WHERE ( count.id = owner.id )
74 JOIN owners owner ON owner.id = me.owner
80 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
82 'pagination with subqueries works'
89 order_by => \'title DESC',
90 order_inner => 'title DESC',
91 order_outer => 'ORDER__BY__001 ASC',
92 order_req => 'ORDER__BY__001 DESC',
93 exselect_outer => 'ORDER__BY__001',
94 exselect_inner => 'title AS ORDER__BY__001',
97 order_by => { -asc => 'title' },
98 order_inner => 'title ASC',
99 order_outer => 'ORDER__BY__001 DESC',
100 order_req => 'ORDER__BY__001 ASC',
101 exselect_outer => 'ORDER__BY__001',
102 exselect_inner => 'title AS ORDER__BY__001',
105 order_by => { -desc => 'title' },
106 order_inner => 'title DESC',
107 order_outer => 'ORDER__BY__001 ASC',
108 order_req => 'ORDER__BY__001 DESC',
109 exselect_outer => 'ORDER__BY__001',
110 exselect_inner => 'title AS ORDER__BY__001',
114 order_inner => 'title',
115 order_outer => 'ORDER__BY__001 DESC',
116 order_req => 'ORDER__BY__001',
117 exselect_outer => 'ORDER__BY__001',
118 exselect_inner => 'title AS ORDER__BY__001',
121 order_by => [ qw{ title me.owner} ],
122 order_inner => 'title, me.owner',
123 order_outer => 'ORDER__BY__001 DESC, me.owner DESC',
124 order_req => 'ORDER__BY__001, me.owner',
125 exselect_outer => 'ORDER__BY__001',
126 exselect_inner => 'title AS ORDER__BY__001',
129 order_by => ['title', { -desc => 'bar' } ],
130 order_inner => 'title, bar DESC',
131 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC',
132 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC',
133 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
134 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
137 order_by => { -asc => [qw{ title bar }] },
138 order_inner => 'title ASC, bar ASC',
139 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC',
140 order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC',
141 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
142 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
147 { -desc => [qw{bar}] },
148 { -asc => [qw{me.owner sensors}]},
150 order_inner => 'title, bar DESC, me.owner ASC, sensors ASC',
151 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC',
152 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC',
153 exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003',
154 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003',
157 my $o_sel = $ord_set->{exselect_outer}
158 ? ', ' . $ord_set->{exselect_outer}
161 my $i_sel = $ord_set->{exselect_inner}
162 ? ', ' . $ord_set->{exselect_inner}
167 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
168 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
171 me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
174 me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
176 JOIN owners owner ON owner.id = me.owner
178 ORDER BY $ord_set->{order_inner}
180 ORDER BY $ord_set->{order_outer}
182 ORDER BY $ord_set->{order_req}
184 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
191 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
192 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
194 SELECT me.id, me.source, me.owner, me.price, me.title
197 me.id, me.source, me.owner, me.price, me.title
200 me.id, me.source, me.owner, me.price, me.title
202 JOIN owners owner ON owner.id = me.owner
211 JOIN owners owner ON owner.id = me.owner
216 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
221 # test deprecated column mixing over join boundaries
222 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
223 '+select' => ['owner.name'],
224 '+as' => ['owner_name'],
229 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
231 TOP 1 me.id, me.source, me.owner, me.title, me.price,
234 JOIN owners owner ON owner.id = me.owner
237 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
242 my $rs = $schema->resultset('Artist')->search({}, {
243 columns => 'artistid',
245 order_by => 'artistid',
247 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
250 ${$rs->as_query}->[0],
251 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
252 'Newlines/spaces preserved in final sql',
257 my $subq = $schema->resultset('Owners')->search({
258 'books.owner' => { -ident => 'owner.id' },
259 }, { alias => 'owner', select => ['id'] } )->count_rs;
261 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } );
264 $rs_selectas_rel->as_query,
265 '(SELECT TOP 1 me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) )',
267 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
269 'Pagination with sub-query in WHERE works'