node.js - How to use CASE WHEN expression in Sequelize?

Question

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  |
+--------+----------+--------+--------+-------+

Answer 1

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