从 DBIx::Class 中的多个表中选择
我有以下 DBIx::Class 代码:
my $where = 'me.loginid = ? AND me.code = ?';
my @bind = ( $loginID, $code );
my $tip_signals = $bom_schema->resultset('Table1')->search_literal(
$where, @bind,
{
join => 'table2',
group_by => [ 'me.id' ],
'+select' => [ {'count' => '*'}, 'table2.id' ],
'+as' => [ 'Count', 'ID' ],
});
以下 SQL 语句是根据上述 DBIx::Class 代码生成的:
SELECT me.id, me.loginid, me.tipster_date, me.code, me.short_code_without_payout, COUNT( * ), table2.id
FROM table1 me LEFT JOIN table2 table2 ON table2.tip_signals_id = me.id
WHERE (
me.loginid = 'yccheok' AND me.code = 'ALIBABA_CODE'
)
GROUP BY me.id
现在,我希望从 4 个表中获取结果。 我手动编写自己的 SQL 代码:
SELECT me.id, me.loginid, me.tipster_date, me.code, me.short_code_without_payout, COUNT( * ), table2.id
FROM table1 me, table2, referrers, affiliates
WHERE (
me.loginid = 'yccheok' AND me.code = 'ALIBABA_CODE'
and table2.tip_signals_id = me.id
and referrers.affiliates_id = affiliates.id
and affiliates.loginid = me.loginid
and referrers.loginid = table2.loginid
)
GROUP BY me.id
我尝试将上述 SQL 语句转换为 DBIx::Class,如下所示:
my $where = 'me.loginid = ? AND me.code = ? AND table2.tip_signals_id = me.id AND referrers.affiliates_id = affiliates.id AND affiliates.loginid = me.loginid AND referrers.loginid = table2.loginid';
my @bind = ( $loginID, $code );
my $tip_signals = $bom_schema->resultset('Table1')->search_literal(
$where, @bind,
{
from => [ {table2=>'table2'}, {referrers=>'referrers'}, {affiliates=>'affiliates'} ],
group_by => [ 'me.id' ],
'+select' => [ {'count' => '*'}, 'table2.id' ],
'+as' => [ 'Count', 'ID' ],
});
当我尝试对结果集执行 next 时,出现异常“Not an ARRAY reference” 。 请注意,我不允许在 DBIx::Class 中使用 join,因为 referrers 和 affiliates 表与 *table 没有关系*** 表。
如何获得与 SQL 多选表等效的 DBIx::Class 代码?
I have the following DBIx::Class code :
my $where = 'me.loginid = ? AND me.code = ?';
my @bind = ( $loginID, $code );
my $tip_signals = $bom_schema->resultset('Table1')->search_literal(
$where, @bind,
{
join => 'table2',
group_by => [ 'me.id' ],
'+select' => [ {'count' => '*'}, 'table2.id' ],
'+as' => [ 'Count', 'ID' ],
});
The following SQL statement was generated based on the above DBIx::Class code:
SELECT me.id, me.loginid, me.tipster_date, me.code, me.short_code_without_payout, COUNT( * ), table2.id
FROM table1 me LEFT JOIN table2 table2 ON table2.tip_signals_id = me.id
WHERE (
me.loginid = 'yccheok' AND me.code = 'ALIBABA_CODE'
)
GROUP BY me.id
Now, I wish to get result from 4 tables. I write my own SQL code manually :
SELECT me.id, me.loginid, me.tipster_date, me.code, me.short_code_without_payout, COUNT( * ), table2.id
FROM table1 me, table2, referrers, affiliates
WHERE (
me.loginid = 'yccheok' AND me.code = 'ALIBABA_CODE'
and table2.tip_signals_id = me.id
and referrers.affiliates_id = affiliates.id
and affiliates.loginid = me.loginid
and referrers.loginid = table2.loginid
)
GROUP BY me.id
I try to translate above SQL statement to DBIx::Class as follows:
my $where = 'me.loginid = ? AND me.code = ? AND table2.tip_signals_id = me.id AND referrers.affiliates_id = affiliates.id AND affiliates.loginid = me.loginid AND referrers.loginid = table2.loginid';
my @bind = ( $loginID, $code );
my $tip_signals = $bom_schema->resultset('Table1')->search_literal(
$where, @bind,
{
from => [ {table2=>'table2'}, {referrers=>'referrers'}, {affiliates=>'affiliates'} ],
group_by => [ 'me.id' ],
'+select' => [ {'count' => '*'}, 'table2.id' ],
'+as' => [ 'Count', 'ID' ],
});
I get exception "Not an ARRAY reference" while I try to perform next on resultset. Please note that, I was not allowed to use join in DBIx::Class, as referrers and affiliates tables, has no relationship with *table*** tables.
How can I have DBIx::Class code equivalent to SQL multiple selected table?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果 table1/2 和引用者/附属机构之间没有关系,那么为什么不在 DBIx::Class 结果类中创建它们呢?
您没有粘贴这些,所以我在这里猜测,您想要将类似的内容添加到您的 Table1 类中:
在 Table2 中:
修复这些类名以匹配您的实际类。
我猜测您使用 Schema::Loader 来加载表,但它没有为您创建这些表,因为您的数据库没有设置适当的外键? DBIx::Class 并不关心,您可以很高兴地在其中建立更多关系,这些关系不是实际的 FK,并且它将创建您需要的联接。
HTH。
If you don't have relationships between table1/2 and referrers/affiliates, then why not create them in your DBIx::Class result classes?
You didn't paste those, so I'm making a guess here, you want to add something like this to your Table1 class:
And in Table2:
Fix up those class names to match your actual classes.
I'll make a guess that you used the Schema::Loader to load your tables, and it didn't create these for you as your database doesn't have the appropriate foreign keys set up? DBIx::Class doesn't care, you can quite happily set up more relationships in it, that aren't actual FKs, and it will create the joins you need for you.
HTH.
我可以简单地创建一个视图来映射那些 WHERE 和 FROM 语句,而不是在 DBIx 中手动编码复杂的查询。 然后,只需使用单行 DBIx :
Instead of hand coded complicated query in DBIx, I can simply create a view to map up those WHERE and FROM statement. Then, by just using single line of DBIx :