Sequelize.js: Select rows by group

Overview

Sequelize is a powerful Object-Relational Mapping (ORM) framework for Node.js that allows developers to work with relational databases in an asynchronous, promise-based manner. It abstracts the database interaction and provides a high-level API for handling various database operations, including ‘group by’ functionality which is used for aggregating data. In this tutorial, we will explore how to select rows by group using Sequelize, taking into consideration different levels of complexity ranging from basic usage to more advanced topics such as custom attributes and having clauses.

Basics of Group By in Sequelize

To start with ‘group by’ in Sequelize, you first need to understand the role of the findAll method. This method can be used with the group option to group the results by a specific attribute. Let’s start with a simple query:

const { User } = require('./models');

async function groupUsersByCountry() {
  const usersByCountry = await User.findAll({
    attributes: ['country', [Sequelize.fn('COUNT', Sequelize.col('id')), 'userCount']],
    group: 'country'
  });
  console.log(usersByCountry);
}

groupUsersByCountry();

Advanced Group By Queries

Going a step further, Sequelize allows for more complex group by queries. For instance, let’s select users and the count of posts they have made in each category, assuming we have a Post model that is associated with the User model:

const { User, Post } = require('./models');

async function groupPostsByUserAndCategory() {
  const postCounts = await Post.findAll({
    attributes: ['userId', 'category', [Sequelize.fn('COUNT', Sequelize.col('postId')), 'postCount']],
    include: [{
      model: User,
      attributes: []
    }],
    group: ['userId', 'category'],
    raw: true
  });
  console.log(postCounts);
}

groupPostsByUserAndCategory();

Complex Aggregations and Having Clauses

For more sophisticated queries, Sequelize provides the options to use having clauses and aggregate functions. For example, you may want to group users by country and only retrieve groups having more than 10 users:

const { User } = require('./models');

async function groupUsersWithMinimum() {
  const usersByCountryWithMinimum = await User.findAll({
    attributes: ['country', [Sequelize.fn('COUNT', Sequelize.col('id')), 'userCount']],
    group: 'country',
    having: Sequelize.literal('COUNT(id) > 10')
  });
  console.log(usersByCountryWithMinimum);
}

groupUsersWithMinimum();

Using Subqueries and Aliases

Subqueries can further enhance the power of group by operations in Sequelize. They can be particularly useful when dealing with complex data relationships or when you need to select specific data that is not directly available through simple joins. Here’s an example using a subquery to count the number of active users by country:

// ... Other requires and setups

async function countActiveUsersByCountry() {
  const activeUsers = await User.findAll({
    attributes: [
      'country',
      [Sequelize.literal('(SELECT COUNT(*) FROM Users as activeUsers WHERE activeUsers.country = User.country AND activeUsers.isActive = true)'), 'activeUsersCount']
    ],
    group: ['country']
  });
  console.log(activeUsers);
}

countActiveUsersByCountry();

Conclusion

To conclude, the Sequelize ‘group by’ feature is a versatile tool in your ORM toolkit that enables you to perform complex aggregation queries. While starting with basic grouping operations can be straightforward, Sequelize also supports advanced scenarios including custom attributes, having clauses, subqueries, and aliased group attributes. As with any powerful tool, it comes with its nuances which must be understood to be used effectively. By stepping through the examples provided here, you should now have a solid foundation to build more complex ‘group by’ queries for your data analysis needs using Sequelize.