Sequelize adds id(PK) of main table in findAll with 'group' and currupts a query with it in posgre.

[rusekr]

Hello! I have Products model, and Vendors model. With assocation that Vendor has many products and product has one vendorId. I need filter vendors by product properties and/or vendor properties. Using postgre 9.6 and camelcase columns. Sequelize 3.30.2

Therefore object for querying:

 Products.findAll({
      attributes: [],
      where: { type: 'service' },
      include: [{model: Vendors, attributes: ['id', 'name', 'description'], as: 'vendor', required: true, where: { approved: true } }],
      group: ['vendor.id']
    })

Generates incorrect query (with "Products"."id"):

SELECT "Products"."id", "vendor"."id" AS "vendor.id", "vendor"."name" AS "vendor.name", "vendor"."description" AS "vendor.description" FROM "Products" AS "Products" INNER JOIN "Vendors" AS "vendor" ON "Products"."vendorId" = "vendor"."id" AND "vendor"."approved" = true WHERE "Products"."type" = 'service' GROUP BY "vendor"."id";

Query without "Products"."id" works fine and returns vendorId`s filtered by vendor parameters (or/and product parameters):

SELECT "vendor"."id" AS "vendor.id", "vendor"."name" AS "vendor.name", "vendor"."description" AS "vendor.description" FROM "Products" AS "Products" INNER JOIN "Vendors" AS "vendor" ON "Products"."vendorId" = "vendor"."id" AND "vendor"."approved" = true WHERE "Products"."type" = 'service' GROUP BY "vendor"."id";

Is any way to tell Sequelize to not include primary key if I don`t want it in attributes?

Thanks!

upd. workaround https://github.com/sequelize/sequelize/issues/7534#issuecomment-881615707

[rusekr]

As for Mysql - it ignores primary key id in first query and returns expected result. But that not postgre..

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[rusekr]

bump

[javier-perez]

I have the same issue with mysql (ONLY_FULL_GROUP_BY enabled), the query is showing gratitude.id even I dont have it in the attributes array.

gratitudeModel.findAll({ include: [ { attributes: [], model: sequelize.model('skill'), as: 'skill' } ], attributes: [ sequelize.col('skillId'), [sequelize.fn('sum', { senderId: userId } ), 'given'], [sequelize.fn('sum', { senderId: userId } ), 'received'], ], where: { networkId: networkId, $or: [ { 'receiverId': userId }, { 'senderId': userId } ] }, group: [ 'skillId'] })

Error

Executing (default): SELECT gratitude.id, skillId, sum(senderId = '1') AS given, sum(senderId = '1') AS received FROM gratitude AS gratitude LEFT OUTER JOIN skill AS skill ON gratitude.skillId = skill.id WHERE gratitude.networkId = '1' AND (gratitude.receiverId = '1' OR gratitude.senderId = '1') GROUP BY skillId; info: error: stats - Method: find: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'feedback.gratitude.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by error: SequelizeDatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'feedback.gratitude.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Thanks

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[wdonet]

Using: Sequelize 4.5.0 and Mysql 5.7.19

Exactly this happened to me . As @rusekr mentioned, it can be ignored in MySQL through doing something like below (but not a good practice): SET SESSION sql_mode =''

Wonder why sequelize adds the PK. Following docs , it says about options.attributes

A list of the attributes that you want to select, or an object with include and exclude keys...

Then for options.attributes.include

Select all the attributes of the model, plus some additional ones...

Then for options.attributes.exclude

Select all the attributes of the model, except some few ...

That means that the only options for selecting the right columns on my query is to use options.attributes with an array value instead of object value. But then the PK gets added to the resultant query.

Doesn't make sense to add the PK whenever you join models or doing that without having a way to exclude it.

[anilkonsal]

Adding raw: true to the main object solves this issue.

[rusekr]

@anilkonsal Thanks for the tip! But "raw" heavely changes parse result logic though.

[rusekr]

update: adding raw :true not solved problem in 3.30.2

[rusekr]

update: adding raw :true not solved problem in 4.19.0

[rusekr]

bug still present in 4.27.0..

[avrebarra]

Any updates on this issue? @rusekr

---

I tried this query:

await Coupon.all({
      subQuery: false,
      attributes: ['tag'],
      include: [
        {
          association: 'coupon_histories',
        },
      ],
      group: 'tag',
    });

I was expecting something like this:

 SELECT 
    `coupon`.`tag`,
    COUNT(`coupon_histories`.`coupon_id`)
FROM
    `coupons` AS `coupon`
    LEFT OUTER JOIN
    `coupon_histories` AS `coupon_histories` ON `coupon`.`id` = `coupon_histories`.`coupon_id`
GROUP BY `coupon`.`tag`

Yet Sequelize is resulting this query:

SELECT 
    `coupon`.`id`,
    `coupon`.`tag`,
    `coupon_histories`.`id` AS `coupon_histories.id`,
    `coupon_histories`.`user_id` AS `coupon_histories.user_id`,
    `coupon_histories`.`coupon_id` AS `coupon_histories.coupon_id`,
    `coupon_histories`.`order_id` AS `coupon_histories.order_id`
FROM
    `coupons` AS `coupon`
    LEFT OUTER JOIN
    `coupon_histories` AS `coupon_histories` ON `coupon`.`id` = `coupon_histories`.`coupon_id`
GROUP BY `tag`;

[rusekr]

It still adding ID. And no response from maintainers.

[NikxDa]

This should get some attention, it is a persistent and deal-breaking problem.

[rusekr]

It breaks aggregation

[avrebarra]

for now I resorted to raw queries. made me add another deps like sql-bricks, dotties to make the sql queries bit more customizable and manageable.

it's working quite well, but i'd still rather have this issue fixed though to have uniform and simple data fetching method across my system.

[Alexsey]

if you are using group with include then you must set included attrubutes: []. Trik is that you are listing needed attributes in the top attributes and if you are not using MySQL that allow having non-aggregated non-grouped attributes in SELECT then you must wrap them with some dummy function, like MAX

Products.findAll({
  attributes: [
        [sequelize.fn('MAX', sequelize.col('vendor.id'), 'id'],
        [sequelize.fn('MAX', sequelize.col('vendor.name'), 'name'],
        [sequelize.fn('MAX', sequelize.col('vendor.description'), 'description']
  ],
  where: { type: 'service' },
  include: [{
    model: Vendors,
    as: 'vendor', 
    attributes: [],
    required: true,
    where: { approved: true }
  }],
  group: ['vendor.id']
})

I know that it looks horrible, but it works

[joewoodhouse]

Still stuck on this issue, anyone looking?

[ehsankhfr]

Generally, there are two solutions: 1- to revise your queries and make them sorted by the standards this error is expecting 2- to disable the rule: in sequelize:

sequelize.query(
                    `SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))`,
                    { raw: true }
                ).then(() => {
                    console.log('variable is set')
                }).catch((err) => {
                    console.log('variable is not set')
                    console.error(err)
                })

or, editing /etc/mysql/my.cnf:

[mysqld]  
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I recommend the first solution(revising the queries), but solution 2 may be fine if you feel confident about the queries.

[yasnovo]

Hi, any updates on this issue? It seems to still present in 4.38.0.

[papb]

If #3142 is fixed, would this issue be fixed too?

[rusekr]

If #3142 is fixed, would this issue be fixed too?

Can`t test now but saw https://github.com/sequelize/sequelize/issues/3142 and it not fixes fact when model has PK and PK automatically added to any query with 'group' property which errores in postgres. I will rename issue to reflect this.

This by seeing the code might fix current issue: https://github.com/sequelize/sequelize/pull/11283

[rusekr]

Waiting for response from https://github.com/sequelize/sequelize/pull/11283

[jsvargas]

If you add an attribute named 'id' with another value, sequelize returns all the expected values. For example:

Products.findAll({
  attributes: [
        [sequelize.fn('CONCAT', sequelize.col('Product.id'), ' ', sequelize.col('Vendor.id')), 'id']
        [sequelize.col('vendor.name'), 'name'],
        [sequelize.col('vendor.description'), 'description']
  ],
  include: [{
    model: Vendors,
    attributes: [],
    required: true,
    where: { approved: true }
  }],
  group: ['vendor.id']
})

Here, concat is used to generate an id for each Product/Vendor association.

[cagdas001]

This is still an issue with MSSQL. raw: true and even sequelize.literaral (for order and group) doesn't work, it still adds the PK.

await Incident.findAll({
    attributes: ['incidentHandler.name', [models.sequelize.fn('count', '*'), 'count']],
    include: [
      {
        model: User,
        attributes: [],
        as: 'incidentHandler',
        required: true
      }
    ],
    limit: 7,
    order: models.sequelize.literal('count desc'),
    group: ['incidentHandler.name'],
    raw: true
  })

generates

SELECT [incidentHandler].[name], count(N'*') AS [count] FROM [dbo].[Incident] AS [Incident] INNER JOIN [dbo].[Users] AS [incidentHandler] ON [Incident].[IncidentHandler] = [incidentHandler].[Id] GROUP BY [incidentHandler].[name] ORDER BY count desc ORDER BY [Incident].[Id] OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY;

we need to get rid of the automatically added ORDER BY [Incident].[Id]

Unfortunately, I ended up using raw query:

  const topHandlers = await models.sequelize.query(
    'SELECT IncidentHandler.name AS name, count(*) AS count ' +
    'FROM Incident AS Incident ' +
    'INNER JOIN Users AS IncidentHandler ON Incident.IncidentHandler = IncidentHandler.Id ' +
    'GROUP BY name ' +
    'ORDER BY count desc ' +
    'OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY;',
    { type: Sequelize.QueryTypes.SELECT }
  )

[ashwinkjoseph]

This issue still exists for me, but for nested includes. My sequelize version is 6.3.5. This is my query:

Model1.findAll({
        attributes: [
          [Sequelize.fn('date_trunc', groupBy, Sequelize.literal(`"model1"."createdAt" AT TIME ZONE '${timeZone}'`)), 'aggregateattribute1'],
          [Sequelize.fn('sum', Sequelize.col('Model1attribue4')), 'aggregateattribute2'],
          [Sequelize.fn('count', Sequelize.col('"model1".id')), 'aggregateattribute3'],
        ],
        where: {
          model1attribute3: Model1Status.paid,
          model1attribute1: false,
          model1attribute2: Model1Type.package,
          createdAt: {
            [Sequelize.Op.gte]: startTime,
            [Sequelize.Op.lte]: endTime ? endTime : startTime,
          },
        },
        group: [Sequelize.fn('date_trunc', 'day', Sequelize.literal(`"Model1"."createdAt" AT TIME ZONE '${timeZone}'`))],
        include: [
          {
            association: Model1.associations.Model2,
            where: {
              driverId: ctx.user.id,
              status: Model2Status.completed,
            },
            attributes: [],
            include: [
              {
                association: Model2.associations.Model3,
                attributes: [
                  [
                    Sequelize.fn('sum', Sequelize.literal('COALESCE("model2->model3"."attribute1", NOW()) - "model2->model3"."attribute2"')),
                    'attribute3',
                  ]
                ],
                required: true,
              },
            ],
            required: true,
          },
        ],
      })

This works perfectly fine until the first include but breaks at the second nested include. It includes the ID of Model3 in the select query

Here's the generated query -

SELECT date_trunc('day', "model1"."createdAt" AT TIME ZONE 'US/Central') AS "aggregateattribute1", sum("Model1attribue4") AS "aggregateattribute2", count("model1"."id") AS "aggregateattribute3", "model2->model3"."id" AS "model2.model3.id", sum(COALESCE("model2->model3"."attribute1", NOW()) - "model2->model3"."attribute2") AS "model2.model3.attribute3" FROM "Model1" AS "model1" INNER JOIN "Model2" AS "model2" ON "model1"."foreignkeytomodel2" = "model2"."model2primarykey" AND "model2"."model2foreignkeytouser" = 2 AND "model2"."status" = 'completed' INNER JOIN "Model2" AS "model2" ON "model2"."primarykeyofmodel2" = "model3"."foreignKeytomodel2" WHERE "model1"."model1attribute3" = 'paid' AND "model1"."model1attribute1" = false AND "model1"."model1attribute2" = 'package' AND ("model1"."createdAt" >= '2020-11-25 09:01:25.288 +00:00' AND "model1"."createdAt" <= '2020-11-25 09:01:25.288 +00:00') GROUP BY date_trunc('day', "model1"."createdAt" AT TIME ZONE 'US/Central');

As you can see, the model3's id is also being added into the select query even though I haven't specified it in attributes list.

I will be resorting to raw queries for now.

I am willing to help you resolve this issue by making a PR if any of you can guide me on where to get started.

[fanker]

I have the same issue in mysql, with [email protected]

[jamt0]

I have the same issue in mysql

[GhettoBurger996]

Got the same issue in PostgreSQL using sequelize version 6.6.2

[kippllo]

This is still an issue for me in Sequelize v6.3.5 with MSSQL. I guess the devs hate us... 🤣

I wonder if this will ever get fixed...