javascript - How to include number of related rows in sequelize

Question

I have two models, a Session model and a Register model. The Register model has a foreign key to the Session model.

In Sequelize I have the relation described as:

Session.hasMany(Register, {
  foreignKey: {
    allowNull: false,
  },
});
Register.belongsTo(Session);

I want to query all the Sessions and also include the the number of Register objects that are related to the Session in a key called num_registers.

For example,

[{
    id: 4,
    name: 'a session',
    num_registers: 43
}]

EDIT: Say that the Register model also has a finished value that is a boolean, how would I do the same thing above but also filter only Register objects that belong to the session with a finished value of false.

Answer 1

I suppose you need to use a subquery using sequelize.literal:

Session.findAll({
    attributes: {
        include: [
            [
                sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM Registers AS register
                    WHERE
                        register.sessionId = session.id
                        AND
                        register.finished = true
                )`),
                'num_registers'
            ]
        ]
    }
});

See Subqueries