express - Sequelize distinct in include 在 \"DISTINCT\" 处或附近引发语法错误

Question

我试图在执行包含后过滤掉重复的行

我的代码

const courses = await Course.findAll({
    subQuery: false,
    order: [ [ 'courseID', 'ASC' ] ],
    include: [
        {
            model: CourseAvailable,
            as: 'courseAvailable',
            required: false
        }
    ]
});

输出

{
"success": true,
"data": [
    {
        "id": 132,
        "courseID": "CSS112",
        "courseName": "COMPUTER PROGRAMMING",
        "required": null,
        "createdAt": "2020-05-21T07:29:58.596Z",
        "updatedAt": "2020-05-21T07:29:58.596Z",
        "courseAvailable": [
            {
                "id": 508,
                "courseID": "CSS112",
                "semester": 2,
                "credit": 3,
                "totalSeat": 55,
                "section": "1",
                "allowedGroup": "CSS 1 A(0)",
                "day": "อ.",
                "start": "08.30",
                "end": "10.30",
                "classroom": "SCL607",
                "createdAt": "2020-05-21T07:30:10.417Z",
                "updatedAt": "2020-05-21T07:30:10.417Z"
            },
            {
                "id": 509,
                "courseID": "CSS112",
                "semester": 2,
                "credit": 3,
                "totalSeat": 55,
                "section": "1",
                "allowedGroup": "OTHER (0)",
                "day": "อ.",
                "start": "08.30",
                "end": "10.30",
                "classroom": "SCL607",
                "createdAt": "2020-05-21T07:30:10.417Z",
                "updatedAt": "2020-05-21T07:30:10.417Z"
            },
            {
                "id": 510,
                "courseID": "CSS112",
                "semester": 2,
                "credit": 3,
                "totalSeat": 55,
                "section": "1",
                "allowedGroup": "OTHER (0)",
                "day": "อ.",
                "start": "10.30",
                "end": "12.30",
                "classroom": "SCL703",
                "createdAt": "2020-05-21T07:30:10.417Z",
                "updatedAt": "2020-05-21T07:30:10.417Z"
            }
        ]
    }
]
}

正如您在 courseAvailable 中看到的那样，它返回了 3 个元素，如果 (section,start,end) 相同，我想通过区分元素来区分

所以这是我的预期输出

{
"success": true,
"data": [
    {
        "id": 132,
        "courseID": "CSS112",
        "courseName": "COMPUTER PROGRAMMING",
        "required": null,
        "createdAt": "2020-05-21T07:29:58.596Z",
        "updatedAt": "2020-05-21T07:29:58.596Z",
        "courseAvailable": [
            {
                "id": 508,
                "courseID": "CSS112",
                "semester": 2,
                "credit": 3,
                "totalSeat": 55,
                "section": "1",
                "allowedGroup": "CSS 1 A(0)",
                "day": "อ.",
                "start": "08.30",
                "end": "10.30",
                "classroom": "SCL607",
                "createdAt": "2020-05-21T07:30:10.417Z",
                "updatedAt": "2020-05-21T07:30:10.417Z"
            },
            {
                "id": 510,
                "courseID": "CSS112",
                "semester": 2,
                "credit": 3,
                "totalSeat": 55,
                "section": "1",
                "allowedGroup": "OTHER (0)",
                "day": "อ.",
                "start": "10.30",
                "end": "12.30",
                "classroom": "SCL703",
                "createdAt": "2020-05-21T07:30:10.417Z",
                "updatedAt": "2020-05-21T07:30:10.417Z"
            }
        ]
    }
]
}

（我的期望）您可以看到 id 509 消失了，因为 (section,start,end) 已由 distinct 查询

我试过的

const courses = await Course.findAll({
    subQuery: false,
    order: [ [ 'courseID', 'ASC' ] ],
    include: [
        {
            model: CourseAvailable,
            as: 'courseAvailable',
            attributes: [ [ sequelize.fn('DISTINCT', sequelize.col('section')), 'section' ] ].concat(
                Object.keys(CourseAvailable.rawAttributes)
            ), // was trying to do distinct only one column for checking if the distinct working or not working at all, and I am not sure if this is the right way to do it also, after I've been researching it for a day but found nothings that will solves my problem
            required: false
        }
    ]
});

但它在 \"DISTINCT\" 处或附近引发了一个错误 => 语法错误。我也尝试使用 sequelize.literal 来做不同的事情，但错误仍然一样

标签： expresssequelize.js