DBIx :: Class中的子查询 [英] Subqueries in DBIx::Class
问题描述
我在此上花费了太多时间,但仍然无法使语法正常工作。
在DBIx :: Class中可以使用此选择语句吗?
I have spent too much time on this, and still cannot get the syntax to work. Is this select statement possible in DBIx::Class?
"SELECT A.id, A.name, count(C.a_id) AS count1,
(SELECT count(B.id FROM A, B WHERE A.id=B.a_id GROUP BY B.a_id, A.id) AS count2
FROM A LEFT OUTER JOIN C on A.id=C.a_id GROUP BY C.a_id, A.id"
下面的代码在DBIx :: Class中起作用,以获取表的计数'C',但是我多次努力添加表'B'的计数却多次失败:
This code below works in DBIx::Class to pull the count for table 'C', but multiple efforts of mine to add in the count for table 'B' have repeatedly failed:
my $data= $c->model('DB::Model')
->search({},
{
join => 'C',
join_type => 'LEFT_OUTER',
distinct => 1,
'select' => [ 'me.id','name',{ count => 'C.id', -as => 'count1'} ],
'as' => [qw/id name count1 /],
group_by => ['C.a_id','me.id'],
}
)
->all();
我试图在一个查询中获得两个计数,以便将结果保存在一个数据结构中。在另一个论坛上,建议我进行两个单独的搜索调用,然后合并结果。不过,当我查看DBIx :: Class文档时,它提到工会已被弃用。使用'literal'DBIx :: Class无效,因为它只能用作where子句。我不想使用视图(另一个建议),因为SQL最终将被扩展以匹配其中一个ID。如何格式化此查询以在DBIx :: Class中工作?谢谢。
I am trying to get two counts in one query so that the results are saved in one data structure. On another forum it was suggested that I make two separate search calls and then union the results. When I looked at the DBIx::Class documentation though, it mentioned that 'union' is being deprecated. Using the 'literal' DBIx::Class doesn't work because it's only meant to be used as a where clause. I do not want to use a view (another's suggestion) because the SQL will eventually be expanded to match upon one of the id's. How do I format this query to work in DBIx::Class? Thank you.
推荐答案
DBIx :: Class通过使用 as_query $ c非常方便地支持子查询。 $ c>子查询结果集上的方法。在其中有一些示例烹饪书。对于您的用例,它看起来像:
DBIx::Class supports subqueries pretty conveniently by using the as_query
method on your subquery resultset. There are some examples in the cookbook. For your use case, it'd look something like:
# your subquery goes in its own resultset object
my $subq_rs = $schema->resultset('A')->search(undef, {
join => 'B',
group_by => [qw/A.id B.id/],
})->count_rs;
# the subquery is attached to the parent query with ->as_query
my $combo_rs = $schema->resultset('A')->search(undef, {
select => [qw/ me.id me.name /,{ count => 'C.id' }, $subq_rs->as_query],
as => [qw/ id name c_count b_count/],
});
这篇关于DBIx :: Class中的子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!