mysql - 如何在过滤元素时使sql查询显示每一行？

Question

我正在使用 sequelize 作为 ORM 通过“category_id ”过滤我的建立表。我的 问题 是我通过 category_id = 11 过滤女巫产生类似的东西:

提出的查询:

WHERE  `subcategories`.`category_id` = 11;

给定的 JSON(仅包含一个子类别行)

"establishments": [
        {
            "id": 11,
            "name": "Tasco do Ti efe",
            "subcategories": [
                {
                    "category_id": 11,
                    "category_type": "fashion",
                    "SubCategoryEstablishements": {
                        "sub_category_id": 31,
                        "establishment_id": 11,
                        "createdAt": "2020-04-26 13:39:20",
                        "updatedAt": "2020-04-26 13:39:20"
                    }
                }
            ],
]

我希望能够找到合适的餐厅并拥有属于他的所有子类别，如下所示:

"establishments": [
        {
            "id": 1,
            "name": "Melhor efef",
            "subcategories": [
                {
                "category_id": 21,
                "category_type": "beautycara",
                "SubCategoryEstablishements": {
                    "sub_category_id": 41,
                    "establishment_id": 11,
                    "createdAt": "2020-04-26 13:39:20",
                    "updatedAt": "2020-04-26 13:39:20"
                }
            },
            {
                "category_id": 11,
                "category_type": "fashion",
                "SubCategoryEstablishements": {
                    "sub_category_id": 31,
                    "establishment_id": 11,
                    "createdAt": "2020-04-26 13:39:20",
                    "updatedAt": "2020-04-26 13:39:20"
                }
            },
            {
                "category_id": 31,
                "category_type": "homefamily",
                "SubCategoryEstablishements": {
                    "sub_category_id": 51,
                    "establishment_id": 11,
                    "createdAt": "2020-04-26 13:39:20",
                    "updatedAt": "2020-04-26 13:39:20"
                }
            }
            ],

我应该对我的查询进行哪些更改才能实现这一点？

Answer 1

您需要定义表 establishment 和 subcategory 的两个不同关联。一个关联将用于过滤，另一个将用于连接 establishment 和 subcategory 。

您的关联应如下 -

subcategory.belongsTo(models.establishment, { foreignKey: 'categoryId', as: 'establishment1' });

subcategory.belongsTo(models.establishment, { foreignKey: 'categoryId', as: 'establishment2' });

您的 Sequelize 语句应如下所示 -

const allEstablishments = establishment.findAll({

    include: [{
            model: establishment,
            as: 'establishment1',
            attributes: [],
            required: true,
            where: {
                [op.and]: [{
                    '$`subcategories`.`category_id`$ = 11'
                }]
            }
        },
        {
            model: establishment,
            as: 'establishment2',
            required: true,
        }
    ]

});

希望能帮助到你!