This is my code, I really don't know how to fix this issue anymore

when I enter this code.

     db.models.Price.findAll({
        attributes:['id', 'item.item_name'],
        group: [
            ['masterfile_price.id','item.id','item.item_name']
        ],
        // [sequelize.literal('Item.item_name')]
        //  ['masterfile_price.id']

        raw: true,
        include: [{
            model: db.models.Item,
            as: 'item',
            attributes: ['id',"item_name"],
        }],

    })
        

It will give me this error error DatabaseError [SequelizeDatabaseError]: column "item.item_name" must appear in the GROUP BY clause or be used in an aggregate function I don't know why

the query is this

SELECT
"masterfile_price"."id",
"item"."item_name",
"item"."id" AS "item.id",
"item"."item_name" AS "item.item_name" 
FROM
"masterfile_prices" AS "masterfile_price"
LEFT OUTER JOIN "masterfile_items" AS "item" ON "masterfile_price"."itemId" = "item"."id" 
GROUP BY
"masterfile_price"."id";
    

the problem is I only want the item_name

in my orignal query without using sequelize it's working this

    SELECT
    "item"."item_name" AS "item.item_name" 
    FROM
    "masterfile_prices" AS "masterfile_price"
    LEFT OUTER JOIN "masterfile_items" AS "item" ON "masterfile_price"."itemId" = "item"."id" 
    GROUP BY
    item.item_name;

which will the results of

I don't know why I can't replicate it, in sequelize, or am I missing something?

or is there something wrong with sequelize group by

CodePudding user response：

group option should be either string | Fn | Col or an array of string | Fn | Col. So you need to remove extra brackets:

group: ['masterfile_price.id','item.id','item.item_name']

If you want to select and group by only item.item_name then you need to indicate only it in both attributes and group options:

db.models.Price.findAll({
        attributes:[[Sequelize.col('item.item_name'), 'item_name']],
        group: Sequelize.col('item.item_name'),
        raw: true,
        include: [{
            model: db.models.Item,
            as: 'item',
            attributes: [],
        }],
    })