Another candidate for somethingawful.com (fix left join-ed count)
[dbsrgits/DBIx-Class-Historic.git] / t / 42toplimit.t
CommitLineData
65c2b042 1use strict;
2use warnings;
3
4use Test::More;
65c2b042 5use lib qw(t/lib);
e606d0ce 6use DBICTest;
65c2b042 7use DBIC::SqlMakerTest;
8
e606d0ce 9my $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,
13# but some conditions needs a $rsrc
e606d0ce 14delete $schema->storage->_sql_maker->{_cached_syntax};
15$schema->storage->_sql_maker->limit_dialect ('Top');
16
17my $rs = $schema->resultset ('FourKeys')->search ({}, { rows => 1, offset => 3 });
65c2b042 18
19sub test_order {
20 my $args = shift;
65c2b042 21
e606d0ce 22 my $req_order = $args->{order_req}
23 ? "ORDER BY $args->{order_req}"
24 : ''
25 ;
26
27 is_same_sql_bind(
28 $rs->search ({}, {order_by => $args->{order_by}})->as_query,
29 "(
30 SELECT * FROM (
31 SELECT TOP 1 * FROM (
32 SELECT TOP 4 me.foo, me.bar, me.hello, me.goodbye, me.sensors, me.read_count FROM fourkeys me ORDER BY $args->{order_inner}
33 ) foo ORDER BY $args->{order_outer}
34 ) bar
35 $req_order
36 )",
37 [],
65c2b042 38 );
39}
40
e606d0ce 41my @tests = (
42 {
43 order_by => \ 'foo DESC',
44 order_req => 'foo DESC',
45 order_inner => 'foo DESC',
46 order_outer => 'foo ASC'
47 },
48 {
49 order_by => { -asc => 'foo' },
50 order_req => 'foo ASC',
51 order_inner => 'foo ASC',
52 order_outer => 'foo DESC',
53 },
54 {
55 order_by => 'foo',
56 order_req => 'foo',
57 order_inner => 'foo ASC',
58 order_outer => 'foo DESC',
59 },
60 {
61 order_by => [ qw{ foo bar} ],
62 order_req => 'foo, bar',
63 order_inner => 'foo ASC,bar ASC',
64 order_outer => 'foo DESC, bar DESC',
65 },
66 {
67 order_by => { -desc => 'foo' },
68 order_req => 'foo DESC',
69 order_inner => 'foo DESC',
70 order_outer => 'foo ASC',
71 },
72 {
73 order_by => ['foo', { -desc => 'bar' } ],
74 order_req => 'foo, bar DESC',
75 order_inner => 'foo ASC, bar DESC',
76 order_outer => 'foo DESC, bar ASC',
77 },
78 {
79 order_by => { -asc => [qw{ foo bar }] },
80 order_req => 'foo ASC, bar ASC',
81 order_inner => 'foo ASC, bar ASC',
82 order_outer => 'foo DESC, bar DESC',
83 },
84 {
85 order_by => [
86 { -asc => 'foo' },
87 { -desc => [qw{bar}] },
88 { -asc => [qw{hello sensors}]},
89 ],
90 order_req => 'foo ASC, bar DESC, hello ASC, sensors ASC',
91 order_inner => 'foo ASC, bar DESC, hello ASC, sensors ASC',
92 order_outer => 'foo DESC, bar ASC, hello DESC, sensors DESC',
93 },
94 {
95 order_by => undef,
96 order_req => undef,
97 order_inner => 'foo ASC, bar ASC, hello ASC, goodbye ASC',
98 order_outer => 'foo DESC, bar DESC, hello DESC, goodbye DESC',
99 },
100 {
101 order_by => '',
102 order_req => undef,
103 order_inner => 'foo ASC, bar ASC, hello ASC, goodbye ASC',
104 order_outer => 'foo DESC, bar DESC, hello DESC, goodbye DESC',
105 },
106 {
107 order_by => {},
108 order_req => undef,
109 order_inner => 'foo ASC, bar ASC, hello ASC, goodbye ASC',
110 order_outer => 'foo DESC, bar DESC, hello DESC, goodbye DESC',
111 },
112 {
113 order_by => [],
114 order_req => undef,
115 order_inner => 'foo ASC, bar ASC, hello ASC, goodbye ASC',
116 order_outer => 'foo DESC, bar DESC, hello DESC, goodbye DESC',
117 },
118);
6a67b557 119
8f6dbee9 120plan (tests => scalar @tests + 1);
121
e606d0ce 122test_order ($_) for @tests;
8f6dbee9 123
124is_same_sql_bind (
125 $rs->search ({}, { group_by => 'bar', order_by => 'bar' })->as_query,
126 '(
127 SELECT * FROM
128 (
129 SELECT TOP 1 * FROM
130 (
131 SELECT TOP 4 me.foo, me.bar, me.hello, me.goodbye, me.sensors, me.read_count FROM fourkeys me GROUP BY bar ORDER BY bar ASC
132 ) AS foo
133 ORDER BY bar DESC
134 ) AS bar
135 ORDER BY bar
136 )',
137 [],
138);