CODEWITHSUNDEEP
node.jssequelize.js
Mrugesh
Mrugesh

Reputation: 4517

sequelize group by with multiple fields results into sequelize error

My query is as shown below :

 db.test.findAll({
            group: ['source'],
            attributes: ['source','source_id', [Sequelize.fn('COUNT', 'source'), 'count']],
            order: [[Sequelize.literal('count'), 'DESC']],
            raw: true
        }).then((sources) => {
            console.log('source is ' + sources);
        }).catch((err) => {
            console.log('error i is ' + err);
        })

test.js

module.exports = function (sequelize, DataTypes) {
    const test = sequelize.define('test', {
        id: {primaryKey: true, type: DataTypes.INTEGER, autoIncrement: true, allowNull: false, unique: true},
        source: {type: DataTypes.STRING, allowNull: false},
        source_id: {type: DataTypes.STRING, allowNull: false},

    });

    return test
};

As soon as I run this query , it gives me this error :

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.test.source_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Upvotes: 2

Views: 20138

Answers (2)

Imthiyaz Muhammed
Imthiyaz Muhammed

Reputation: 31

This issue will be resolved when you add required fields to the 'group' clause. but there comes a problem, your data will be grouped based on all those fields.

in my case I need to return every fields in the row but group them with a field named 'feed_id' - my intention was to get all feed_id but to bring a feed_id only one time.

one hack I found here is that SQL is not asking for a field which is an aggregated columns

Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'feed.Insight.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So the hack is to make an aggregated column with your field ie: you have make sequelize fn to generate the field which is there already in your row

attributes: [
    // 'id',
    'feed_id',
    [Sequelize.fn('MAX', Sequelize.col('id')), 'id'],
  ],
  group: ['feed_id'],

here I am grouping only feed_id but MAX function will return the latest id which is not grouped

Upvotes: 0

Rahul Sharma
Rahul Sharma

Reputation: 1431

Try to use source_id in the group by clause also. because here SQL get's confuse which source_id to pick from multiple. You have the groupBy source so it will pick but souce_id is still ambiguous

 db.test.findAll({
                group: ['source', 'source_id'],
                attributes: ['source','source_id', [Sequelize.fn('COUNT', 'source'), 'count']],
                order: [[Sequelize.literal('count'), 'DESC']],
                raw: true
            }).then((sources) => {
                console.log('source is ' + sources);
            }).catch((err) => {
                console.log('error i is ' + err);
            })

Upvotes: 8

Related Questions