Sorting by multiple columns in Sequelize.js

Overview

In Sequelize.js, sorting results by one or more columns is a common requirement when querying relational databases. This tutorial provides an in-depth look at how to achieve multi-column sorting using Sequelize, an ORM (Object-Relational Mapping) library for Node.js. We’ll start with basic examples, gradually progressing to more complex scenarios. By the end, you’ll be able to sort your queries efficiently using multiple columns, a feature that is essential for building flexible, user-friendly interfaces.

Prerequisites

• Basic understanding of Node.js and Sequelize.
• Sequelize installed and configured in your Node.js project.
• A defined Sequelize model to work with.

Basic Sorting by a Single Column

Before diving into multi-column sorting, let’s start with sorting by a single column which forms the foundation of our later examples. To sort query results by a single column, Sequelize utilizes the order option in the findAll() method:

const users = await User.findAll({
  order: [['lastName', 'ASC']]
});

This finds all users and sorts them in ascending order by their last name. The order option takes an array of arrays, where each sub-array specifies a column and direction (‘ASC’ for ascending or ‘DESC’ for descending).

Sorting by Multiple Columns

To sort by multiple columns, you simply extend the order array:

const users = await User.findAll({
  order: [
    ['lastName', 'ASC'],
    ['firstName', 'ASC']
  ]
});

This will sort users by their last name in ascending order. If two users have the same last name, the sort will fall back to their first name in ascending order.

Conditional Sorting

Sometimes you may want to apply sorting based on certain conditions. This can be achieved using the sequelize.literal() method to create custom sorting conditions:

const users = await User.findAll({
  order: [
    sequelize.literal('CASE WHEN role = 'admin' THEN 1 ELSE 2 END'),
    ['lastName', 'ASC']
  ]
});

This will sort admins first, then other users, and within those groups, it will sort by last name in ascending order.

Join Queries and Sorting Across Relationships

When working with related models, you may want to sort based on columns from a joined table. Sequelize allows you to include related models and specify sorting on their columns as well:

const orders = await Order.findAll({
  include: [
    {
      model: User,
      as: 'user'
    }
  ],
  order: [
    ['user', 'lastName', 'ASC'],
    ['createdAt', 'DESC']
  ]
});

This sorts orders by the last name of the associated user in ascending order and then by the order’s creation date in descending order where ‘User’ is a related model included in the query.

Advanced Usage: Custom Model Methods for Sorting

For more complex scenarios, you may want to encapsulate sorting logic within model methods. This makes your codebase cleaner and more maintainable:

User.sortByTitleAndName = function() {
  return this.findAll({
    order: [
      ['title', 'ASC'],
      ['lastName', 'ASC'],
      ['firstName', 'ASC']
    ]
  });
};
const sortedUsers = await User.sortByTitleAndName();

This defines a custom method that sorts users by title, then by last name, and then by first name.

Conclusion

Sequelize provides a robust and flexible way to handle sorting by multiple columns in your queries. Starting with basic single-column sorting, you can easily extend to multi-column and conditional sorting, as well as sorting across relationships with joined tables. By encapsulating complex sorting logic into custom model methods, your code stays organized and scalable. With this guide, you can now implement advanced sorting functionality in your Sequelize-based applications, enhancing their performance and user experience.