javascript - How does group by works in sequelize? - Stack Overflow

admin管理员组

文章数量:1135147

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

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

• javascript
• sql
• orm
• sequelize.js
• query-builder

Share Improve this question Follow edited Apr 29, 2020 at 9:24 Akash Kumar Verma 3,30822 gold badges1818 silver badges3333 bronze badges asked Mar 25, 2014 at 6:48 user964287user964287 77311 gold badge77 silver badges1313 bronze badges 1

• 1 although it can group it cannot execute the above. I am using a raw query now – user964287 Commented Apr 8, 2014 at 4:14

Add a comment  | 

10 Answers 10

Sorted by: Reset to default 83

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

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

i use [email protected]

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) { });

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'],
})

Try this -

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

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.

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) => { })

**Try this*

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

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

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'))

The GROUP BY clause is used to group rows that have the same values into summary rows like "total" or "average".
When you use GROUP BY, it combines rows into groups and then aggregate functions like COUNT, SUM, AVG, MAX, or MIN can be applied to each group. and you give reference to field to be grouped as

group: ['field']

本文标签： javascriptHow does group by works in sequelizeStack Overflow