Sequelize select with aggregate includes bad attributes => error

Question

The models are Person and Team with a M:1 relationship.

The query that fails:

db.Person.findAll({
    attributes: [ [sequelize.fn('COUNT', sequelize.col('*')), 'count']],
    include: [{model: db.Team, required: true}], // to force inner join
    group: ['team.team_id']
    }).complete(function(err, data) {
        ...
    });

The generated SQL is:

SELECT "person"."person_id", 
       COUNT(*) AS "count", 
       "team"."team_id" AS "team.team_id", 
       "team"."team_name" AS "team.team_name", 
       "team"."team_email" AS "team.team_email",
       "team"."team_lead" AS "team.team_lead" 
FROM "person" AS "person" INNER JOIN "team" AS "team" 
    ON "person"."team_id" = "team"."team_id" 
GROUP BY "team"."team_id";

Obviously, the person.person_id included in the SELECT clause, screws it up, with Postgres complaining correctly that:

ERROR:  column "person.person_id" must appear in the `GROUP BY` clause or be used in an aggregate function

It seems that the attributes option is taken into account since the COUNT appears correctly, but all the rest of the columns in the SELECT clause are added by default.

Is there another way (besides attributes) to explicitly define which columns appear in the SELECT clause or is this a bug?

I'm using Sequelize v2.0.3.

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

Sequelize will always add the primary key to the selected fields. Currently there is no way to disable that.

Perhaps adding DISTINCT ON as suggested here https://stackoverflow.com/a/19723716/800016 to person_id could fix the issue?

Otherwise, feel free to open an issue on the sequelize bug tracker

edited May 23 '17 at 11:59

Community

1
1

answered Feb 25 '15 at 08:41

Jan Aagaard Meier

28,078
8
95
66

Its been couple of years since this answer, Is it still true that "Sequelize will always add the primary key to the selected fields", and that there is no work around? – Rohit Gupta Nov 13 '17 at 08:49

Sequelize select with aggregate includes bad attributes => error

1 Answers1