use Test::More;
use Test::Deep;
use lib qw(t/lib);
-use DBICTest;
-use DBIC::SqlMakerTest;
+use DBICTest ':DiffSQL';
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) ],
+ track_titles => [ sort $_->tracks->get_column('title')->all ],
},
} ( $cdrs->all ) };
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
(SELECT COUNT( * )
FROM cd siblings
- WHERE siblings.artist = me.artist
+ WHERE me.artist != ?
+ AND 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
[
# subselect
- [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
- => 23414 ],
-
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
+ [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
+ => 23414 ],
+
# 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);
-
-cmp_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);
+$schema->is_executed_querycount( sub {
+ cmp_deeply (
+ { map
+ { $_->cdid => {
+ track_titles => [ sort map { $_->title } ($_->tracks->all) ],
+ siblings => $_->get_column ('sibling_count'),
+ } }
+ $c_rs->all
+ },
+ $cd_data,
+ 'Proper information retrieved from correlated subquery'
+ );
+}, 1, 'Only 1 query fired to retrieve everything');
# now add an unbalanced select/as pair
$c_rs = $c_rs->search ({}, {
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
(SELECT COUNT( * )
FROM cd siblings
- WHERE siblings.artist = me.artist
+ WHERE me.artist != ?
+ AND 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 != ?
+ WHERE me.artist != ?
+ AND siblings.artist = me.artist
),
tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
FROM cd me
[
# first subselect
- [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
- => 23414 ],
-
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
+ [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' }
+ => 23414 ],
+
# second subselect
[ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
=> 2 ],
]},
});
+ # we are using cds_unordered explicitly above - do the sorting manually
+ my @results = sort { $a->{artistid} <=> $b->{artistid} } @{$final_query->all_hri};
+ @$_ = sort { $a->{cdid} <=> $b->{cdid} } @$_ for map { $_->{cds} } @results;
+
is_deeply (
- $final_query->all_hri,
+ \@results,
[
{ artistid => 1, charfield => undef, max_competition_release => 1998, name => "Caterwauler McCrae", rank => 13, cds => [
- { artist => 1, cdid => 3, genreid => undef, single_track => undef, title => "Caterwaulin' Blues", year => 1997 },
- { artist => 1, cdid => 2, genreid => undef, single_track => undef, title => "Forkful of bees", year => 2001 },
{ artist => 1, cdid => 1, genreid => 1, single_track => undef, title => "Spoonful of bees", year => 1999 },
+ { artist => 1, cdid => 2, genreid => undef, single_track => undef, title => "Forkful of bees", year => 2001 },
+ { artist => 1, cdid => 3, genreid => undef, single_track => undef, title => "Caterwaulin' Blues", year => 1997 },
] },
{ artistid => 2, charfield => undef, max_competition_release => 1997, name => "Random Boy Band", rank => 13, cds => [
{ artist => 2, cdid => 4, genreid => undef, single_track => undef, title => "Generic Manufactured Singles", year => 2001 },