use strict;
-use warnings;
+use warnings;
use Test::More;
+use Test::Exception;
+
use lib qw(t/lib);
-use DBICTest;
+use DBICTest ':DiffSQL';
my $schema = DBICTest->init_schema();
-plan tests => 20;
-
- {
- my $rs = $schema->resultset( 'CD' )->search(
- {
- 'producer.name' => 'blah',
- 'producer_2.name' => 'foo',
- },
- {
- 'join' => [
- { cd_to_producer => 'producer' },
- { cd_to_producer => 'producer' },
- ],
- 'prefetch' => [
- 'artist',
- { cd_to_producer => 'producer' },
- ],
- }
- );
-
- eval {
- my @rows = $rs->all();
- };
- is( $@, '' );
- }
-
+lives_ok (sub {
+ my $rs = $schema->resultset( 'CD' )->search(
+ {
+ 'producer.name' => 'blah',
+ 'producer_2.name' => 'foo',
+ },
+ {
+ 'join' => [
+ { cd_to_producer => 'producer' },
+ { cd_to_producer => 'producer' },
+ ],
+ 'prefetch' => [
+ 'artist',
+ { cd_to_producer => { producer => 'producer_to_cd' } },
+ ],
+ }
+ );
+
+ my @executed = $rs->all();
+
+ is_same_sql_bind (
+ $rs->as_query,
+ '(
+ SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
+ artist.artistid, artist.name, artist.rank, artist.charfield,
+ cd_to_producer.cd, cd_to_producer.producer, cd_to_producer.attribute,
+ producer.producerid, producer.name,
+ producer_to_cd.cd, producer_to_cd.producer, producer_to_cd.attribute
+ FROM cd me
+ LEFT JOIN cd_to_producer cd_to_producer
+ ON cd_to_producer.cd = me.cdid
+ LEFT JOIN producer producer
+ ON producer.producerid = cd_to_producer.producer
+ LEFT JOIN cd_to_producer producer_to_cd
+ ON producer_to_cd.producer = producer.producerid
+ LEFT JOIN cd_to_producer cd_to_producer_2
+ ON cd_to_producer_2.cd = me.cdid
+ LEFT JOIN producer producer_2
+ ON producer_2.producerid = cd_to_producer_2.producer
+ JOIN artist artist ON artist.artistid = me.artist
+ WHERE ( ( producer.name = ? AND producer_2.name = ? ) )
+ )',
+ [
+ [ { sqlt_datatype => 'varchar', dbic_colname => 'producer.name', sqlt_size => 100 }
+ => 'blah' ],
+ [ { sqlt_datatype => 'varchar', dbic_colname => 'producer_2.name', sqlt_size => 100 }
+ => 'foo' ],
+ ],
+ );
+
+}, 'Complex join parsed/executed properly');
my @rs1a_results = $schema->resultset("Artist")->search_related('cds', {title => 'Forkful of bees'}, {order_by => 'title'});
is($rs1a_results[0]->title, 'Forkful of bees', "bare field conditions okay after search related");
cmp_ok(scalar @cds, '==', 1, "condition based on inherited join okay");
my $rs3 = $rs2->search_related('cds');
-cmp_ok(scalar($rs3->all), '==', 45, "All cds for artist returned");
+cmp_ok(scalar($rs3->all), '==', 15, "All cds for artist returned");
-cmp_ok($rs3->count, '==', 45, "All cds for artist returned via count");
+cmp_ok($rs3->count, '==', 15, "All cds for artist returned via count");
-my $rs4 = $schema->resultset("CD")->search({ 'artist.artistid' => '1' }, { join => ['tracks', 'artist'], prefetch => 'artist' });
+my $rs4 = $schema->resultset("CD")->search({ 'artist.artistid' => '1' }, {
+ join => ['tracks', 'artist'], prefetch => 'artist', order_by => 'me.cdid'
+});
my @rs4_results = $rs4->all;
is($rs4_results[0]->cdid, 1, "correct artist returned");
is(scalar(@{$merge_rs_2->{attrs}->{join}}), 1, 'only one join kept when inherited');
my $merge_rs_2_cd = $merge_rs_2->next;
-eval {
+lives_ok (sub {
my @rs_with_prefetch = $schema->resultset('TreeLike')
->search(
prefetch => [ 'parent', { 'children' => 'parent' } ],
});
-};
+}, 'pathological prefetch ok');
-ok(!$@, "pathological prefetch ok");
+my $rs = $schema->resultset("Artist")->search({}, { join => 'twokeys' });
+my $second_search_rs = $rs->search({ 'cds_2.cdid' => '2' }, { join =>
+['cds', 'cds'] });
+is(scalar(@{$second_search_rs->{attrs}->{join}}), 3, 'both joins kept');
+ok($second_search_rs->next, 'query on double joined rel runs okay');
-1;
+# test joinmap pruner
+lives_ok ( sub {
+ my $rs = $schema->resultset('Artwork')->search (
+ {
+ },
+ {
+ distinct => 1,
+ join => [
+ { artwork_to_artist => 'artist' },
+ { cd => 'artist' },
+ ],
+ },
+ );
+
+ is_same_sql_bind (
+ $rs->count_rs->as_query,
+ '(
+ SELECT COUNT( * )
+ FROM (
+ SELECT me.cd_id
+ FROM cd_artwork me
+ JOIN cd cd ON cd.cdid = me.cd_id
+ JOIN artist artist_2 ON artist_2.artistid = cd.artist
+ GROUP BY me.cd_id
+ ) me
+ )',
+ [],
+ );
+
+ ok (defined $rs->count);
+});
+
+# make sure multiplying endpoints do not lose heir join-path
+lives_ok (sub {
+ my $rs = $schema->resultset('CD')->search (
+ { },
+ { join => { artwork => 'images' } },
+ )->get_column('cdid');
+
+ is_same_sql_bind (
+ $rs->as_query,
+ '(
+ SELECT me.cdid
+ FROM cd me
+ LEFT JOIN cd_artwork artwork
+ ON artwork.cd_id = me.cdid
+ LEFT JOIN images images
+ ON images.artwork_id = artwork.cd_id
+ )',
+ [],
+ );
+
+ # execution
+ $rs->next;
+});
+
+done_testing;