sql - DBIC 分组查询没有这种关系
问题描述
我尝试使用以下 SQL 查询来计算按类型分组的所有产品:
select pt.id,
pt.name,
count(p.id) as n
from prd.product_types as pt,
prd.products as p
where pt.id = p.type
group by pt.id,
pt.name
order by pt.name
这两个表(prd.product_types和prd.products)由这两个 DBIC 类映射:
package ki::Schema::Result::ProductTypes;
use strict;
use warnings;
use utf8;
use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';
__PACKAGE__->load_components("InflateColumn::DateTime", "TimeStamp", "EncodedColumn");
__PACKAGE__->table("prd.product_types");
__PACKAGE__->add_columns(
"id",
{
data_type => "uuid",
default_value => \"uuid_generate_v4()",
is_nullable => 0,
size => 16,
},
"name",
{ data_type => "varchar", is_nullable => 0, size => 128 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint("uk_product_types", ["name"]);
__PACKAGE__->has_many(
"products",
"ki::Schema::Result::Products",
{ "foreign.type" => "self.id" },
{ cascade_copy => 0, cascade_delete => 0 },
);
__PACKAGE__->meta->make_immutable;
1;
和
package ki::Schema::Result::Products;
use strict;
use warnings;
use utf8;
use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';
__PACKAGE__->load_components("InflateColumn::DateTime", "TimeStamp", "EncodedColumn");
__PACKAGE__->table("prd.products");
__PACKAGE__->add_columns(
"id",
{
data_type => "uuid",
default_value => \"uuid_generate_v4()",
is_nullable => 0,
size => 16,
},
"type",
{ data_type => "uuid", is_foreign_key => 1, is_nullable => 0, size => 16 },
"name",
{ data_type => "varchar", is_nullable => 0, size => 128 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint("uk_products", ["name"]);
__PACKAGE__->belongs_to(
"type",
"ki::Schema::Result::ProductTypes",
{ id => "type" },
{ is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" },
);
__PACKAGE__->meta->make_immutable;
1;
当我在我的 Catalyst 控制器中设置加入时,我得到“没有这样的关系”:
DBIx::Class::ResultSource::_resolve_join(): No such relationship prd.products on ProductTypes
结果集是这样的:
$c->stash(product_types_rs => $c->model('DB_T::ProductTypes'));
连接看起来像这样:
$c->stash(categories => [$c->stash->{product_types_rs}->search({}, {
join => [qw/ prd.products /],
select => [ { count => 'prd.products.id' } ],
as => [qw/ n /],
group_by => [qw/ id /],
order_by => 'name ASC'
})]);
上面的查询是 DBIC 文档中查询 artist-cd 的一对一复制。但是有些东西我无法理解并且是错误的。
回来有一些消息:
到目前为止唯一可行的方法是:
$c->stash(categories => [$c->stash->{product_types_rs}->search({},
{
'+select' => [{ COUNT => 'products.id', -as => 'n'}],
join => ['products'],
group_by => 'me.id',
order_by => { -desc => ' COUNT( products.id )' },
})])
它的 SQL 如下所示:
SELECT me.id, me.name, COUNT( products.id ) AS n
FROM prd.product_types me LEFT JOIN prd.products products
ON products.type = me.id
GROUP BY me.id
ORDER BY COUNT( products.id ) DESC
我测试了生成的 SQL 查询,得到了想要的结果。还有一个问题。在我看来,我确实使用这个
[% FOREACH category IN categories -%]
<li>([% category.n %])</li>
[% END -%]
循环迭代结果集。但是即使 Dumper->Dump() 向我显示正确的数据, n的值也不会显示:
...
'_column_data' => {
'name' => "XTorckAlsa",
'n' => 1,
'id' => '88b94b12-4169-4964-9022-3eebbc5c05c5'
},
...
'_column_data' => {
'name' => "Pledicts11",
'n' => 5,
'id' => 'ebda7223-99e3-48d6-b662-c7d17d124787'
},
我仍然缺少一些东西。
解决方案
您命名了关系products
,因此这就是您需要在join
参数中使用的内容。表名永远不会在任何 DBIC 参数中使用,仅在 ResultSource 定义中使用。
您也永远不会在其中一个参数中拥有一个以上的点。
建议使用columns
而不是select
andas
因为它不太容易出现用户错误。
order_by
也支持非文字 SQL 语法,即
[{ -asc => 'columnname' }]
推荐阅读
- r - 用不同的变量替换重复项
- c - 如何在汇编中减去两个数字(确切地说是字符)?
- java - 索引返回错误值,即 1 而不是 0
- java - Dropwizard 拦截器响应状态
- javascript - 在javascript中的switch case中传递url参数
- python - 克隆和修改模块的github repo后如何导入python模块
- python - 如何使用命令来打破这个循环
- html - CSS SVG 过滤器:url(#element) 在 Firefox 中不起作用并且在 safari 中有奇怪的行为
- c# - 应用程序内的客户端-服务器安全通信
- python - 无法在 nano jetson 上安装 h5py