through: {attributes: []} does not remove attributes

[jmwilkinson]

Setting through attributes to [] doesn't exclude the id columns from being selected.

The model query:

componentsPromise = models.Component.findAll({
    include: [{
        attributes: [],
        model: models.Platform,
        through: {
            attributes: []
        },
        as: 'platforms',
        where: {
            id: {
                in: ids
            }
        }
    }],
    group: ['component.id'],
    having: ['COUNT(?) = ?', 'component.id', ids.length]
})

Defining the association:

Component.belongsToMany(Platform, {
    through: 'platform_components',
    as: 'platforms',
    foreignKey: 'component_id',
    otherKey: 'platform_id'
});

Platform.belongsToMany(Component, {
    through: 'platform_components',
    as: 'components',
    foreignKey: 'platform_id',
    otherKey: 'component_id'
});

The result query:

SELECT
  "component"."id",
  "component"."component_name",
  "component"."component_type",
  "component"."created_at",
  "component"."updated_at",
  "platforms.platform_components"."platform_id"  AS "platforms.platform_components.platform_id",
  "platforms.platform_components"."component_id" AS "platforms.platform_components.component_id"
FROM "components" AS "component" INNER JOIN
  ("platform_components" AS "platforms.platform_components" INNER JOIN "platforms" AS "platforms"
      ON "platforms"."id" = "platforms.platform_components"."platform_id")
    ON "component"."id" = "platforms.platform_components"."component_id" AND "platforms"."id" IN ('1', '2', '3')
GROUP BY "component"."id", "platforms.platform_components.platform_id", "platforms.platform_components.component_id"
HAVING COUNT('component.id') = 3;

This is a major problem in postgres, as this query will fail because the group by must include the through table primary keys, which subsequently makes the query not function.

[mickhansen]

We need the id for deduplication.
Unfortunetaly we don't currently have a way of detecting that a thing is only used for aggregation.

[jmwilkinson]

This may be a reason to have a "join" parameter. It would run a join against a table without needing the data in those tables.

Something like:

models.User.findAll({
  join: [{
    model: models.Role
    where: {
      created_at: TODAY
    }
  }]
})

[mickhansen]

Wait, i just looked at your model query again, i don't see any include statements? And you have the through stament as a top level option.

Please include your actual code and i can see if there's any issues.

[jmwilkinson]

I just edited the formatting. My include was spaced in too far, making it look like it was on the same level as the through. What I have there is (was) the actual code.

[mickhansen]

Hmm with that code it shouldn't select anything, that's odd, should just work.

[jmwilkinson]

If you have a chance to verify this behaviour, perhaps we can mark this a bug and work on a fix.

[xizhao]

#5602 may be related; I can't seem to specify which attributes are selected on the through table. Instead it looks like an all-or-nothing op

[mickhansen]

I'll mark as a bug untill i can verify either way.

[ningshen]

+1.
I found If I comment if statement at:
https://github.com/sequelize/sequelize/blob/master/lib/model.js#L1390

It will work.

[mickhansen]

@ningshen That will great reduce deduplication performance since we can no longer match primary keys though.