I have this function which returns the categories containing the product with the lowest price.
async getAllWithMinPrice(req, res, next) {
try {
const categories = await Category.findAll({
attributes: ["id", "name"],
group: ["products.id"],
include: [
{
model: Product,
attributes: [
"id",
"name",
"pluralName",
"description",
"image",
[Sequelize.fn("MIN", Sequelize.col("price")), "minPrice"],
],
include: [
{
model: Type,
attributes: [],
},
],
},
],
});
return res.json(categories);
} catch (e) {
return next(ApiError.badRequest(e.message ? e.message : e));
}
}
But I am getting an error: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fotoluks.category.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by".
On my test server, I just disabled ONLY_FULL_GROUP_BY in MySQL, but I don't have permission to do so on the current server.
As far as I understand, to solve this problem, you need to use any_value, but I just can't figure out how to do it in Sequelize.
P.S. In a similar function, I had exactly the same error, then I solved it by simply adding attributes: [], but in this case I can't do it.