Revert parts of e1ab2f7a, the depchain got perturbed again
[dbsrgits/DBIx-Class.git] / t / 76select.t
CommitLineData
218aa968 1use strict;
c4af4c4e 2use warnings;
218aa968 3
4use Test::More;
5use Test::Exception;
6use lib qw(t/lib);
7use DBICTest;
faf79003 8use DBIC::SqlMakerTest;
218aa968 9
10my $schema = DBICTest->init_schema();
11
218aa968 12my $rs = $schema->resultset('CD')->search({},
13 {
14 '+select' => \ 'COUNT(*)',
15 '+as' => 'count'
16 }
17);
18lives_ok(sub { $rs->first->get_column('count') }, 'additional count rscolumn present');
19dies_ok(sub { $rs->first->get_column('nonexistent_column') }, 'nonexistant column requests still throw exceptions');
20
21$rs = $schema->resultset('CD')->search({},
22 {
23 '+select' => [ \ 'COUNT(*)', 'title' ],
24 '+as' => [ 'count', 'addedtitle' ]
25 }
26);
27lives_ok(sub { $rs->first->get_column('count') }, 'multiple +select/+as columns, 1st rscolumn present');
28lives_ok(sub { $rs->first->get_column('addedtitle') }, 'multiple +select/+as columns, 2nd rscolumn present');
29
30$rs = $schema->resultset('CD')->search({},
31 {
32 '+select' => [ \ 'COUNT(*)', 'title' ],
33 '+as' => [ 'count', 'addedtitle' ]
34 }
35)->search({},
36 {
37 '+select' => 'title',
38 '+as' => 'addedtitle2'
39 }
40);
41lives_ok(sub { $rs->first->get_column('count') }, '+select/+as chained search 1st rscolumn present');
42lives_ok(sub { $rs->first->get_column('addedtitle') }, '+select/+as chained search 1st rscolumn present');
43lives_ok(sub { $rs->first->get_column('addedtitle2') }, '+select/+as chained search 3rd rscolumn present');
0eb27426 44
45
46# test the from search attribute (gets between the FROM and WHERE keywords, allows arbitrary subselects)
47# also shows that outer select attributes are ok (i.e. order_by)
48#
49# from doesn't seem to be useful without using a scalarref - there were no initial tests >:(
50#
0eb27426 51my $cds = $schema->resultset ('CD')->search ({}, { order_by => 'me.cdid'}); # make sure order is consistent
52cmp_ok ($cds->count, '>', 2, 'Initially populated with more than 2 CDs');
53
54my $table = $cds->result_source->name;
80625830 55$table = $$table if ref $table eq 'SCALAR';
0eb27426 56my $subsel = $cds->search ({}, {
57 columns => [qw/cdid title/],
58 from => \ "(SELECT cdid, title FROM $table LIMIT 2) me",
59});
60
61is ($subsel->count, 2, 'Subselect correctly limited the rs to 2 cds');
62is ($subsel->next->title, $cds->next->title, 'First CD title match');
63is ($subsel->next->title, $cds->next->title, 'Second CD title match');
bbdff861 64
65is($schema->resultset('CD')->current_source_alias, "me", '$rs->current_source_alias returns "me"');
61d26fae 66
faf79003 67
68
69$rs = $schema->resultset('CD')->search({},
61d26fae 70 {
71 'join' => 'artist',
faf79003 72 'columns' => ['cdid', 'title', 'artist.name'],
61d26fae 73 }
74);
faf79003 75
faf79003 76is_same_sql_bind (
af6aac2d 77 $rs->as_query,
faf79003 78 '(SELECT me.cdid, me.title, artist.name FROM cd me JOIN artist artist ON artist.artistid = me.artist)',
79 [],
80 'Use of columns attribute results in proper sql'
81);
82
83lives_ok(sub {
84 $rs->first->get_column('cdid')
85}, 'columns 1st rscolumn present');
86
87lives_ok(sub {
88 $rs->first->get_column('title')
89}, 'columns 2nd rscolumn present');
90
0ca2f0d1 91lives_ok(sub {
bbd828a6 92 $rs->first->artist->get_column('name')
93}, 'columns 3rd rscolumn present');
4c253752 94
95
faf79003 96
97$rs = $schema->resultset('CD')->search({},
bbd828a6 98 {
faf79003 99 'join' => 'artist',
100 '+columns' => ['cdid', 'title', 'artist.name'],
101 }
102);
103
faf79003 104is_same_sql_bind (
af6aac2d 105 $rs->as_query,
37aafa2e 106 '(SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, artist.name FROM cd me JOIN artist artist ON artist.artistid = me.artist)',
faf79003 107 [],
108 'Use of columns attribute results in proper sql'
109);
110
111lives_ok(sub {
bbd828a6 112 $rs->first->get_column('cdid')
faf79003 113}, 'columns 1st rscolumn present');
114
115lives_ok(sub {
116 $rs->first->get_column('title')
117}, 'columns 2nd rscolumn present');
4c253752 118
0ca2f0d1 119lives_ok(sub {
120 $rs->first->artist->get_column('name')
121}, 'columns 3rd rscolumn present');
b3b13ec9 122
123
124$rs = $schema->resultset('CD')->search({'tracks.position' => { -in => [2] } },
125 {
126 join => 'tracks',
127 columns => [qw/me.cdid me.title/],
128 '+select' => ['tracks.position'],
129 '+as' => ['track_position'],
130
131 # get a hashref of CD1 only (the first with a second track)
132 result_class => 'DBIx::Class::ResultClass::HashRefInflator',
133 order_by => 'cdid',
134 rows => 1,
135 }
136);
137
138is_deeply (
139 $rs->single,
140 {
141 cdid => 1,
142 track_position => 2,
143 title => 'Spoonful of bees',
144 },
145 'limited prefetch via column works on a multi-relationship',
146);
147
18850595 148my $sub_rs = $rs->search ({},
b3b13ec9 149 {
150 columns => [qw/artist tracks.trackid/], # columns should not be merged but override $rs columns
151 '+select' => ['tracks.title'],
152 '+as' => ['tracks.title'],
153 }
154);
155
c9d29bb2 156is_deeply(
b3b13ec9 157 $sub_rs->single,
158 {
c9d29bb2 159 artist => 1,
0d5e6c6c 160 tracks => {
161 title => 'Apiary',
1dfb11d4 162 trackid => 17,
c9d29bb2 163 },
b3b13ec9 164 },
165 'columns/select/as fold properly on sub-searches',
166);
167
fd4b0742 168# *very* esoteric use-case, yet valid (the "empty" object should not be undef):
169$rs = $schema->resultset('Artist');
170$rs->create({ artistid => 69, name => 'Ranetki' });
171
172my $relations_or_1_count =
173 $rs->search_related('cds')->count
174 +
175 $rs->search({ 'cds.cdid' => undef }, { join => 'cds' })->count
176;
177
178my $weird_rs = $rs->search({}, {
179 order_by => { -desc => [ 'me.artistid', 'cds.cdid' ] },
180 columns => [{ cd_title => 'cds.title', cd_year => 'cds.year' }],
181 join => 'cds',
182});
183
184my $weird_rs_hri = $weird_rs->search({}, { result_class => 'DBIx::Class::ResultClass::HashRefInflator' });
185
186for my $rs ($weird_rs, $weird_rs_hri) {
187 is ($rs->count, $relations_or_1_count, 'count on rhs data injection matches');
188
189 my @all;
190 while (my $r = $rs->next) {
191 push @all, $r;
192 }
193
194 is (scalar @all, $relations_or_1_count, 'object count on rhs data injection matches');
195 is_deeply (
196 ( $rs->result_class eq 'DBIx::Class::ResultClass::HashRefInflator'
197 ? \@all
198 : [ map { +{$_->get_columns} } @all ]
199 ),
200 [
201 {
202 cd_title => undef,
203 cd_year => undef,
204 },
205 {
206 cd_title => "Come Be Depressed With Us",
207 cd_year => 1998,
208 },
209 {
210 cd_title => "Generic Manufactured Singles",
211 cd_year => 2001,
212 },
213 {
214 cd_title => "Caterwaulin' Blues",
215 cd_year => 1997,
216 },
217 {
218 cd_title => "Forkful of bees",
219 cd_year => 2001,
220 },
221 {
222 cd_title => "Spoonful of bees",
223 cd_year => 1999,
224 },
225 ],
226 'Correct data retrieved'
227 );
228
229 is_deeply( [ $rs->all ], \@all, '->all matches' );
230}
231
c4af4c4e 232done_testing;