Sequelize query with count in inner join

Question

I am trying to convert this query to sequelize query object what is the right wayto do it?

SELECT families.id, count('answers.familyId') FROM families LEFT JOIN 
answers on families.id = answers.familyId WHERE answers.isActive=1 AND
answers.answer=1 GROUP BY families.id HAVING COUNT('answers.familyId')>=6

Answer 1

Let's assume that Family is your families sequelize model and Answer is your answers sequelize model, and sequelize is your Sequelize instance

Family.findAll({
    attributes: ['*', sequelize.fn('COUNT', sequelize.col('Answers.familyId'))],
    include: [
        {
            model: Answer,
            attributes: [],
            where: {
                isActive: 1,
                answer: 1
            }
        }
    ],
    group: '"Family.id"',
    having: sequelize.where(sequelize.fn('COUNT', sequelize.col('Answers.familyId')), '>=', 6)
}).then((families) => {
    // result
});

Useful documentation links:

• sequelize.fn()
• sequelize.where()
• sequelize.col()

Answer 2

You need to use get() on the attribute: aliased count column

There are two important gotchas when reading the aggregates out:

• the count only shows up on results if you alias it with attributes as shown by Piotr at https://stackoverflow.com/a/42472696/895245 and as shown at How do I select a column using an alias attributes aliasing has the unexpected effect of requiring you to use .get().

• as mentioned at: How does group by works in sequelize? the count comes out as a string in PostgreSQL due to bigint shenanigans, and you need parseInt it

Here's a minimal runnable example where we have posts and users who can like posts, and we want to count how:

• how many likes each user has
• ignoring likes to post2
• considering only users that have 0 or 1 likes in total

The following small improvements are made over Piotr's code:

• you likely don't want attributes: ['*' because that selects all columns, and therefore generally includes columns that are neither aggregates nor grouped by, leading to indeterminate behavior in some DBMSs and errors in others. You should just specify the GROUP by column instead, in our case the column is name.
• using the slightly cleaner Op.lte rather than the literal '<='

Due to required: false, this first version does a LEFT OUTER JOIN + COUNT(column), see also: https://dba.stackexchange.com/questions/174694/how-to-get-a-group-where-the-count-is-zero

sqlite.js

const assert = require('assert');
const { DataTypes, Op, Sequelize } = require('sequelize');
const sequelize = new Sequelize('tmp', undefined, undefined, Object.assign({
  dialect: 'sqlite',
  storage: 'tmp.sqlite'
}));
;(async () => {
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
}, {});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
}, {});
User.belongsToMany(Post, {through: 'UserLikesPost'});
Post.belongsToMany(User, {through: 'UserLikesPost'});
await sequelize.sync({force: true});
const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})
const post0 = await Post.create({body: 'post0'})
const post1 = await Post.create({body: 'post1'})
const post2 = await Post.create({body: 'post2'})
// Set likes for each user.
await user0.addPosts([post0, post1])
await user1.addPosts([post0, post2])

let rows = await User.findAll({
  attributes: [
    'name',
    [sequelize.fn('COUNT', sequelize.col('Posts.id')), 'count'],
  ],
  include: [
    {
      model: Post,
      attributes: [],
      required: false,
      through: {attributes: []},
      where: { id: { [Op.ne]: post2.id }},
    },
  ],
  group: ['User.name'],
  order: [[sequelize.col('count'), 'DESC']],
  having: sequelize.where(sequelize.fn('COUNT', sequelize.col('Posts.id')), Op.lte, 1)
})
assert.strictEqual(rows[0].name, 'user1')
assert.strictEqual(parseInt(rows[0].get('count'), 10), 1)
assert.strictEqual(rows[1].name, 'user2')
assert.strictEqual(parseInt(rows[1].get('count'), 10), 0)
assert.strictEqual(rows.length, 2)
})().finally(() => { return sequelize.close() });

with:

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 Node v14.17.0.

If we wanted the INNER JOIN version excluding 0 counts, we could just remove the required: false, which makes it be the default true. We can also use do a slightly simpler COUNT(*) since there will be no NULLs now:

let rows = await User.findAll({
  attributes: [
    'name',
    [sequelize.fn('COUNT', '*'), 'count'],
  ],
  include: [
    {
      model: Post,
      attributes: [],
      through: {attributes: []},
      where: { id: { [Op.ne]: post2.id }},
    },
  ],
  group: ['User.name'],
  order: [[sequelize.col('count'), 'DESC']],
  having: sequelize.where(sequelize.fn('COUNT', '*'), Op.lte, 1)
})
assert.strictEqual(rows[0].name, 'user1')
assert.strictEqual(parseInt(rows[0].get('count'), 10), 1)
assert.strictEqual(rows.length, 1)

PostgreSQL support has been broken for several years due to column X must appear in the GROUP BY clause or be used in an aggregate function

The above code should work for PostgreSQL too, but as mentioned at:

• https://github.com/sequelize/sequelize/issues/3256
• https://github.com/sequelize/sequelize/issues/5481#issuecomment-964387232

there's a bug and it doesn't. The fact that such glaring bugs have persisted for several years make me doubt if I should really be using this ORM.

The workaround is to use both:

  raw: true,
  includeIgnoreAttributes: false,

Full working example with the workaround:

#!/usr/bin/env node
const assert = require('assert');
const { DataTypes, Op, Sequelize } = require('sequelize');
const sequelize = new Sequelize('tmp', undefined, undefined, Object.assign({
  dialect: 'postgres',
  host: '/var/run/postgresql',
}));
;(async () => {
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
}, {});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
}, {});
User.belongsToMany(Post, {through: 'UserLikesPost'});
Post.belongsToMany(User, {through: 'UserLikesPost'});
await sequelize.sync({force: true});
const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})
const post0 = await Post.create({body: 'post0'})
const post1 = await Post.create({body: 'post1'})
const post2 = await Post.create({body: 'post2'})
// Set likes for each user.
await user0.addPosts([post0, post1])
await user1.addPosts([post0, post2])

let rows = await User.findAll({
  attributes: [
    'name',
    [sequelize.fn('COUNT', '*'), 'count'],
  ],
  raw: true,
  includeIgnoreAttributes: false,
  include: [
    {
      model: Post,
      where: { id: { [Op.ne]: post2.id }},
    },
  ],
  group: ['User.name'],
  order: [[sequelize.col('count'), 'DESC']],
  having: sequelize.where(sequelize.fn('COUNT', '*'), Op.lte, 1)
})
assert.strictEqual(rows[0].name, 'user1')
assert.strictEqual(parseInt(rows[0].count, 10), 1)
assert.strictEqual(rows.length, 1)
})().finally(() => { return sequelize.close() });

tested on PostgreSQL 13.4, Ubuntu 21.10.

Related

Counting associated entries with Sequelize