时间戳

首页 > 解决方案 > 如何在过滤元素时使sql查询显示每一行？

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

问题描述

我正在category_id使用 sequelize 作为 ORM 通过“”过滤我的建立表。我的问题是我正在按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"
                }
            }
            ],

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

标签： mysqlnode.jssequelize.js

解决方案

您需要定义两个不同的 tableestablishment和subcategory. 一个关联将用于过滤，另一个将用于加入establishment和subcategory。

您的关联应如下所示 -

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

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

您的 Sequuelize 语句应如下所示 -

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,
        }
    ]

});

希望能帮助到你！

推荐阅读