Sequelize,проблема с запросом продуктов с рейтингом и тэгами

Question

Проблема с запросом в которым выбираются продукты с тэгами и их рейтингом. Не могу решить проблему уже 2 месяц,очень нужна помощь.

Вот связи:

const models = require("./models");

const initRelations = () => {
  models.Product.belongsToMany(models.Tag, { through: "ProductTag" });

  models.Tag.belongsToMany(models.Product, { through: "ProductTag" });

  models.User.belongsToMany(models.Role, { through: "UserRoles" });

  models.Role.belongsToMany(models.User, { through: "UserRoles" });

  models.User.hasMany(models.Rating);

  models.Product.hasMany(models.Rating);

  models.Rating.belongsTo(models.Product);

  models.Rating.belongsTo(models.User);
};
module.exports = { initRelations };

Вот запрос:

const products = await models.Product.findAll({
        subQuery: false,

        offset,

        limit: Number(amount),

        where: whereOptions,

        order: orderOptions,

        attributes: [
          "id",
          "price",
          "title",
          "amount",
          "description",
          "picture",
          "createdAt",
          "updatedAt",

          [
            sequelize.fn("avg", sequelize.col("ratings.ratingValue")),
            "averageRating"
          ],

          [
            sequelize.fn("count", sequelize.col("ratings.ratingValue")),
            "amountOfRatings"
          ]
        ],

        include: [
          {
            model: models.Rating,
            required: false,
            attributes: ["ratingValue", "productId"]
          },
          {
            model: models.Tag,
            attributes: []
          }
        ],

        group: ["products.id"]
      });

Вот ошибка:

DatabaseError [SequelizeDatabaseError]: Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'omVapeShop.ratings.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mainserver       |     at Query.formatError (/app/mainserver/node_modules/sequelize/lib/dialects/mysql/query.js:244:16)
mainserver       |     at Query.handler [as onResult] (/app/mainserver/node_modules/sequelize/lib/dialects/mysql/query.js:51:23)
mainserver       |     at Query.execute (/app/mainserver/node_modules/mysql2/lib/commands/command.js:30:14)
mainserver       |     at Connection.handlePacket (/app/mainserver/node_modules/mysql2/lib/connection.js:417:32)
mainserver       |     at PacketParser.onPacket (/app/mainserver/node_modules/mysql2/lib/connection.js:75:12)
mainserver       |     at PacketParser.executeStart (/app/mainserver/node_modules/mysql2/lib/packet_parser.js:75:16)
mainserver       |     at Socket.<anonymous> (/app/mainserver/node_modules/mysql2/lib/connection.js:82:25)
mainserver       |     at Socket.emit (events.js:321:20)
mainserver       |     at addChunk (_stream_readable.js:297:12)
mainserver       |     at readableAddChunk (_stream_readable.js:273:9)
mainserver       |     at Socket.Readable.push (_stream_readable.js:214:10)
mainserver       |     at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
mainserver       |   name: 'SequelizeDatabaseError',

Тело запроса:

Executing (default): SELECT `product`.`id`, `product`.`price`, `product`.`title`, `product`.`amount`, `product`.`description`, `product`.`picture`, `product`.`createdAt`, `product`.`updatedAt`, avg(`ratings`.`ratingValue`) AS `averageRating`, count(`ratings`.`ratingValue`) AS `amountOfRatings`, `ratings`.`id` AS `ratings.id`, `ratings`.`ratingValue` AS `ratings.ratingValue`, `ratings`.`productId` AS `ratings.productId`, `tags->ProductTag`.`createdAt` AS `tags.ProductTag.createdAt`, `tags->ProductTag`.`updatedAt` AS `tags.ProductTag.updatedAt`, `tags->ProductTag`.`productId` AS `tags.ProductTag.productId`, `tags->ProductTag`.`tagId` AS `tags.ProductTag.tagId` FROM `products` AS `product` LEFT OUTER JOIN `ratings` AS `ratings` ON `product`.`id` = `ratings`.`productId` LEFT OUTER JOIN ( `ProductTag` AS `tags->ProductTag` INNER JOIN `tags` AS `tags` ON `tags`.`id` = `tags->ProductTag`.`tagId`) ON `product`.`id` = `tags->ProductTag`.`productId` GROUP BY `product`.`id` ORDER BY `product`.`amount` DESC LIMIT 0, 3;

Если будут нужны еще какие нибудь данные - обязательно скину

Answer 1

Ответ найден:

const products = await models.Product.findAll({
        subQuery: false,

        offset,

        limit: Number(amount),

        where: whereOptions,

        order: orderOptions,

        attributes: [
          "id",
          "price",
          "title",
          "amount",
          "description",
          "picture",
          "createdAt",
          "updatedAt",

          [
            sequelize.fn("avg", sequelize.col("ratings.ratingValue")),
            "averageRating"
          ],

          [
            sequelize.fn("count", sequelize.col("ratings.ratingValue")),
            "amountOfRatings"
          ]
        ],

        include: [
          {
            model: models.Rating,
            required: false,
            attributes: []
          },
          {
            model: models.Tag,
            attributes: ["text", "id"],
            through: {
              attributes: []
            }
          }
        ],

        group: [
          "id",
          "price",
          "title",
          "amount",
          "description",
          "picture",
          "createdAt",
          "updatedAt",
          "text",
          "product.id",
          "tags.id"
        ]
      });

БЛОГ НА HUSL

Sequelize,проблема с запросом продуктов с рейтингом и тэгами

Ответы (1 шт):