Prevent Sequelize Join from returning primary key of include table

Question

I have a sequelize query which renders the correct SQL statement with the exception that it will return the primary key of the joined table. As this query is using Group By the additional field is unacceptable but I am not certain how to remove it.

userDB.tblUsers.belongsTo(userDB.tblSMSSent, { foreignKey: 'collector', targetKey: 'user' });
    userDB.tblSMSSent.hasMany(userDB.tblUsers);

    let users = await userDB.tblUsers.findAll({
        attributes: ['collector', 'tblUsers.id'],
        include: {
            model: userDB.tblSMSSent,
            as: 'tblSMSSent',
            attributes: [[sequelize.fn('COUNT', 'tblSMSSent.id'), 'numMessages']]
        },
        group: ['tblUsers.collector', 'tblUsers.id'],
        logging: console.log
    })

The SQL rendered is such:

SELECT 
    [tblUsers].[collector], 
    [tblUsers].[id], 
    [tblSMSSent].[id] AS [tblSMSSent.id], 
    COUNT(N'tblSMSSent.id') AS [tblSMSSent.numMessages] 
FROM [dbo].[tblUsers] AS [tblUsers] LEFT OUTER JOIN [dbo].[tblSMSSent] AS [tblSMSSent] ON [tblUsers].[collector] = [tblSMSSent].[user] 
GROUP BY [tblUsers].[collector], [tblUsers].[id];

I need this query without tblSMSSent.id included. I have tried using exclude: tblSMSSent.id as outlined in this article in the include attributes but that has not succeeded either. How am I able to correctly exclude this column from my query?

score 0 · Accepted Answer · answered Sep 02 '21 at 19:42

0

Added the raw:true tag in the JSON sent to sequelize. This forced only the inclusion of the required fields without primary keys.

answered Sep 02 '21 at 19:42

Geoff

353
3
19

Prevent Sequelize Join from returning primary key of include table

1 Answers1