How does group by works in sequelize?

Question

I am looking for group by queries through Sequelize and cannot seem to find any documentation.

    SELECT column, count(column)  
    FROM table 
    GROUP BY column

Mullein

although it can group it cannot execute the above. I am using a raw query now – Mullein

Answer 1

issue: https://github.com/sequelize/sequelize/issues/348

User.findAll({
 group: ['field']
})

i use [email protected]

Antakiya

Answer 2

I think you looking for something like this:

 Table.findAll({
   attributes: ['column1', 
     sequelize.fn('count', sequelize.col('column2'))], 
   group: ["Table.column1"]
 }).success(function (result) { });

Update: Newer versions of Sequelize uses .then instead of .success.

 Table.findAll({
   attributes: ['column1', 
     sequelize.fn('count', sequelize.col('column2'))], 
   group: ["Table.column1"]
 }).then(function (result) { });

Marley

Wow, this is great. How did you know that? – Quinary

Newer versions of sequelize now use .then(function (result) { }); instead of .success(function (result) { }); – Confer

is there any documentation on this? – Ginnifer

@Ginnifer docs.sequelizejs.com/en/latest/docs/models-usage/… – Ormandy

@mparnisari sorry, I missed this part "Everything you see below can also be done for group" – Ginnifer

No worries. @Mullein this should be marked as the correct answer! – Ormandy

Awesome answer is awesome. I've been struggling a bit with sequelize's documentation. This was of much help. – Bellerophon

Answer 3

Group by with count sequelize ORM

'field' - custom user input, you can rename this string, it's your field(column) in database

'count' - reserved sequelize keyword string need for get count in sequelize

'cnt' - custom user input, you can rename this string, it's your output count

Sequelize version 3.25.0

User.findAll({
      attributes: ['field', [sequelize.fn('count', sequelize.col('field')), 'cnt']],
      group: ['field'],
})

Surrounding

Answer 4

Try this -

Table.count(
{
   attributes: ['column'], 
   group: 'column',
} 

Manufacturer

Answer 5

Example: how to add an convenient alias to the grouped function column.

I did this as a separate response mostly because it wouldn't format well in a comment... otherwise I would have just added it to Sampat's answer.

function getSumsBySomeId() {
  const criteria = {
    attributes: ['some_id', [sequelize.fn('sum', sequelize.col('some_count')), 'some_count_sum']],
    group: ['some_id'],
    raw: true
  };
  return Table.getAll(criteria);
}

YIELDS:

{ some_id: 42, some_count_sum: 100 },
{ some_id: 43, some_count_sum: 150 }
...
etc.

Mayes

Answer 6

Your code should look something like these using ES6 standard.

Table.findAll({ attributes: ['column1', sequelize.fn('count', sequelize.col('column2'))],   group: ["Table.column1"]  }).then( (result) => { })

Runnymede

Answer 7

**Try this*

Table.findAll({
     group: ['column']
})

Une

Meaningless SQL query – Hostetter

That is what's on their documentation, I wouldn't be on stackoverflow looking for answers if that example is useful enough. Right really useless. – Pearly

Answer 8

You need to use row.get('count') to get the count, row.count won't work

The API mentioned at in this answer is correct, but there were a few tricks I was missing in order to actually get the count results out.

As mentioned at: How do I select a column using an alias you need to use .get() for attribute aliased columns for some reason.

And another thing: you need to use parseInt to get an integer out of PostgreSQL count: Postgres sequelize raw query to get count returns string value due to bigint shenanigans.

Minimal runnable example also demonstrating ORDER BY, WHERE and HAVING:

main.js

#!/usr/bin/env node
// https://cirosantilli.com/sequelize-example
const assert = require('assert')
const { DataTypes, Op } = require('sequelize')
const common = require('./common')
const sequelize = common.sequelize(__filename, process.argv[2])
;(async () => {
const UserLikesPost = sequelize.define('UserLikesPost', {
  userId: {
    type: DataTypes.INTEGER,
  },
  postId: {
    type: DataTypes.INTEGER,
  },
}, {})
await UserLikesPost.sync({force: true})
await UserLikesPost.create({userId: 1, postId: 1})
await UserLikesPost.create({userId: 1, postId: 2})
await UserLikesPost.create({userId: 1, postId: 3})
await UserLikesPost.create({userId: 2, postId: 1})
await UserLikesPost.create({userId: 2, postId: 2})
await UserLikesPost.create({userId: 3, postId: 1})
await UserLikesPost.create({userId: 4, postId: 1})
// Count likes on all posts but:
// - don't consider likes userId 4
// - only return posts that have at least 2 likes
// Order posts by those with most likes first.
const postLikeCounts = await UserLikesPost.findAll({
  attributes: [
    'postId',
    [sequelize.fn('COUNT', '*'), 'count'],
  ],
  group: ['postId'],
  where: { userId: { [Op.ne]: 4 }},
  order: [[sequelize.col('count'), 'DESC']],
  having: sequelize.where(sequelize.fn('COUNT', '*'), Op.gte, 2)
})
assert.strictEqual(postLikeCounts[0].postId, 1)
assert.strictEqual(parseInt(postLikeCounts[0].get('count'), 10), 3)
assert.strictEqual(postLikeCounts[1].postId, 2)
assert.strictEqual(parseInt(postLikeCounts[1].get('count'), 10), 2)
assert.strictEqual(postLikeCounts.length, 2)
await sequelize.close()
})()

common.js

const path = require('path');

const { Sequelize } = require('sequelize');

function sequelize(filename, dialect, opts) {
  if (dialect === undefined) {
    dialect = 'l'
  }
  if (dialect === 'l') {
    return new Sequelize(Object.assign({
      dialect: 'sqlite',
      storage: path.parse(filename).name + '.sqlite'
    }, opts));
  } else if (dialect === 'p') {
    return new Sequelize('tmp', undefined, undefined, Object.assign({
      dialect: 'postgres',
      host: '/var/run/postgresql',
    }, opts));
  } else {
    throw new Error('Unknown dialect')
  }
}
exports.sequelize = sequelize

package.json:

{
  "name": "tmp",
  "private": true,
  "version": "1.0.0",
  "dependencies": {
    "pg": "8.5.1",
    "pg-hstore": "2.3.3",
    "sequelize": "6.5.1",
    "sqlite3": "5.0.2"
  }
}

and PostgreSQL 13.4 on Ubuntu 21.10. GitHub upstream.

Generated PostgreSQL query:

SELECT
  "postId",
  COUNT('*') AS "count"
FROM
  "UserLikesPosts" AS "UserLikesPost"
WHERE
  "UserLikesPost"."userId" != 4
GROUP BY
  "postId"
HAVING
  COUNT('*') >= 2
ORDER BY
  "count" DESC;

JOIN + GROUP BY + aggregate

See: Sequelize query with count in inner join

Debug

Answer 9

To create a query like

SELECT key, COUNT(ref) FROM MyModel GROUP BY key

You can do this as follows

  const results = await MyModel.findAll({
    attributes: ['key', [Sequelize.fn('COUNT', Sequelize.col('ref')), 'count']],
    group: ['key']
  });

Optionally you can cast the result to something like (MyModel & { count: number })[]

Finally, to extract the count for each row, you'll need to use the getDataValue function. e.g.

results.map(r => r.getDataValue('count'))

Cupid