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