Exclude id column of the associated table

[preraksola]

What you are doing?

I have two models associated with each other as :
process.hasOne(process_measure, { foreignKey: 'process_id' });

My process model is defined as:

module.exports = function (sequelize, DataTypes) {
    return sequelize.define('zo_process', {
	id: {
	    type: DataTypes.BIGINT,
	    allowNull: false,
	    primaryKey: true
	},
	pid: {
	    type: DataTypes.INTEGER,
	    allowNull: false
	},
	command: {
	    type: DataTypes.TEXT,
	    allowNull: true
	}
    }, {
	tableName: 'zo_process',
	timestamps: false,
	freezeTableName: true,
    });
};

My process_measure model is defined as:

module.exports = function (sequelize, DataTypes) {
    return sequelize.define('zo_process_measure', {
	id: {
	    type: DataTypes.BIGINT,
	    allowNull: false,
	    primaryKey: true
	},
	process_id: {
	    type: DataTypes.BIGINT,
	    allowNull: false,
	    references: {
	  	model: 'zo_process',
		key: 'id'
	    }
	},
	cpuLoadPercent: {
	    type: DataTypes.FLOAT,
	    allowNull: false
	}
    }, {
	tableName: 'zo_process_measure',
	timestamps: false,
	freezeTableName: true,
    });
};

I want all the process details from the process table along with the AVG of cpuLoadPercent for each of them. So, I did the following query:

process.findAndCountAll({
    include: [{
	model: process_measure,
	attributes: [[Sequelize.fn('AVG', Sequelize.col('cpuLoadPercent')), 'cpuUsage']],
	group: "process_id"
    }],
    attributes: ["command"],
    group: [
        "zo_process.id",
	"zo_process.command",
    ]
}).then(function (result) {
    console.log(result);
});

What do you expect to happen?

I am expecting result with the details of processes along with the AVGof cpuLoadPercent from process_measuretable. The SQL I expect to be generated is:

SELECT [zo_process].[id], 
       [zo_process].[command], 
       AVG([cpuLoadPercent]) AS [zo_process_measure.cpuUsage] 
FROM [zo_process] AS [zo_process] LEFT OUTER JOIN [zo_process_measure] AS [zo_process_measure] 
    ON [zo_process].[id] = [zo_process_measure].[process_id] 
GROUP BY [zo_process].[id], [zo_process].[command];

What is actually happening?

I am forced to add all the other columns except cpuLoadPercent in the GROUP BY clause for aggregate function to work, which I understand is correct. But in the SQL generated, the column [zo_process_measure].[id] is also added, which spoils the results. As zo_process_measure.id is unique for each row in process_measure table, the GROUP BY does not work as intended. The generated SQL is:

SELECT [zo_process].[id], 
       [zo_process].[command], 
       [zo_process_measure].[id] AS [zo_process_measure.id], 
       AVG([cpuLoadPercent]) AS [zo_process_measure.cpuUsage] 
FROM [zo_process] AS [zo_process] LEFT OUTER JOIN [zo_process_measure] AS [zo_process_measure] 
    ON [zo_process].[id] = [zo_process_measure].[process_id] 
GROUP BY [zo_process].[id], [zo_process].[command], [zo_process_measure].[id]

So is there a way I can exclude/prevent the zo_process_measure.id to be added in the generated SQL?

Dialect: mssql
Database version: SQL Server 2016
Sequelize version: 3.30.2

[stale]

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 🙂