Sequelize - Get max date for a group of objects

user_1689262 0
Viewed 3871

I want to get the max (most recent) created at date of each group of objects from a table using sequelize.

My entity roughly follows this interface:

class MyEntity {
    id,
    groupName,
    createdAt
}

I want to do something like this:

await MyEntity.findAll({
      attributes: [[Sequelize.fn('max', Sequelize.col('created_at')), 'max']],
      group: ['group_name']
    })

I would interpret this as: 1. For all entites 2. Group by "groupName" 3. And get the max value for each group

However, I get the following error:

SequelizeDatabaseError: column "Template.created_at" must appear in the GROUP BY clause or be used in an aggregate function

I definitely do not want to group by the created_at column as well, as that would be meaningless.

The SQL for this operation seems pretty basic:

SELECT groupName, MAX(createdAt) FROM [MyEntity]
GROUP BY groupName
sequelize.js node.js javascript
2 Answers

The issue was caused because I had a default scope applied to the model, that was ordering the results on the created_at column. So, because created_at was not a attribute in my result set, I was unable to sort based on that column and got the error.

I had to unscope my finder prior to performing the aggregation:

await MyEntity.unscoped().findAll({
      attributes: [[Sequelize.fn('max', Sequelize.col('created_at')), 'max']],
      group: ['group_name']
    })
user_16892620

It might be late now. But it might help someone,

I got the same by on sequelize v5

myModel.findAll({
    attributes: [
        [Sequelize.fn('max', Sequelize.col('date')), 'max'],
        'name'
    ],
    group: ['name']
})
user_151932510
Related