Commit | Line | Data |
65c2b042 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
65c2b042 |
5 | use lib qw(t/lib); |
e606d0ce |
6 | use DBICTest; |
65c2b042 |
7 | use DBIC::SqlMakerTest; |
ed0648ee |
8 | |
e606d0ce |
9 | my $schema = DBICTest->init_schema; |
65c2b042 |
10 | |
e606d0ce |
11 | # Trick the sqlite DB to use Top limit emulation |
8f6dbee9 |
12 | # We could test all of this via $sq->$op directly, |
ed0648ee |
13 | # but some conditions need a $rsrc |
e606d0ce |
14 | $schema->storage->_sql_maker->limit_dialect ('Top'); |
15 | |
07dc2055 |
16 | my $rs = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 1, offset => 3 }); |
65c2b042 |
17 | |
83dee2e9 |
18 | sub default_test_order { |
19 | my $order_by = shift; |
20 | is_same_sql_bind( |
21 | $rs->search ({}, {order_by => $order_by})->as_query, |
22 | "(SELECT |
23 | TOP 1 me__id, source, owner, title, price, owner__id, name FROM |
24 | (SELECT |
25 | TOP 4 me.id AS me__id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name |
26 | FROM books me |
27 | JOIN owners owner ON |
28 | owner.id = me.owner |
29 | WHERE ( source = ? ) |
30 | ORDER BY me__id ASC |
31 | ) me ORDER BY me__id DESC |
32 | )", |
33 | [ [ source => 'Library' ] ], |
34 | ); |
35 | } |
36 | |
65c2b042 |
37 | sub test_order { |
38 | my $args = shift; |
65c2b042 |
39 | |
e606d0ce |
40 | my $req_order = $args->{order_req} |
41 | ? "ORDER BY $args->{order_req}" |
42 | : '' |
43 | ; |
44 | |
45 | is_same_sql_bind( |
46 | $rs->search ({}, {order_by => $args->{order_by}})->as_query, |
83dee2e9 |
47 | "(SELECT |
48 | me__id, source, owner, title, price, owner__id, name FROM |
49 | (SELECT |
50 | TOP 1 me__id, source, owner, title, price, owner__id, name FROM |
ed0648ee |
51 | (SELECT |
52 | TOP 4 me.id AS me__id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name FROM |
53 | books me |
54 | JOIN owners owner ON owner.id = me.owner |
55 | WHERE ( source = ? ) |
56 | ORDER BY $args->{order_inner} |
57 | ) me ORDER BY $args->{order_outer} |
83dee2e9 |
58 | ) me $req_order |
e606d0ce |
59 | )", |
07dc2055 |
60 | [ [ source => 'Library' ] ], |
65c2b042 |
61 | ); |
62 | } |
63 | |
e606d0ce |
64 | my @tests = ( |
65 | { |
83dee2e9 |
66 | order_by => \'foo DESC', |
e606d0ce |
67 | order_req => 'foo DESC', |
68 | order_inner => 'foo DESC', |
83dee2e9 |
69 | order_outer => 'foo ASC' |
e606d0ce |
70 | }, |
71 | { |
72 | order_by => { -asc => 'foo' }, |
73 | order_req => 'foo ASC', |
74 | order_inner => 'foo ASC', |
75 | order_outer => 'foo DESC', |
76 | }, |
77 | { |
78 | order_by => 'foo', |
79 | order_req => 'foo', |
80 | order_inner => 'foo ASC', |
81 | order_outer => 'foo DESC', |
82 | }, |
83 | { |
84 | order_by => [ qw{ foo bar} ], |
85 | order_req => 'foo, bar', |
0491b597 |
86 | order_inner => 'foo ASC, bar ASC', |
e606d0ce |
87 | order_outer => 'foo DESC, bar DESC', |
88 | }, |
89 | { |
90 | order_by => { -desc => 'foo' }, |
91 | order_req => 'foo DESC', |
92 | order_inner => 'foo DESC', |
93 | order_outer => 'foo ASC', |
94 | }, |
95 | { |
96 | order_by => ['foo', { -desc => 'bar' } ], |
97 | order_req => 'foo, bar DESC', |
98 | order_inner => 'foo ASC, bar DESC', |
99 | order_outer => 'foo DESC, bar ASC', |
100 | }, |
101 | { |
102 | order_by => { -asc => [qw{ foo bar }] }, |
103 | order_req => 'foo ASC, bar ASC', |
104 | order_inner => 'foo ASC, bar ASC', |
105 | order_outer => 'foo DESC, bar DESC', |
106 | }, |
107 | { |
108 | order_by => [ |
109 | { -asc => 'foo' }, |
110 | { -desc => [qw{bar}] }, |
111 | { -asc => [qw{hello sensors}]}, |
112 | ], |
113 | order_req => 'foo ASC, bar DESC, hello ASC, sensors ASC', |
114 | order_inner => 'foo ASC, bar DESC, hello ASC, sensors ASC', |
115 | order_outer => 'foo DESC, bar ASC, hello DESC, sensors DESC', |
116 | }, |
e606d0ce |
117 | ); |
6a67b557 |
118 | |
83dee2e9 |
119 | my @default_tests = ( undef, '', {}, [] ); |
120 | |
e606d0ce |
121 | test_order ($_) for @tests; |
83dee2e9 |
122 | default_test_order ($_) for @default_tests; |
123 | |
8f6dbee9 |
124 | |
125 | is_same_sql_bind ( |
07dc2055 |
126 | $rs->search ({}, { group_by => 'title', order_by => 'title' })->as_query, |
83dee2e9 |
127 | '(SELECT |
128 | me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name FROM |
129 | ( SELECT |
130 | id, source, owner, title, price FROM |
131 | ( SELECT |
ed0648ee |
132 | TOP 1 id, source, owner, title, price FROM |
133 | ( SELECT |
134 | TOP 4 me.id, me.source, me.owner, me.title, me.price FROM |
135 | books me JOIN |
136 | owners owner ON owner.id = me.owner |
137 | WHERE ( source = ? ) |
138 | GROUP BY title |
139 | ORDER BY title ASC |
140 | ) me |
141 | ORDER BY title DESC |
83dee2e9 |
142 | ) me |
143 | ORDER BY title |
144 | ) me JOIN |
145 | owners owner ON owner.id = me.owner WHERE |
146 | ( source = ? ) |
147 | ORDER BY title)' , |
148 | [ [ source => 'Library' ], [ source => 'Library' ] ], |
8f6dbee9 |
149 | ); |
a5f843e3 |
150 | |
41eac664 |
151 | my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, { |
152 | '+select' => ['owner.name'], |
153 | '+as' => ['owner_name'], |
154 | join => 'owner', |
155 | rows => 1 |
156 | }); |
a5f843e3 |
157 | |
158 | is_same_sql_bind( $rs_selectas_top->search({})->as_query, |
41eac664 |
159 | '(SELECT |
160 | TOP 1 me.id, me.source, me.owner, me.title, me.price, |
161 | owner.name |
162 | FROM books me |
163 | JOIN owners owner ON owner.id = me.owner |
164 | WHERE ( source = ? ) |
a5f843e3 |
165 | ORDER BY me.id ASC |
41eac664 |
166 | )', |
a5f843e3 |
167 | [ [ 'source', 'Library' ] ], |
168 | ); |
169 | |
170 | done_testing; |