时间戳

首页 > 解决方案 > 包含 where 条件，但在包含之前保留所有结果

express - 包含 where 条件，但在包含之前保留所有结果

问题描述

我在查询包含时遇到问题

我所做的是

    const courses = await Course.findAll({
    subQuery: false,
    order: [ [ 'courseID', 'ASC' ] ],
    include: [
        {
            model: CourseAvailable,
            as: 'courseAvailable',
            where: {
                id: 350 // dummy condition only for asking here ( I had my purpose )
            }
        }
    ]
    });

和输出

{
"success": true,
"data": [
    {
        "id": 20,
        "courseID": "MTH102",
        "courseName": "MATHEMATICS II",
        "required": "{MTH101}",
        "createdAt": "2020-05-19T17:30:44.085Z",
        "updatedAt": "2020-05-19T17:30:44.085Z",
        "courseAvailable": [
            {
                "id": 350,
                "courseID": "MTH102",
                "semester": 2,
                "credit": 3,
                "totalSeat": 150,
                "section": "7",
                "allowedGroup": "EET 1 A(0)",
                "day": "จ.",
                "start": "10.30",
                "end": "12.30",
                "classroom": "CB1103",
                "createdAt": "2020-05-20T15:22:50.176Z",
                "updatedAt": "2020-05-20T15:22:50.176Z"
            }
        ]
    }
  ]
}

从输出中，我知道在包含中查询只会返回在包含中通过查询找到的课程（如您所见，返回的数据中只有 1 门课程）

但是我仍然想在包含中进行查询以使包含仅在满足我的条件时才显示（例如过滤包含）

但问题是我仍然想保留不符合任何条件的其他课程

预期产出

{
"success": true,
"data": [
    {
        "id": 19,
        "courseID": "MTH101",
        "courseName": "MATHEMATICS I",
        "required": null,
        "createdAt": "2020-05-19T17:30:44.085Z",
        "updatedAt": "2020-05-19T17:30:44.085Z",
        "courseAvailable": [] // showing empty array if none has met my query => still keep the data above
    },
    {
        "id": 20,
        "courseID": "MTH102",
        "courseName": "MATHEMATICS II",
        "required": "{MTH101}",
        "createdAt": "2020-05-19T17:30:44.085Z",
        "updatedAt": "2020-05-19T17:30:44.085Z",
        "courseAvailable": [
            {
                "id": 350,
                "courseID": "MTH102",
                "semester": 2,
                "credit": 3,
                "totalSeat": 150,
                "section": "7",
                "allowedGroup": "EET 1 A(0)",
                "day": "จ.",
                "start": "10.30",
                "end": "12.30",
                "classroom": "CB1103",
                "createdAt": "2020-05-20T15:22:50.176Z",
                "updatedAt": "2020-05-20T15:22:50.176Z"
            }
        ]
    }
  ]
}

标签： expresssequelize.js

解决方案

当在 include 中使用 where 时，Sequilize 将执行内部连接，如此处所述。您需要在required: false包含中设置属性才能执行左连接。将您的查询更改为

const courses = await Course.findAll({
    subQuery: false,
    order: [ [ 'courseID', 'ASC' ] ],
    include: [
        {
            model: CourseAvailable,
            as: 'courseAvailable',
            where: {
                id: 350 // dummy condition only for asking here ( I had my purpose )
            },
            required: false
        }
    ]
    });

推荐阅读