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
| item.item_name |
|---|
| test9 |
| test10 |
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
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: [],
}],
})