1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
|
use strict;
use warnings;
use Test::More;
use Test::Exception;
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
my $schema = DBICTest->init_schema();
my $orig_debug = $schema->storage->debug;
my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }});
my $cd_data = { map {
$_->cdid => {
siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1,
track_titles => [ map { $_->title } ($_->tracks->all) ],
},
} ( $cdrs->all ) };
my $c_rs = $cdrs->search ({}, {
prefetch => 'tracks',
'+columns' => { sibling_count => $cdrs->search(
{
'siblings.artist' => { -ident => 'me.artist' },
'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 23414] },
}, { alias => 'siblings' },
)->count_rs->as_query,
},
});
is_same_sql_bind(
$c_rs->as_query,
'(
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
(SELECT COUNT( * )
FROM cd siblings
WHERE siblings.artist = me.artist
AND siblings.cdid != me.cdid
AND siblings.cdid != ?
AND me.artist != ?
),
tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
FROM cd me
LEFT JOIN track tracks
ON tracks.cd = me.cdid
WHERE me.artist != ?
ORDER BY tracks.cd
)',
[
# subselect
[ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
=> 23414 ],
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
# outher WHERE
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
],
'Expected SQL on correlated realiased subquery'
);
my $queries = 0;
$schema->storage->debugcb(sub { $queries++; });
$schema->storage->debug(1);
is_deeply (
{ map
{ $_->cdid => {
track_titles => [ map { $_->title } ($_->tracks->all) ],
siblings => $_->get_column ('sibling_count'),
} }
$c_rs->all
},
$cd_data,
'Proper information retrieved from correlated subquery'
);
is ($queries, 1, 'Only 1 query fired to retrieve everything');
$schema->storage->debug($orig_debug);
$schema->storage->debugcb(undef);
# now add an unbalanced select/as pair
$c_rs = $c_rs->search ({}, {
'+select' => $cdrs->search(
{ 'siblings.artist' => { -ident => 'me.artist' } },
{ alias => 'siblings', columns => [
{ first_year => { min => 'year' }},
{ last_year => { max => 'year' }},
]},
)->as_query,
'+as' => [qw/active_from active_to/],
});
is_same_sql_bind(
$c_rs->as_query,
'(
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
(SELECT COUNT( * )
FROM cd siblings
WHERE siblings.artist = me.artist
AND siblings.cdid != me.cdid
AND siblings.cdid != ?
AND me.artist != ?
),
(SELECT MIN( year ), MAX( year )
FROM cd siblings
WHERE siblings.artist = me.artist
AND me.artist != ?
),
tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
FROM cd me
LEFT JOIN track tracks
ON tracks.cd = me.cdid
WHERE me.artist != ?
ORDER BY tracks.cd
)',
[
# first subselect
[ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
=> 23414 ],
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
# second subselect
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
# outher WHERE
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
],
'Expected SQL on correlated realiased subquery'
);
done_testing;
|