Using Sequelize ORM for Node.js, how do you use CASE/WHEN expression in a select statement?
I do not see any reference or examples to use the SQL expression CASE according to Sequelize Documentation . Is this possible?
Here's my example:
SQL
SELECT userId, Status,
MAX(CASE Type WHEN 'Employee' THEN Rate ELSE 0 END) AS "Employee",
MAX(CASE Type WHEN 'School' THEN Rate ELSE 0 END) AS "School",
MAX(CASE Type WHEN 'Public' THEN Rate ELSE 0 END) AS "Public",
MAX(CASE Type WHEN 'Other' THEN Rate ELSE 0 END) AS "Other"
FROM Database.dbo.invoice
WHERE Status IN ('Included', 'Excluded')
GROUP BY userId, Status
Sequelize
var query = {
attributes: ['userId'], // need to include Rate (as Employee, School, Public, Other for each CASE/WHEN)
/*
MAX(CASE Type WHEN 'Employee' THEN Rate ELSE 0 END) AS "Employee",
MAX(CASE Type WHEN 'School' THEN Rate ELSE 0 END) AS "School",
MAX(CASE Type WHEN 'Public' THEN Rate ELSE 0 END) AS "Public",
MAX(CASE Type WHEN 'Other' THEN Rate ELSE 0 END) AS "Other"
*/
where: {
user: userId,
Type: ['Employee', 'School', 'Public', 'Other'],
},
group: ['userId'],
raw: true
};
models.invoice.findAll(query).then(result => {
console.log(result);
});
Database Structure
+--------+------+----------+
| userId | Rate | Type |
+--------+------+----------+
| 1 | 2.00 | Employee |
+--------+------+----------+
| 1 | 3.50 | School |
+--------+------+----------+
| 1 | 4.00 | Public |
+--------+------+----------+
| 1 | 2.50 | Other |
+--------+------+----------+
| 2 | 3.75 | Employee |
+--------+------+----------+
| 2 | 4.25 | School |
+--------+------+----------+
| 2 | 2.00 | Public |
+--------+------+----------+
| 2 | 3.00 | Other |
+--------+------+----------+
Desired Result:
+--------+----------+--------+--------+-------+
| userId | Employee | School | Public | Other |
+--------+----------+--------+--------+-------+
| 1 | 2.00 | 3.50 | 4.00 | 2.50 |
+--------+----------+--------+--------+-------+
| 2 | 3.75 | 4.25 | 2.00 | 3.00 |
+--------+----------+--------+--------+-------+
You can use Sequelize.literal in attributes
to build special queries
var query = {
attributes: [
'userId',
[Sequelize.literal(`MAX(CASE Type WHEN 'Employee' THEN Rate ELSE 0 END)`), 'Employee'],
[Sequelize.literal(`MAX(CASE Type WHEN 'School' THEN Rate ELSE 0 END)`), 'School'],
[Sequelize.literal(`MAX(CASE Type WHEN 'Public' THEN Rate ELSE 0 END)`), 'Public'],
[Sequelize.literal(`MAX(CASE Type WHEN 'Other' THEN Rate ELSE 0 END)`), 'Other'],
],
where: {
user: userId,
Type: ['Employee', 'School', 'Public', 'Other'],
},
group: ['userId'],
raw: true
};
models.invoice.findAll(query).then(result => {
console.log(result);
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With