7 use DBIC::SqlMakerTest;
9 my $schema = DBICTest->init_schema;
11 # Trick the sqlite DB to use Top limit emulation
12 # We could test all of this via $sq->$op directly,
13 # but some conditions need a $rsrc
14 delete $schema->storage->_sql_maker->{_cached_syntax};
15 $schema->storage->_sql_maker->limit_dialect ('Top');
17 my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 2, offset => 3 });
26 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
30 id, source, owner, title, price, owner__id, owner__name
33 me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name
35 JOIN owners owner ON owner.id = me.owner
41 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
47 my $subq = $schema->resultset('Owners')->search({
48 'count.id' => { -ident => 'owner.id' },
49 }, { alias => 'owner' })->count_rs;
51 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, {
53 { owner_name => 'owner.name' },
54 { owner_books => $subq->as_query },
62 $rs_selectas_rel->as_query,
64 SELECT TOP 2 owner_name, owner_books
66 SELECT TOP 5 owner.name AS owner_name,
69 WHERE ( count.id = owner.id )
72 JOIN owners owner ON owner.id = me.owner
78 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
80 'pagination with subqueries works'
87 order_by => \'foo DESC',
88 order_inner => 'foo DESC',
89 order_outer => 'ORDER__BY__1 ASC',
90 order_req => 'ORDER__BY__1 DESC',
91 exselect_outer => 'ORDER__BY__1',
92 exselect_inner => 'foo AS ORDER__BY__1',
95 order_by => { -asc => 'foo' },
96 order_inner => 'foo ASC',
97 order_outer => 'ORDER__BY__1 DESC',
98 order_req => 'ORDER__BY__1 ASC',
99 exselect_outer => 'ORDER__BY__1',
100 exselect_inner => 'foo AS ORDER__BY__1',
103 order_by => { -desc => 'foo' },
104 order_inner => 'foo DESC',
105 order_outer => 'ORDER__BY__1 ASC',
106 order_req => 'ORDER__BY__1 DESC',
107 exselect_outer => 'ORDER__BY__1',
108 exselect_inner => 'foo AS ORDER__BY__1',
112 order_inner => 'foo',
113 order_outer => 'ORDER__BY__1 DESC',
114 order_req => 'ORDER__BY__1',
115 exselect_outer => 'ORDER__BY__1',
116 exselect_inner => 'foo AS ORDER__BY__1',
119 order_by => [ qw{ foo me.owner} ],
120 order_inner => 'foo, me.owner',
121 order_outer => 'ORDER__BY__1 DESC, me.owner DESC',
122 order_req => 'ORDER__BY__1, me.owner',
123 exselect_outer => 'ORDER__BY__1',
124 exselect_inner => 'foo AS ORDER__BY__1',
127 order_by => ['foo', { -desc => 'bar' } ],
128 order_inner => 'foo, bar DESC',
129 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC',
130 order_req => 'ORDER__BY__1, ORDER__BY__2 DESC',
131 exselect_outer => 'ORDER__BY__1, ORDER__BY__2',
132 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2',
135 order_by => { -asc => [qw{ foo bar }] },
136 order_inner => 'foo ASC, bar ASC',
137 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 DESC',
138 order_req => 'ORDER__BY__1 ASC, ORDER__BY__2 ASC',
139 exselect_outer => 'ORDER__BY__1, ORDER__BY__2',
140 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2',
145 { -desc => [qw{bar}] },
146 { -asc => [qw{me.owner sensors}]},
148 order_inner => 'foo, bar DESC, me.owner ASC, sensors ASC',
149 order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC, me.owner DESC, ORDER__BY__3 DESC',
150 order_req => 'ORDER__BY__1, ORDER__BY__2 DESC, me.owner ASC, ORDER__BY__3 ASC',
151 exselect_outer => 'ORDER__BY__1, ORDER__BY__2, ORDER__BY__3',
152 exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2, sensors AS ORDER__BY__3',
155 my $o_sel = $ord_set->{exselect_outer}
156 ? ', ' . $ord_set->{exselect_outer}
159 my $i_sel = $ord_set->{exselect_inner}
160 ? ', ' . $ord_set->{exselect_inner}
165 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
167 id, source, owner, title, price, owner__id, owner__name
170 id, source, owner, title, price, owner__id, owner__name$o_sel
173 me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
175 JOIN owners owner ON owner.id = me.owner
177 ORDER BY $ord_set->{order_inner}
179 ORDER BY $ord_set->{order_outer}
181 ORDER BY $ord_set->{order_req}
183 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
190 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
191 '(SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name
193 SELECT TOP 2 id, source, owner, title, price
196 id, source, owner, title, price
199 me.id, me.source, me.owner, me.title, me.price
201 JOIN owners owner ON owner.id = me.owner
210 JOIN owners owner ON owner.id = me.owner
215 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
220 # test deprecated column mixing over join boundaries
221 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
222 '+select' => ['owner.name'],
223 '+as' => ['owner_name'],
228 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
230 TOP 1 me.id, me.source, me.owner, me.title, me.price,
231 owner.name AS owner_name
233 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({}, {
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 = ? ) ) ORDER BY me.id)',
267 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
269 'Pagination with sub-query in WHERE works'