首页 文章

Sequelize ORM在选择中添加主键列，而我将其排除在外

提问于
浏览 次
2

我通过sequelize准备一个查询，它通过关联hasOne，聚合函数SUM（）和Group By子句获得记录，但是sequelize查询自动添加了目标表主键列，并且该列导致group by子句错误 .

这是我的节点功能，它从服务中获取数据 .

exports.getCustomerByManagerId = async (userId, limit, offset) => {
  const customers = await models.Customer.findAndCountAll({
    where: { account_manager_id: userId },
    include: [
      { model: models.UserProfile, as: 'accountmanager', attributes: userProfileSanitizer.sanitizeuserProfile() },
      {
        model: models.Task,
        as: 'customertask',
        attributes: [
          [sequelize.literal('SUM(IF(customertask.status = 1, 1, 0))'), 'todo'],
          [sequelize.literal('SUM(IF(customertask.status = 2, 1, 0))'), 'pendingTask'],
          [sequelize.literal('SUM(IF(customertask.status = 3, 1, 0))'), 'completeTask'],
          [sequelize.literal('COUNT(customertask.id)'), 'totalTask']
        ]
      }
    ],
    group: ['`customer`.`customer_id`'],
    order: [
      ['createdAt', 'DESC']
    ],
    limit,
    offset
  });

并且sequelize查询生成结果查询AS：

SELECT `customer`.`customer_id`, `customer`.`userId`, `customer`.`name`, `customer`.`account_manager_id`, `customer`.`start_date`, `customer`.`renewal_date`, `customer`.`customer_type`, 
`customer`.`SKU`, `customer`.`variation`, `customer`.`similar`, `customer`.`setCost`, `customer`.`SKUCost`, `customer`.`variationCost`, `customer`.`similarCost`, 
`customer`.`actual_monthly_cost`, `customer`.`address1`, `customer`.`address2`, `customer`.`city`, `customer`.`state`, `customer`.`category`, `customer`.`createdAt`, 
`customer`.`updatedAt`, `accountmanager`.`userId` AS `accountmanager.userId`, `accountmanager`.`firstName` AS `accountmanager.firstName`, `accountmanager`.`lastName` 
AS `accountmanager.lastName`, `accountmanager`.`isTermsofService` AS `accountmanager.isTermsofService`, 
`customertask`.`id` AS `customertask.id`,
SUM(IF(customertask.status = 1, 1, 0)) AS `customertask.todo`, 
SUM(IF(customertask.status = 2, 1, 0)) AS `customertask.pendingTask`, 
SUM(IF(customertask.status = 3, 1, 0)) AS `customertask.completeTask`, 
COUNT(customertask.customer_id) AS `customertask.totalTask` 
FROM `customer` AS `customer` 
LEFT OUTER JOIN `user_profiles` AS `accountmanager` ON `customer`.`account_manager_id` = `accountmanager`.`userId` 
LEFT OUTER JOIN `task` AS `customertask` ON `customer`.`customer_id` = `customertask`.`customer_id` 
WHERE `customer`.`account_manager_id` = 1 
GROUP BY `customer`.`customer_id`
ORDER BY `customer`.`createdAt` DESC LIMIT 0, 10;

这是由sequelize添加的，导致错误customeateask.id AS customertask.id，当我删除此行并在mysql workbench编辑器中执行查询然后它工作ERROR AS：错误代码：1055. SELECT列表的表达式＃27不在GROUP BY子句并包含非聚合列'DBName.customertask.id'，它在功能上不依赖于GROUP BY子句中的列

mysql node.js sequelize.js

1 回答

  • 0

    我会先说这个，我不知道这是不是解决方案，但是在评论中写的太多了 .

    您是否尝试从 customertask 明确排除 id 字段，因此它不是选定的属性？例如

    exports.getCustomerByManagerId = async (userId, limit, offset) =>
{
    const customers = await models.Customer.findAndCountAll({
        where: { account_manager_id: userId },
        include: [
            { model: models.UserProfile, as: 'accountmanager', attributes: userProfileSanitizer.sanitizeuserProfile() },
            {
                model: models.Task,
                as: 'customertask',
                attributes: {
                    include: [
                        [ sequelize.literal('SUM(IF(customertask.status = 1, 1, 0))'), 'todo' ],
                        [ sequelize.literal('SUM(IF(customertask.status = 2, 1, 0))'), 'pendingTask' ],
                        [ sequelize.literal('SUM(IF(customertask.status = 3, 1, 0))'), 'completeTask' ],
                        [ sequelize.literal('COUNT(customertask.id)'), 'totalTask' ]
                    ],
                    exclude: [ 'id' ] // <---------- exclude the id
                }
            }
        ],
        group: [ '`customer`.`customer_id`' ],
        order: [
            [ 'createdAt', 'DESC' ]
        ],
        limit,
        offset
    });
};
    回复于

相关问题