1use strict;
2use warnings;
3
4use Test::More;
5use Test::Exception;
6use lib qw(t/lib);
7use DBICTest;
8use DBIC::SqlMakerTest;
9
10my $schema = DBICTest->init_schema();
11
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');
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#
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;
55$table = $$table if ref $table eq 'SCALAR';
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');
64
65is($schema->resultset('CD')->current_source_alias, "me", '$rs->current_source_alias returns "me"');
66
67
68
69$rs = $schema->resultset('CD')->search({},
70    {
71        'join' => 'artist',
72        'columns' => ['cdid', 'title', 'artist.name'],
73    }
74);
75
76is_same_sql_bind (
77  $rs->as_query,
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
91lives_ok(sub {
92  $rs->first->artist->get_column('name')
93}, 'columns 3rd rscolumn present');
94
95
96
97$rs = $schema->resultset('CD')->search({},
98    {
99        'join' => 'artist',
100        '+columns' => ['cdid', 'title', 'artist.name'],
101    }
102);
103
104is_same_sql_bind (
105  $rs->as_query,
106  '(SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, me.cdid, me.title, artist.name FROM cd me  JOIN artist artist ON artist.artistid = me.artist)',
107  [],
108  'Use of columns attribute results in proper sql'
109);
110
111lives_ok(sub {
112  $rs->first->get_column('cdid')
113}, 'columns 1st rscolumn present');
114
115lives_ok(sub {
116  $rs->first->get_column('title')
117}, 'columns 2nd rscolumn present');
118
119lives_ok(sub {
120  $rs->first->artist->get_column('name')
121}, 'columns 3rd rscolumn present');
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
148my $sub_rs = $rs->search ({},
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
156is_deeply(
157  $sub_rs->single,
158  {
159    artist         => 1,
160    tracks => {
161      title => 'Apiary',
162      trackid => 17,
163    },
164  },
165  'columns/select/as fold properly on sub-searches',
166);
167
168done_testing;
169