sequelize / sequelize/sequelize

Is there a way to exclude primary key attribute when doing COUNT + LEFT JOIN + GROUP BY + ORDER BY?

Open
#3,417 23 comments 15 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
TypeScript
Stars
30.4k
Forks
4.3k
Avg merge
1d 6h
Merged PRs (30d)
68

Description

I have the following code, its in coffeescript:

Reservation.findAll({
    attributes: [
      [DB.Sequelize.fn('COUNT', 'item_id'), 'num_reservations']
    ]
    include: [
      {model: models.Item, as: 'Item', attributes: []}
    ],
    group: ['Item.id']
    order: 'num_reservations ASC'
    })

that generates the following SQL:

SELECT "reservation"."id", COUNT('item_id') AS "num_reservations", "Item"."id" AS "Item.id" 
FROM "reservation" AS "reservation" 
LEFT OUTER JOIN "item" AS "Item" ON "reservation"."item_id" = "Item"."id" AND "Item"."deleted_at" IS NULL 
WHERE "reservation"."deleted_at" IS NULL GROUP BY "Item"."id" ORDER BY num_reservations ASC

and the error:

SequelizeDatabaseError: column "res_reservation.id" must appear in the GROUP BY clause

The expected SQL that works would be the same as above just without "reservation"."id" from the SELECT statement:

SELECT COUNT('item_id') AS "num_reservations", "Item"."id" AS "Item.id" 
FROM "reservation" AS "reservation" 
LEFT OUTER JOIN "item" AS "Item" ON "reservation"."item_id" = "Item"."id" AND "Item"."deleted_at" IS NULL 
WHERE "reservation"."deleted_at" IS NULL GROUP BY "Item"."id" ORDER BY num_reservations ASC

Is there a way to do this? How can I tell the query builder to exclude the "id" (primary key) attribute from the Reservations table?
I know I can solve this using raw query.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the shown Reservation.findAll query with its attributes, include, group, and order options, then inspect the generated SQL. Done means the query builder can omit reservation.id from SELECT for this grouped aggregate query and the resulting SQL executes without the GROUP BY error.

Written by the indexing model from the issue text.

Assessment

Tech stack
coffeescript, sql
Domain
databases
Issue type
Feature
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.