DBIx可能具有自定义条件，对象始终不设防

Question

更新我通常在stackhexchange的乳胶部分漫游，因此我们必须提供一个完整的最小示例来重现问题。

所以这里是一个完整的细分。我的问题的原始描述如下。

测试数据库设置，我们使用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');

通过加载到test.db

sqlite3 test.db < create.sql

从测试数据中可以看出，m1在address中有两个条目，而m2有一个条目。

接下来是DBIx设置(我不知道如何将其合并到一个文件中，这是一个受欢迎的想法，因为它会使测试更容易(。这些是通过dbicdump自动生成的，在这里我删除了所有的注释。

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;

我的测试脚本：

#!/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.

DBIC_TRACE给出

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'

如果您针对test.db手动运行它，则会产生

m1|Test 1|m1|home|home address|m1|work|work address

因此SQL能够产生正确的输出。但是访问者/对象——不管你想怎么称呼它们——一直是空的。我想知道为什么？

---

我最初的问题：

我的数据我有成员，他们每个人最多可以有两个地址(家庭和工作(都存储在同一个表中

所以我有一些类似的东西

Member; primary key(uid)
Address; unique(uid,address_type) # the latter is work or home

当我获取一个成员时，我想使用might_have关系预取最多两个地址。所以在Schema::Result::Member中我有

__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 },
);

我通过称之为

my $row = $self->schema->resultset('Member')
->find({uid => $uid},
{
prefetch => [qw/home_address work_address/],
});

据我从DBIC_TRACE中看到的，生成的SQL是正确的

... 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'

但CCD_ 14始终只是CCD_。

我也试过

__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 },
);

但是CCD_ 16部分从来不是CCD_。

你知道我在这里遗漏了什么吗？

Answer 1

DBIx:：class文档有一个自定义关系的示例，该关系在rel的远程端具有固定值：https://metacpan.org/pod/DBIx::Class::Relationship::Base#Custom-加入条件。

您遗漏的部分是-ident，因此DBIC可以区分固定值和相关列。因此，查询最终会得到一个绑定变量，该变量在执行时传递文本字符串"me.uid"。