Update I normally roam in the latex part of stachexchange and thus we have to provide a full minimal example to reproduce issues.
So here is a full breakdown. The original description of my problem is found below.
Test DB setup, we using SQLite, create.sql:
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS `member`;
DROP TABLE IF EXISTS `address`;
create table `member` (
`uid` VARCHAR(30) NOT NULL,
`name` VARCHAR(255) DEFAULT '',
CONSTRAINT `pk_uid` PRIMARY KEY(`uid`)
);
INSERT INTO `member` VALUES ('m1','Test 1'),('m2','Test 2');
create table `address` (
`uid` VARCHAR(30) NOT NULL,
`address_type` VARCHAR(30) NOT NULL, -- will either be work or home
`text` TEXT DEFAULT '',
CONSTRAINT `pk_uid_type` UNIQUE(`uid`,`address_type`)
CONSTRAINT `fk_uid`
FOREIGN KEY(uid)
REFERENCES member(uid)
ON DELETE CASCADE
);
INSERT INTO `address` VALUES
('m1','home','home address'),
('m1','work','work address'),
('m2','home','home address');
to be loaded into test.db via
sqlite3 test.db < create.sql
As we can see from the test data m1 has two entries in address whereas m2 has one.
Next the DBIx setup (I have no idea how to merge this into a single file, ideas a welcome as it would making the test easier). These are autogenerated via dbicdump, here I've removed alle the comments.
Schema.pm:
use utf8;
package Schema;
use strict;
use warnings;
use base 'DBIx::Class::Schema';
__PACKAGE__->load_namespaces;
1;
Schema/Result/Member.pm:
use utf8;
package Schema::Result::Member;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table("member");
__PACKAGE__->add_columns(
"uid",
{ data_type => "varchar", is_nullable => 0, size => 30 },
"name",
{ data_type => "varchar", default_value => "", is_nullable => 1, size => 255 },
);
__PACKAGE__->set_primary_key("uid");
__PACKAGE__->has_many(
"addresses",
"Schema::Result::Address",
{ "foreign.uid" => "self.uid" },
{ cascade_copy => 0, cascade_delete => 0 },
);
# I added
__PACKAGE__->might_have(
"home_address" => "Schema::Result::Address",
#{ 'foreign.uid' => 'self.uid'},
sub {
my $args = shift;
return {
"$args->{foreign_alias}.uid" => "$args->{self_alias}.uid",
"$args->{foreign_alias}.address_type" => 'home',
}
},
{ cascade_copy => 0, cascade_delete => 0 },
);
__PACKAGE__->might_have(
"home_address_alt" => "Schema::Result::Address",
{ 'foreign.uid' => 'self.uid'},
{ cascade_copy => 0, cascade_delete => 0 },
);
__PACKAGE__->might_have(
"work_address" => "Schema::Result::Address",
sub {
my $args = shift;
return {
"$args->{foreign_alias}.uid" => "$args->{self_alias}.uid",
"$args->{foreign_alias}.address_type" => 'work',
}
},
{ cascade_copy => 0, cascade_delete => 0 },
);
1;
Schema/Result/Address.pm:
use utf8;
package Schema::Result::Address;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table("address");
__PACKAGE__->add_columns(
"uid",
{ data_type => "varchar", is_foreign_key => 1, is_nullable => 0, size => 30 },
"address_type",
{ data_type => "varchar", is_nullable => 0, size => 30 },
"text",
{ data_type => "text", default_value => "", is_nullable => 1 },
);
__PACKAGE__->add_unique_constraint("uid_address_type_unique", ["uid", "address_type"]);
__PACKAGE__->belongs_to(
"u",
"Schema::Result::Member",
{ uid => "uid" },
{ is_deferrable => 0, on_delete => "CASCADE", on_update => "NO ACTION" },
);
1;
My test script:
#!/usr/bin/perl
use strict;
use warnings;
use utf8;
use open qw/:std :utf8/;
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
$Data::Dumper::Maxdepth = 0;
use Modern::Perl;
use lib qw(.);
use Schema;
BEGIN {
$ENV{DBIC_TRACE} = 1;
}
my $schema = Schema->connect(
'dbi:SQLite:dbname=test.db',
'',
'',
{
on_connect_do => 'PRAGMA foreign_keys = ON',
sqlite_unicode => 1,
RaiseError => 1,
}
);
my $row = $schema->resultset('Member')->find({ uid => 'm1'},
{
prefetch => ['home_address','work_address'],
}
);
# these are both undef
print Dumper $row->home_address;
print Dumper $row->work_address;
# using
$row = $schema->resultset('Member')->find({ uid => 'm1'},
{
prefetch => ['home_address','work_address'],
result_class => 'DBIx::Class::ResultClass::HashRefInflator',
}
);
# then
print Dumper $row;
# gives
# $VAR1 = {
# 'home_address' => undef,
# 'name' => 'Test 1',
# 'uid' => 'm1',
# 'work_address' => undef
# };
# using the "normal might_have home_address_alt in Member on m2
$row = $schema->resultset('Member')->find({ uid => 'm2'},
{
prefetch => ['home_address_alt'],
result_class => 'DBIx::Class::ResultClass::HashRefInflator',
}
);
say Dumper $row;
# does work, but only because m2 only have a single entry in Address whereas m1 has two
$row = $schema->resultset('Member')->find({ uid => 'm1'},
{
prefetch => ['home_address_alt'],
result_class => 'DBIx::Class::ResultClass::HashRefInflator',
}
);
say Dumper $row;
# which gives this warning: DBIx::Class::Storage::DBI::select_single(): Query returned more than one row. SQL that returns multiple rows is DEPRECATED for ->find and ->single and returns the first found.
The DBIC_TRACE gives
SELECT me.uid, me.name, home_address.uid, home_address.address_type, home_address.text, work_address.uid, work_address.address_type, work_address.text FROM member me LEFT JOIN address home_address ON ( home_address.address_type = ? AND home_address.uid = ? ) LEFT JOIN address work_address ON ( work_address.address_type = ? AND work_address.uid = ? ) WHERE ( me.uid = ? ): 'home', 'me.uid', 'work', 'me.uid', 'm1'
Which if you run it manually against test.db gives
m1|Test 1|m1|home|home address|m1|work|work address
So the SQL is capable of producing the correct output. But the accessors/objects whatever you want to call them, keeps being empty. I'd like to know why?
My original question:
I my data I have members and they can each have up to two addresses (home and work) both stored in the same table
So I have something similar to
Member; primary key(uid)
Address; unique(uid,address_type) # the latter is work or home
when I grab a member I'd like to prefetch the up to two addresses using might_have relationships. So in Schema::Result::Member I have
__PACKAGE__->might_have(
"home_address" => "Schema::Result::Address",
sub {
my $args = shift;
return {
"$args->{foreign_alias}.uid" => "$args->{self_alias}.uid",
"$args->{foreign_alias}.address_type" => 'home',
}
},
{ cascade_copy => 0, cascade_delete => 0 },
);
__PACKAGE__->might_have(
"work_address" => "Schema::Result::Address",
sub {
my $args = shift;
return {
"$args->{foreign_alias}.uid" => "$args->{self_alias}.uid",
"$args->{foreign_alias}.address_type" => 'work',
}
},
{ cascade_copy => 0, cascade_delete => 0 },
);
And I call it via
my $row = $self->schema->resultset('Member')
->find({uid => $uid},
{
prefetch => [qw/home_address work_address/],
});
As far as I can see from DBIC_TRACE the generated SQL is correct
... LEFT JOIN address home_address ON ( home_address.address_type = ? AND home_address.uid = ? ) LEFT JOIN address work_address ON ( work_address.address_type = ? AND work_address.uid = ? ) WHERE ( me.uid = ? ): 'home', 'me.uid', 'work', 'me.uid', '120969'
but $row->home_address is always just undef and I do not understand why.
I have also tried
__PACKAGE__->might_have(
"home_address" => "Schema::Result::Address",
{ 'foreign.uid' => 'self.uid' },
{ where => { 'address_type' => 'home' } , cascade_copy => 0, cascade_delete => 0 },
);
__PACKAGE__->might_have(
"work_address" => "Schema::Result::Address",
{ 'foreign.uid' => 'self.uid' },
{ where => { 'address_type' => 'work' } , cascade_copy => 0, cascade_delete => 0 },
);
but the where part is never a part of the DBIC_TRACE.
Any ideas as to what I'm missing here?
The DBIx::Class docs have an example of a custom relationship with fixed values on the remote side of the rel: https://metacpan.org/pod/DBIx::Class::Relationship::Base#Custom-join-conditions.
The part you've missed is the -ident, so DBIC can distinguish between a fixed value and a related column.
Because of that the query ends up with a bind variable that is passed the literal string 'me.uid' on execution.