sequelize: Columns of join table are always selected, which breaks aggregation in PostgresSQL

Found a Fix!

By changing the query to add {includeIgnoreAttributes: false}, I now get the correct result:

query =
  includeIgnoreAttributes: false
  include: [
    {model: UserModel, as: "users", where: {id: user.id}}
  ]
  group: "users.id"

LeadModel.min("LastModifiedDate", query)

SQL generated

SELECT min("LastModifiedDate") AS "min"
  FROM "Leads" AS "Lead"
  INNER JOIN ("user_leads" AS "users.user_lead"
    INNER JOIN "Users" AS "users"
    ON "users"."id" = "users.user_lead"."UserId")
  ON "Lead"."id" = "users.user_lead"."LeadId" AND "users"."id" = 'sadlkjfsaljsl1234'
  GROUP BY users.id;

Result

2017-03-14T00:28:11.000Z

Hope this helps someone else! I found the clue to (the undocumented feature) includeIgnoreAttributes in #6711, grepped the source and found it was being set to false in Model.count.

EDIT: includeIgnoreAttributes: false makes attributes: [] superfluous, but if you do need any attributes from included Models, include them explicitly in the attributes array.

Keep it open.

I can confirm the flag of includeIgnoreAttributes: false works.

If you have a User table and Team table both linked with a belongsToMany and thus a join table. Doing…

teams = await currentUser.getTeams({raw: true, includeIgnoreAttributes: false});

will return you the flat array of teams that belong to that user.

Easy as pie… however, its not documented!

this problem still stands there in Sequelize V4.2.1 #5590

Having the same problem as @idrisadetunmbi

had to spend some time to get here. +1 for keeping this issue open, v4.42

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

Can also confirm this for postgres on a belongsTo relationship, so definitely not just for through attributes

Same problem, got saved by the above workaround of includeIgnoreAttributes: false. I too believe this workaround should be documented until the issue is resolved! Between root attributes, include attributes and through attributes (plus trying out exclude magic) on top of group/aggregate it was a pain to debug and search a correct workaround.

It’s 2021, i use Version 6.5.0 and this is still an issue

This is still an issue for me, however I have found a workaround if you are using a Model definition for your join table.

Using: sequelize@5.21.7

The following is a stripped down example of what I have (using Typescript):

const User = sequelize.define('User', {
  active: sequelize.Sequelize.BOOLEAN
});

const Tenancy = sequelize.define('Tenancy', {
  name: sequelize.Sequelize.STRING
});

const TenancyUser = sequelize.define('TenancyUser', {
  isAdmin: sequelize.Sequelize.BOOLEAN
})

User.belongsToMany(Tenancy, through: { model: TenancyUser })
Tenancy.belongsToMany(User, through: { model: TenancyUser })

TenancyUser.belongsTo(User)
TenancyUser.belongsTo(Tenancy)

// Find how many active users for a Tenancy.id
const id = 'someid'
TenancyUser.findOne({
  attributes: [
    [
      sequelize.Sequelize.fn(
        'count',
        sequelize.Sequelize.col('User.id')
      ),
      'activeUsers'
    ]
  ],
  include: [
    {
      model: Models.Tenancy,
      where: {
        id: id
      },
      attributes: ['id'],
    },
    {
      model: Models.User,
      where: {
        active: true
      },
      attributes: [],
      required: false
    }
  ],
  group: [
    'Tenancy.id'
  ],
  // bypasses loading this into a Model and gives you a raw row
  // if you don't use this, `activeUsers` is not attached to the model
  raw: true
}) as unknown as {
  // activeUsers is returned as a string NOT a number
  activeUsers: string;
  'Tenancy.id': string;
}

This ends up generating the following SQL:

SELECT
    count("User"."id") AS "activeUsers"
    "Tenancy"."id" AS "Tenancy.id"
FROM
    "TenancyUsers" AS "TenancyUsers"
    INNER JOIN "Tenancies" AS "Tenancy" ON "TenancyUsers"."TenancyId" = "Tenancy"."id"
        AND("Tenancy"."id" = 'someid')
    LEFT OUTER JOIN "Users" AS "User" ON "TenancyUsers"."UserId" = "User"."id"
    AND("User"."active" = TRUE)
GROUP BY
    "Tenancy"."id"
LIMIT 1;

Which gives me a single row only with ‘Tenancy.id’ and ‘activeUsers’.

I found this previously merged PR which appeared to fix it in the event you use raw:true https://github.com/sequelize/sequelize/pull/4029

However, at a guess, the test is bad because it didn’t define the joining table to have a primary column in the definition. =/

Thanks @papb, It’s really heartwarming to see this level of responsibility.

Maybe auto-closing inactive issues should be disabled, sometimes an issue is not active because no one is fixing it and people have nothing to add, not just because it’s fixed.

Hi @sassanh, I have this concern on my radar, hopefully in the near future I will be reopening all these wrongfully closed issues!

same issue here, sequelize 4

Any solutions so far? the problem is a big pain in Postgres too

ditto for mysql