Adding ORDER BY clause to Sequelize ORM query

To add an ORDER BY clause to the Sequelize query methods, you need to specify the order option inside the query methods you used for retrieving data from your database.

For example, suppose you have a MySQL table named Cities with the following data:

+----+------------+------------+
| id | cityName   | population |
+----+------------+------------+
|  1 | York       |     467000 |
|  2 | Bristol    |     467000 |
|  3 | Manchester |     210000 |
|  4 | London     |    8000000 |
+----+------------+------------+

Now you are tasked with retrieving the data from the table, ordering the result with the following rules:

• Sort by population column in descending order
• Sort by cityName column in ascending order

The equivalent SQL SELECT statement would be like this:

SELECT * FROM Cities ORDER BY `population` DESC, `cityName` ASC;

To transform the raw query above and retrieve the data using Sequelize ORM Model, you need to write the following JavaScript code:

Cities.findAll({
  order: [
    ["population", "DESC"],
    ["cityName", "ASC"],
  ],
}).then((cities) => {
  console.log("All cities:", JSON.stringify(cities, null, 2));
});

In the above example, the order option is added into the findAll() method as an object property containing an Array.

You need to specify the column name and the order of the sort as a single Array unit inside the order array.

The console output would look as follows:

All cities: [
  {
    "id": 4,
    "cityName": "London",
    "population": 8000000
  },
  {
    "id": 2,
    "cityName": "Bristol",
    "population": 467000
  },
  {
    "id": 1,
    "cityName": "York",
    "population": 467000
  },
  {
    "id": 3,
    "cityName": "Manchester",
    "population": 210000
  }
]

If you only need to order by one column, you still need to put the ordering array inside the order array as follows:

Cities.findAll({
  order: [["population", "DESC"]],
}).then((cities) => {
  console.log("All cities:", JSON.stringify(cities, null, 2));
});

The order option is available for all Sequelize data retrieval methods like findOne(), findAll(), and findOrCreate().

For more information, you can refer to Sequelize documentation for manipulating datasets with limit, offset, order and group.

And that’s how you add an order by clause to Sequelize ORM query 😉