Sequelize - MySQL error: SELECT list is not in GROUP BY clause

Viewed 65

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.

0 Answers
Related