时间戳

首页 > 解决方案 > 如何获得包括模型 Sequelize 的总数？

postgresql - 如何获得包括模型 Sequelize 的总数？

问题描述

请告诉我如何获取嵌套表中所有元素的计数？为什么 variantCount 不正确？必须是 5，但我只得到 1。

我将非常感谢您的帮助

products = await Product.findAll({
        attributes: ['name', [Sequelize.fn('COUNT', 'variant.id'), 'variantCount']],
        include: [{ model: ProductVariant, as: 'variant', attributes: ['title', 'img'] }],
        group: ['product.id', 'variant.id'],
      })

输出：`

[
    {
        "name": "nike-metcon-7",
        "variantCount": "1",
        "variant": [
            {
                "title": "Nike Metcon 7 (Grey)",
                "img": "f7b6faf0-0522-4443-83c5-3ceeebc54453.jpg"
            },
            {
                "title": "Nike Metcon 7 (Blue)",
                "img": "e8d105a3-2704-4d08-97d7-7382fdb940b1.jpg"
            },
            {
                "title": "Nike Metcon 7 (White)",
                "img": "66c01ec6-ec09-4dec-be24-5164358440ed.jpg"
            },
            {
                "title": "Nike Metcon 7 (Grey)",
                "img": "b0c72280-e116-479c-a3c2-f323b462a861.jpg"
            },
            {
                "title": "Nike Metcon 7 (Red)",
                "img": "c93a50fe-14a5-4e0f-a5d1-014bc45dc3c9.jpg"
            }
        ]
    },
]

`

标签： postgresqlormsequelize.js

解决方案

尝试子查询确切的输出，这比从左连接表中提取计数要简洁

products = await Product.findAll({
        attributes: ['name', [Sequelize.literal('(select count(variant.id) from ProductVariant where product.variant_id = variant.variant_id)'), 'variantCount']],
        include: [{ model: ProductVariant, as: 'variant', attributes: ['title', 'img'] }],
        group: ['product.id', 'variant.id'],
      })

推荐阅读