Sequelize: findAll include model would include primary key attributes of the model, although not specified

[moatazelmasry2]

Hi al,

Consider this code:

Dashboard.findAll({attributes : ['id', 'owner'],
        order: [["createdAt" ,"DESC" ]],
        include : [{
          model : errorFiles,
          required : true,
          attributes : [
            [sequelize.fn('COUNT', sequelize.col('ErrorFiles.DashboardId')), 'errorFilesCount'],
        ]
        }],
        group : ['Dashboard.id']
      }).complete(next);

As you can see I specified one attribute from the model errorFiles, so I expect only this field joined to the result, but in reality sequelize would include the primary key attributes of the errorFiles table as well (i.e. ErrorFiles.filename and ErrorFiles.DashboardId)

Expected:

SELECT `Dashboard`.`id`, `Dashboard`.`owner`, COUNT(`ErrorFiles`.`DashboardId`) AS `ErrorFiles.errorFilesCount` FROM `Dashboards` AS `Dashboard` INNER JOIN `ErrorFiles` AS `ErrorFiles` ON `Dashboard`.`id` = `ErrorFiles`.`DashboardId` GROUP BY `Dashboard`.`id` ORDER BY `Dashboard`.`createdAt` DESC;

Actual:

SELECT `Dashboard`.`id`, `Dashboard`.`owner`, `ErrorFiles`.`filename` AS `ErrorFiles.filename`, `ErrorFiles`.`DashboardId` AS `ErrorFiles.DashboardId`, COUNT(`ErrorFiles`.`DashboardId`) AS `ErrorFiles.errorFilesCount` FROM `Dashboards` AS `Dashboard` INNER JOIN `ErrorFiles` AS `ErrorFiles` ON `Dashboard`.`id` = `ErrorFiles`.`DashboardId` GROUP BY `Dashboard`.`id` ORDER BY `Dashboard`.`createdAt` DESC;

Is this by intention?

Regards

[mickhansen]

Per 3.0.1 you can disable the included attributes with include.attributes = [], you can then do your aggregate on the main model.

[pensacola1989]

my sequelize version is "3.14",and when I try

return UserModel.findAll({
        attributes: ['Users.openid', [this.db.sequelize.fn('COUNT', 'Users.openid'), 'userRightCount']],
        include: [{
            as: 'Answers',
            model: OptionsModel,
            attributes: [],
            where: { is_Answer: 1 }
        }],
        group: ['Users.openid']
    });

sql generate:

SELECT "Users"."openid", COUNT(\'Users.openid\') AS "userRightCount", "Answers"."id" AS "Answers.id", "Answers"."Id" AS "Answers.Id", "Answers.user_answers"."id" AS "Answers.user_answers.id", "Answers.user_answers"."user_id" AS "Answers.user_answers.user_id", "Answers.user_answers"."question_id" AS "Answers.user_answers.question_id", "Answers.user_answers"."answer_id" AS "Answers.user_answers.answer_id", "Answers.user_answers"."createdAt" AS "Answers.user_answers.createdAt", "Answers.user_answers"."updatedAt" AS "Answers.user_answers.updatedAt" FROM "Users" AS "Users" INNER JOIN ("user_answers" AS "Answers.user_answers" INNER JOIN "QuestionOptions" AS "Answers" ON "Answers"."id" = "Answers.user_answers"."answer_id") ON "Users"."openid" = "Answers.user_answers"."user_id" AND "Answers"."is_Answer" = 1 GROUP BY "Users"."openid";

*_you see ,sub model's attributes still has included *_

[mickhansen]

attributes: [] will not remove them from the query completely at the moment since we might still need values for deduplication (in case of nested includes).

Since it looks like you're doing an aggregate you'll need to have have raw: true on your find call forn ow.