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