How to write a query using COUNT, GROUP BY, ORDER BY in sequelize

Question

I want to write a query in sequelize ORM using GROUP BY, ORDER BY, COUNT.

Here is my SQL query :

SELECT COUNT(1),
       bucket_name,
       clusterId
FROM aidc_tenoco.output_table
WHERE clusterId IS NOT NULL
GROUP BY clusterId
ORDER BY COUNT(1) DESC
LIMIT 0,
      1

I want to write in sequelize please help me out.

Answer 1

Code

const Op = require("sequelize").Op;
const sequelize = require("../models").sequelize;

models.OutputTable.findOne({
    where: {
      clusterId: {
        [Op.ne]: null,
      },
    },
    attributes: ["bucket_name", "clusterId", [sequelize.fn("COUNT", "1"), "CountedValue"]],
    group: ["clusterId"],
    order: [[sequelize.col("CountedValue"), "DESC"]],
});

Explanation

Use the docs - Querying It is pretty straight forward.

The findOne method limits your query to 1. The attribute clause selects the column_names. Know that sequelize won't identify your CountedValue column hence order it using sequelize.col(columnAlias).

Hope this helps.