Sequelize distinct in include throws syntax error at or near \"DISTINCT\"

Viewed 393

I am trying to filter out the duplicated row after performing an include

My code

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

Output

{
"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"
            }
        ]
    }
]
}

As you can see in the courseAvailable it returned 3 elements, And I wanted to do the distinction by distinct the element if the (section,start,end) are the same

so this is my expected output

{
"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"
            }
        ]
    }
]
}

(MY EXPECTATION) You can see that the id 509 is disappeared because the (section,start,end) have been queried by the distinct

What I've tried

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
        }
    ]
});

But it threw me an error => syntax error at or near \"DISTINCT\". I also tried using sequelize.literal to do the distinct as well but the error still the same

0 Answers
Related