Make sure DBICTest is always loaded first (purely bookkeep)
[dbsrgits/DBIx-Class-Historic.git] / t / search / select_chains.t
CommitLineData
3a55f069 1use strict;
2use warnings;
3
4use Test::More;
3a55f069 5
6use lib qw(t/lib);
3a55f069 7use DBICTest;
5e724964 8use DBIC::SqlMakerTest;
3a55f069 9
10my $schema = DBICTest->init_schema();
11
12my @chain = (
13 {
14 columns => [ 'cdid' ],
37aafa2e 15 '+columns' => [ { title_lc => { lower => 'title', -as => 'lctitle' } } ],
442363e5 16 '+select' => [ 'genreid' ],
17 '+as' => [ 'genreid' ],
37aafa2e 18 } => 'SELECT me.cdid, LOWER( title ) AS lctitle, me.genreid FROM cd me',
3a55f069 19
20 {
37aafa2e 21 '+columns' => [ { max_year => { max => 'me.year', -as => 'last_y' }}, ],
3a55f069 22 '+select' => [ { count => 'me.cdid' }, ],
23 '+as' => [ 'cnt' ],
f7f53a89 24 } => 'SELECT me.cdid, LOWER( title ) AS lctitle, MAX( me.year ) AS last_y, me.genreid, COUNT( me.cdid ) FROM cd me',
3a55f069 25
26 {
27 select => [ { min => 'me.cdid' }, ],
28 as => [ 'min_id' ],
29 } => 'SELECT MIN( me.cdid ) FROM cd me',
30
31 {
37aafa2e 32 '+columns' => [ { cnt => { count => 'cdid', -as => 'cnt' } } ],
f7f53a89 33 } => 'SELECT COUNT ( cdid ) AS cnt, MIN( me.cdid ) FROM cd me',
3a55f069 34
35 {
37aafa2e 36 columns => [ { foo => { coalesce => [qw/a b c/], -as => 'firstfound' } } ],
37 } => 'SELECT COALESCE( a, b, c ) AS firstfound FROM cd me',
38
39 {
40 '+columns' => [ 'me.year' ],
41 '+select' => [ { max => 'me.year', -as => 'last_y' } ],
42 '+as' => [ 'ly' ],
43 } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y FROM cd me',
44
45 {
46 '+select' => [ { count => 'me.cdid', -as => 'cnt' } ],
47 '+as' => [ 'cnt' ],
48 } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt FROM cd me',
49
50 # adding existing stuff should not alter selector
51 {
52 '+select' => [ 'me.year' ],
53 '+as' => [ 'year' ],
f7f53a89 54 } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year FROM cd me',
37aafa2e 55
56 {
57 '+columns' => [ 'me.year' ],
f7f53a89 58 } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year FROM cd me',
37aafa2e 59
60 {
61 '+columns' => 'me.year',
f7f53a89 62 } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year FROM cd me',
63
64 # naked selector at the end should just work
65 {
66 '+select' => 'me.moar_stuff',
67 } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year, me.moar_stuff FROM cd me',
68
69 {
70 '+select' => [ { MOAR => 'f', -as => 'func' } ],
71 } => 'SELECT COALESCE( a, b, c ) AS firstfound, me.year, MAX( me.year ) AS last_y, COUNT( me.cdid ) AS cnt, me.year, me.moar_stuff, MOAR(f) AS func FROM cd me',
37aafa2e 72
3a55f069 73);
74
75my $rs = $schema->resultset('CD');
76
77my $testno = 1;
78while (@chain) {
79 my $attrs = shift @chain;
80 my $sql = shift @chain;
81
82 $rs = $rs->search ({}, $attrs);
83
84 is_same_sql_bind (
85 $rs->as_query,
14af4aa0 86 "($sql)",
3a55f069 87 [],
88 "Test $testno of SELECT assembly ok",
89 );
90
91 $testno++;
92}
93
37aafa2e 94# Make sure we don't lose bits even with weird selector specs
eb37b376 95# also check that the default selector list is lazy
f7f53a89 96# and make sure that unaliased +select does not go crazy
eb37b376 97$rs = $schema->resultset('CD');
98for my $attr (
99 { '+columns' => [ 'me.title' ] }, # this one should be de-duplicated but not the select's
100
101 { '+select' => \'me.year AS foo' }, # duplication of identical select expected (FIXME ?)
102 { '+select' => \['me.year AS foo'] },
103
104 { '+select' => [ \'me.artistid AS bar' ] },
105 { '+select' => { count => 'artistid', -as => 'baz' } },
106) {
107 for (qw/columns select as/) {
108 ok (! exists $rs->{attrs}{$_}, "No eager '$_' attr on fresh resultset" );
109 }
110
111 $rs = $rs->search({}, $attr);
112}
37aafa2e 113
114is_same_sql_bind (
115 $rs->as_query,
116 '( SELECT
eb37b376 117 me.cdid,
118 me.artist,
37aafa2e 119 me.title,
eb37b376 120 me.year,
121 me.genreid,
122 me.single_track,
37aafa2e 123 me.year AS foo,
eb37b376 124 me.year AS foo,
f7f53a89 125 me.artistid AS bar,
126 COUNT( artistid ) AS baz
37aafa2e 127 FROM cd me
128 )',
129 [],
130 'Correct chaining before attr resolution'
131);
132
dc81dba3 133# Test the order of columns
134$rs = $schema->resultset('CD')->search ({}, {
135 'select' => [ 'me.cdid', 'me.title' ],
136});
137
138is_same_sql_bind (
139 $rs->as_query,
140 '( SELECT
141 me.cdid,
142 me.title
143 FROM cd me
144 )',
145 [],
146 'Correct order of selected columns'
147);
148
f7f53a89 149# Test bare +select with as from root of resultset
150$rs = $schema->resultset('CD')->search ({}, {
151 '+select' => [
152 \ 'foo',
153 { MOAR => 'f', -as => 'func' },
154 ],
155});
156
157is_same_sql_bind (
158 $rs->as_query,
159 '( SELECT
160 me.cdid,
161 me.artist,
162 me.title,
163 me.year,
164 me.genreid,
165 me.single_track,
166 foo,
167 MOAR( f ) AS func
168 FROM cd me
169 )',
170 [],
171 'Correct order of selected columns'
172);
173
b773093f 174done_testing;