sequelize: findAndCountAll does not work with distinct, count is right but rows is wrong

Issue Description

What are you doing?


// model

  class Delta extends Sequelize.Model {}
  Delta.init({
    id: {
      type: Sequelize.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },

    padId: Sequelize.STRING,

    authorId: Sequelize.STRING,

    // delta: Sequelize.JSONB,
    delta: Sequelize.STRING(10000),
    deltaId: Sequelize.INTEGER
  }, { sequelize, modelName: 'delta' })

// query

let perPage = 25
let offset = 0

const result = await Delta.findAndCountAll({
  limit: perPage,
  offset,
  order: [
    ['padId', 'ASC']
  ],
  distinct: true,
  col: 'delta.padId'
})

What do you expect to happen?

It should count the distinct padIds and result.rows should be an array containing all the padId’s

What is actually happening?

The count is working, the select just selects all the parameters instead of just the distinct padIds

Executing (default): SELECT count(DISTINCT("delta"."padId")) AS "count" FROM "delta" AS "delta";
Executing (default): SELECT "id", "padId", "authorId", "delta", "deltaId", "createdAt", "updatedAt" FROM "delta" AS "delta" ORDER BY "delta"."padId" ASC LIMIT 25 OFFSET 0;

Additional context

Add any other context or screenshots about the feature request here.

Environment

  • Sequelize version: sequelize@5.19.0
  • Node.js version: v10.16.3
  • Operating System: Ubuntu 19.04 amd64
  • If TypeScript related: TypeScript version: none

Issue Template Checklist

How does this problem relate to dialects?

  • I think this problem happens regardless of the dialect.
  • I think this problem happens only for the following dialect(s):
  • I don’t know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

Would you be willing to resolve this issue by submitting a Pull Request?

  • Yes, I have the time and I know how to start.
  • Yes, I have the time but I don’t know how to start, I would need guidance.
  • No, I don’t have the time, although I believe I could do it if I had the time…
  • No, I don’t have the time and I wouldn’t even know how to start.

About this issue

  • Original URL
  • State: open
  • Created 5 years ago
  • Reactions: 5
  • Comments: 15 (1 by maintainers)

Most upvoted comments

Any update on that?

Still an issue. Version 5.21.5

Same for me, Help

Still an issue. Version 6.6.2

This what worked for me:

attributes: [[fn('DISTINCT', col('myColumn')), 'myColumn']],
col: 'myColumn',
distinct: true

Seems like attributes property is for the SELECT and the col/distinct properties are for the COUNT