sequelize / sequelize/sequelize
Is there a way to exclude primary key attribute when doing COUNT + LEFT JOIN + GROUP BY + ORDER BY?
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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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