时间戳

首页 > 解决方案 > 使用 sequelize node.js 执行查询时分组格式错误

sql - 使用 sequelize node.js 执行查询时分组格式错误

问题描述

工作至今

model.specialistServiceCategory
    .findAll({ 
      where: { specialistId: req.query.loggedInUserData.userId }
    })
    .then(d => {
      let sServiceCatArr = d.map(i => i.categoryId);
      var searchCond = { 
        where: { status: "OPEN", categoryId: sServiceCatArr, id: { [Op.notIn]: [sequelize.literal("SELECT `bidId` FROM `bid_response` WHERE `specialistId` =" + req.query.loggedInUserData.userId)] } },
      };
      model.bid
        .count(searchCond)
        .then(totalRecord => {
          var totalPage = Math.ceil(totalRecord / limit);
          model.bid
            .findAll({

              where: searchCond.where,
              include: [
                {
                  model: model.requester, attributes: ["id", "firstName", "lastName", "email", "mobile",
                    [sequelize.fn("if", sequelize.literal("profileImage IS NOT NULL"), sequelize.fn("concat", req.app.get("httpUrlUpload"), sequelize.col("profileImage")), ""), "profileImage"]
                  ], required: false
                },  {model:model.bidResponse, attributes:{
    include: ['bidId']
  }, include: {model:model.reviewRating, attributes: [[model.sequelize.fn('AVG', model.sequelize.col('rating')),'avgRating']] } }
                /*{
                  model: model.bidResponse,
                  where: { specialistId: req.query.loggedInUserData.userId },
                  required: false
                }*/
              ],
              limit: limit,
              offset: offset,
              $sort: { id: 1 },
               group: [model.sequelize.literal('`id`')]
            })
            .then(data => util.sendPaginateResponse(res, data, totalRecord, totalPage, page))
            .catch(e => util.handleError(e, res));
        })
        .catch(e => util.handleError(e, res));
    })
    .catch(e => util.handleError(e, res));

通过上面 Sequelize ORM 生成的查询

SELECT `bid`.*, `requester`.`id` AS `requester.id`, `requester`.`firstName` AS `requester.firstName`, `requester`.`lastName` AS `requester.lastName`, `requester`.`email` AS `requester.email`, `requester`.`mobile` AS `requester.mobile`, if(profileImage IS NOT NULL, concat('http://localhost:3002/uploads/', `profileImage`), '') AS `requester.profileImage`, `bidResponses`.`id` AS `bidResponses.id`, `bidResponses`.`bidId` AS `bidResponses.bidId`, `bidResponses`.`specialistId` AS `bidResponses.specialistId`, `bidResponses`.`price` AS `bidResponses.price`, `bidResponses`.`createdAt` AS `bidResponses.createdAt`, `bidResponses`.`updatedAt` AS `bidResponses.updatedAt`, `bidResponses`.`bidId` AS `bidResponses.bidId`, `bidResponses->reviewRatings`.`id` AS `bidResponses.reviewRatings.id`, AVG(`rating`) AS `bidResponses.reviewRatings.avgRating` FROM (SELECT `bid`.`id`, `bid`.`requesterId`, `bid`.`categoryId`, `bid`.`status`, `bid`.`scheduleDate`, `bid`.`expiryDateTime`, `bid`.`description`, `bid`.`latitude`, `bid`.`longitude`, `bid`.`createdAt`, `bid`.`updatedAt` FROM `bid` AS `bid` WHERE `bid`.`status` = 'OPEN' AND `bid`.`categoryId` IN (1, 3, 4) AND `bid`.`id` NOT IN (SELECT `bidId` FROM `bid_response` WHERE `specialistId` =6) GROUP BY `id` LIMIT 0, 10) AS `bid` LEFT OUTER JOIN `requester` AS `requester` ON `bid`.`requesterId` = `requester`.`id` LEFT OUTER JOIN `bid_response` AS `bidResponses` ON `bid`.`id` = `bidResponses`.`bidId` LEFT OUTER JOIN `review_rating` AS `bidResponses->reviewRatings` ON `bidResponses`.`specialistId` = `bidResponses->reviewRatings`.`referenceId`;

必填查询

SELECT `bid`.*, `requester`.`id` AS `requester.id`, `requester`.`firstName` AS `requester.firstName`, `requester`.`lastName` AS `requester.lastName`, `requester`.`email` AS `requester.email`, `requester`.`mobile` AS `requester.mobile`, if(profileImage IS NOT NULL, concat('http://localhost:3002/uploads/', `profileImage`), '') AS `requester.profileImage`, `bidResponses`.`id` AS `bidResponses.id`, `bidResponses`.`bidId` AS `bidResponses.bidId`, `bidResponses`.`specialistId` AS `bidResponses.specialistId`, `bidResponses`.`price` AS `bidResponses.price`, `bidResponses`.`createdAt` AS `bidResponses.createdAt`, `bidResponses`.`updatedAt` AS `bidResponses.updatedAt`, `bidResponses`.`bidId` AS `bidResponses.bidId`, `bidResponses->reviewRatings`.`id` AS `bidResponses.reviewRatings.id`, AVG(`rating`) AS `bidResponses.reviewRatings.avgRating` FROM (SELECT `bid`.`id`, `bid`.`requesterId`, `bid`.`categoryId`, `bid`.`status`, `bid`.`scheduleDate`, `bid`.`expiryDateTime`, `bid`.`description`, `bid`.`latitude`, `bid`.`longitude`, `bid`.`createdAt`, `bid`.`updatedAt` FROM `bid` AS `bid` WHERE `bid`.`status` = 'OPEN' AND `bid`.`categoryId` IN (1, 3, 4) AND `bid`.`id` NOT IN (SELECT `bidId` FROM `bid_response` WHERE `specialistId` =6)  LIMIT 0, 10) AS `bid` LEFT OUTER JOIN `requester` AS `requester` ON `bid`.`requesterId` = `requester`.`id` LEFT OUTER JOIN `bid_response` AS `bidResponses` ON `bid`.`id` = `bidResponses`.`bidId` LEFT OUTER JOIN `review_rating` AS `bidResponses->reviewRatings` ON `bidResponses`.`specialistId` = `bidResponses->reviewRatings`.`referenceId` GROUP BY `id`;

如您所见，上述查询在所需查询中的细微差别是，group by 在最后一个表示正确的数据，而在通过 sequelize.js 生成的错误查询中，节点在子查询中具有 group by。

标签： sqlnode.jsnpmormsequelize.js

解决方案

好的，我可以使用问题 URL的引用来解决它， 问题是关于子查询中的 where 子句，但我遇到了与 group by 类似的问题，但它也解决了我的问题。

我在上面的代码中做了以下更改并且它有效。

  limit: limit,
                  offset: offset,
                  $sort: { id: 1 },
                  subQuery: false,  //this was the change...
                   group: [model.sequelize.literal('`id`'

)]

推荐阅读