postgresql - Sequelize:返回单个 COUNT 条目而不是嵌套包含中的每一行
问题描述
我正在尝试从 Sequelize 中的嵌套包含中获取总行数(计数)。我在这里关注了无数问题,但似乎无法正确解决。
楷模:
- 牌
- 品牌简介(别名
brand_profile
:) - BrandPageView(别名
brand_page_view
)
关系:
// Brand
Brand.hasOne(models.BrandProfile, {
foreignKey: 'brand_id',
as: 'brand_profile'
})
// BrandProfile
BrandProfile.belongsTo(models.Brand, {
foreignKey: 'brand_id',
})
BrandProfile.hasMany(models.BrandPageView, {
foreignKey: 'brand_id',
as: 'brand_page_views'
})
// BrandPageView
BrandProfile.belongsTo(models.BrandProfile, {
foreignKey: 'brand_id',
})
现在,当我尝试像这样正常运行我的查询时:
const { count, rows } = await Brand.findAndCountAll({
include: [
{
model: BrandProfile,
as: 'brand_profile',
include: [
{
model: BrandPageView,
as: 'brand_page_views',
},
],
},
],
})
它返回以下内容:
{
id: 1,
created_at: '2020-12-26T20:42:19.930Z',
updated_at: '2020-12-29T20:46:58.918Z',
deleted_at: null,
name: 'Test brand',
slug: 'test-brand',
status: 'disabled',
brand_profile: {
created_at: '2020-12-26T20:42:19.931Z',
about: [ [Object], [Object], [Object], [Object] ],
cleaned_about: null,
subtitle: 'subtitle test',
photo: '1609477287152.jpeg',
photo_config: { scale: '1.00' },
id: 1,
updated_at: '2021-01-01T05:01:27.414Z',
brand_id: 1,
brand_page_views: [
[Object], [Object],
[Object], [Object],
[Object], [Object],
[Object]
]
},
}
如您所见,brand.brand_profile.brand_page_views
具有所有对象的列表。现在我只想返回计数,所以我使用以下查询:
const { count, rows } = await Brand.findAndCountAll({
include: [
{
model: BrandProfile,
as: 'brand_profile',
include: {
model: BrandPageView,
as: 'brand_page_views',
attributes: [],
},
},
],
attributes: {
include: [
[
Sequelize.fn('COUNT', Sequelize.col('brand_profile.brand_page_views.brand_id')),
'brand_page_views_count',
],
],
},
group: ['brand.id']
})
我收到此错误:
'missing FROM-clause entry for table "brand"'
它输出的 SQL 是:
SELECT "Brand"."id", "Brand"."created_at", "Brand"."updated_at", "Brand"."deleted_at", "Brand"."name", "Brand"."slug", "Brand"."status", COUNT("brand_profile->brand_page_views"."brand_id") AS "brand_profile.brand_page_views.count", "brand_profile"."created_at" AS "brand_profile.created_at", "brand_profile"."about" AS "brand_profile.about", "brand_profile"."cleaned_about" AS "brand_profile.cleaned_about", "brand_profile"."subtitle" AS "brand_profile.subtitle", "brand_profile"."photo" AS "brand_profile.photo", "brand_profile"."email" AS "brand_profile.email", "brand_profile"."photo_config" AS "brand_profile.photo_config", "brand_profile"."id" AS "brand_profile.id", "brand_profile"."updated_at" AS "brand_profile.updated_at", "brand_profile"."brand_id" AS "brand_profile.brand_id" FROM "brands" AS "Brand" LEFT OUTER JOIN "brand_profile" AS "brand_profile" ON "Br2021-01-05 01:32:26 [sequelize] INFO Executing (default): SELECT "Brand"."id", "Brand"."created_at", "Brand"."updated_at", "Brand"."deleted_at", "Brand"."name", "Brand"."slug", "Brand"."status", COUNT("brand_profile->brand_page_views"."brand_id") AS "brand_profile.brand_page_views.count", "brand_profile"."created_at" AS "brand_profile.created_at", "brand_profile"."about" AS "brand_profile.about", "brand_profile"."cleaned_about" AS "brand_profile.cleaned_about", "brand_profile"."subtitle" AS "brand_profile.subtitle", "brand_profile"."photo" AS "brand_profile.photo", "brand_profile"."email" AS "brand_profile.email", "brand_profile"."photo_config" AS "brand_profile.photo_config", "brand_profile"."id" AS "brand_profile.id", "brand_profile"."updated_at" AS "brand_profile.updated_at", "brand_profile"."brand_id" AS "brand_profile.brand_id" FROM "brands" AS "Brand" LEFT OUTER JOIN "brand_profile" AS "brand_profile" ON "Brand"."id" = "brand_profile"."brand_id" LEFT OUTER JOIN "brand_page_views" AS "brand_profile->braand"."id" = "brand_profile"."brand_id" LEFT OUTER JOIN "brand_page_views" AS "brand_profile->brand_page_views" ON "brand_profile"."id" = "brand_profile->brand_page_views"."brand_id" WHERE "Brand"."id" = 1 GROUP BY "brand"."id";
nd_page_views" ON "brand_profile"."id" = "brand_profile->brand_page_views"."brand_id" WHERE "Brand"."id" = 1 GROUP BY "brand"."id";
老实说,我已经尝试了很多方法,添加到brand_profile->brand_page_views.brand_id
,但无济于事。brand_profile.brand_id
group
解决方案
Sequelize 并非旨在支持各种 SQL 聚合。因此,在您的情况下,您应该使用sequelize.literal
子查询来计算子记录:
attributes: {
include: [
[Sequelize.literal('(select COUNT(*) from brand_page_views where brand_page_views.brand_id=brand_profile.id)'),
'brand_page_views_count'],
],
},
不要忘记删除group: ['brand.id']
推荐阅读
- reactjs - 无法在 reactJS Typescript 中获取输入值
- linux - iTop Combodo 中的 OCS 库存集成
- mysql - Mysql left join 耗时太长
- reactjs - 如何避免 useEffect 中的无限循环?
- objective-c - 两条线段之间的距离
- c# - 如何擦除所有 Xamarin.Forms.Application.Current.Properties?
- javascript - 获取请求之间的竞争
- excel - 从 Excel VBA 调用 Bloomberg BQL 查询
- git - Git 无法为特定任务创建分支
- .net - SempahoreSlim 作为异步代码中的锁的正确用法是什么?