CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Sunny S.M
Sunny S.M

Reputation: 5988

Sequelize ORM add primary key column in selection, While I excluded it

I've prepare a query through sequelize, which get record with association hasOne, aggregation function SUM() and with Group By clause, But sequelize query automatically added target table primary key column and that column cause group by clause error.

Here is my Node Function, Which get data from service.

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
  });

And sequelize query produce result query 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;

This is added by sequelize which cause error customertask.id AS customertask.id, and when I remove this line and execute query in mysql workbench editor then it works

ERROR AS : Error Code: 1055. Expression #27 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DBName.customertask.id' which is not functionally dependent on columns in GROUP BY clause

Upvotes: 5

Views: 1832

Answers (2)

Saravanan G
Saravanan G

Reputation: 61

Adding raw:true will help and it will not add the primary key by default.

Upvotes: 6

Ankh
Ankh

Reputation: 5718

I'll preface this by saying that I don't know if this is the solution of not but it's too much to write in a comment..

Have you tried explicitly excluding the id field from customertask so it's not a selected attribute? e.g.

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
    });
};

Upvotes: 0

Related Questions