Sequelize.js: Select the Latest Record of Each Group

Introduction

Retrieving the most recent record from each group in a database is a common and meaningful task when dealing with aggregated data. In Sequelize.js, which is a prominent ORM for Node.js, achieving this requires understanding how to structure complex queries using its API. This is especially true when leveraging SQL’s window functions or aggregation in conjunction with group by statements is necessary. In this article, we’ll dive into how to select the latest record of each group using Sequelize.js.

Finding the Subquery Approach

We begin by utilizing a subquery that finds the maximum value of the date column – or any other timestamp column ensuring distinctness – per each group. This helps in identifying the most recent records. Next, we use that subquery in the where condition whilst performing a join with the base table to zero in on the desired records.

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

const latestRecords = await Model.findAll({
    where: {
        // Subquery starts
        createdAt: {
            [Op.eq]: Sequelize.literal(`(
                SELECT MAX(createdAt)
                FROM tableName AS alias
                WHERE alias.groupId = Model.groupId
            )`)
        }
        // Subquery ends
    }
});

Using Raw Queries to Specify More Complex Logic

Under situations demanding more sophisticated groupings or associations, you might resort to using a raw query. Sequelize provides the method sequelize.query, allowing you to execute plain SQL where ORMs constructed calls might fall short.

const latestRecordsRaw = await sequelize.query(
    `SELECT *
    FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY groupId ORDER BY createdAt DESC) AS rn
        FROM tableName
    ) AS sub
    WHERE sub.rn = 1`,
    { type: Sequelize.QueryTypes.SELECT }
);

Employing Sequelize’s Literal and Where Clauses

The Sequelize.literal function can combine custom SQL statements with the model’s query-building tools. Coupled with Sequelize’s where construct, it can filter down to records that match the latest date per group.

const latestGroupedRecords = await Model.findAll({
    attributes: [
        'id',
        'groupId',
        'data', // Additional model attributes...
        [Sequelize.literal(`(
            SELECT MAX(createdAt)
            FROM tableName
            WHERE groupId = Model.groupId
        )`), 'latestDate']
    ],
    where: Sequelize.literal(`createdAt = latestDate`)
});

Complete Example

Let’s bundle these approaches into a full example where a record of books that holds a column named ‘publicationDate’ indicating when each book was released. Our objective is to find the latest published book in each category:

const Book = sequelize.define('book', {
    title: Sequelize.STRING,
    categoryId: Sequelize.INTEGER,
    publicationDate: Sequelize.DATE
});

async function findLatestBooksPerCategory() {
    return await Book.findAll({
        attributes: [
            'title',
            'categoryId',
            [Sequelize.literal(`(
                SELECT MAX(publicationDate)
                FROM books AS book
                WHERE book.categoryId = Book.categoryId
            )`), 'latestPublicationDate']
        ],
        group: ['categoryId'],
        having: Sequelize.literal(`publicationDate = latestPublicationDate`)
    });
}

// Using this function, call it when needed:
findLatestBooksPerCategory().then(books => console.log(books));

Conclusion

In summary, Sequelize.js requires a mix of SQL knowledge and understanding of its API to efficiently perform tasks like selecting the latest record of each group. As seen, we can achieve this through a combination of subqueries, literal types, and careful model association. The approach you select will vary based on the specifics of your data and the complexity of your relationships. With practice, Sequelize is a powerful tool that can express complex queries in a more maintainable and readable manner than raw SQL.