Ordering and Grouping

[codecnmc]

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

Reproducible Example

The result of the run is grouping before sorting

group by always get the first , because sorting execute after grouping

let data=await app.model.Topic.findAll({
  group:["course_id"],
  order:[["createdAt","desc"]]
})

What do you expect to happen?

Sort before grouping Use query not very convenient

What is actually happening?

But create statement is simple statement So one select can't resolve order and group exist together .
Except use query ,For example

 let data = await app.modelmysql.query(
            "SELECT * FROM ( SELECT`id`,`title`,`plate`,`updatedAt` FROM `topic_forum` AS `topic_forum` WHERE (`topic_forum`.`deletedAt`IS NULL AND(`topic_forum`.`course_id`= :course_id AND`topic_forum`.`school_id`= :school_id )) HAVING (0<>1) ORDER BY `topic_forum`.`updatedAt`DESC ) r GROUP BY r.`plate`"
            , {
                replacements: { school_id, course_id },
                type: QueryTypes.SELECT
 })

Not very convenient

Environment

• Sequelize version:6.0.0
• Node.js version:v16.13.0
• If TypeScript related: TypeScript version:
• Database & Version:8.0.25
• Connector library & Version:

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

---

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.

[WikiRik]

As I mentioned in the comment of your SSCCE PR; timestamps was disabled so createdAt is null in the database and therefore couldn't order by createdAt. If you update the SSCCE we might be able to take another look.

Furthermore you are using a very different query in the 'What is actually happening?' section than would be generated in your example under 'Reproducible Example'. It would help if you used the same example and explained what the current generated SQL is and what you would expect the generated SQL to be like.

[ephys]

If I understand correctly, they would like to have a way to generate the second query, which sorts first and groups by second instead of the current query which groups by then sorts.

That would make this issue not a bug report but a feature request. The current behavior is what the query is correct as the default behavior.

That being said, I think the query is niche enough that it can be handled by sequelize.query for now.
To be perfectly honest I think supporting group in findAll may be a mistake because it's not returning a model anymore. This feature already feels "shoehorned" into the design of findAll and I don't want to add such an option if we're going to provide a cleaner alternative.
I'm really starting to think we need a low-level Query Builder feature à la Knex (something like sequelize.select('id').from('users')) for these sort of queries

[codecnmc]

As I mentioned in the comment of your SSCCE PR; timestamps was disabled so createdAt is null in the database and therefore couldn't order by createdAt. If you update the SSCCE we might be able to take another look.

Furthermore you are using a very different query in the 'What is actually happening?' section than would be generated in your example under 'Reproducible Example'. It would help if you used the same example and explained what the current generated SQL is and what you would expect the generated SQL to be like.

Sorry my bad , this is new sscce url sequelize/sequelize-sscce#249

Use score to order may be easy to understand

[codecnmc]

If I understand correctly, they would like to have a way to generate the second query, which sorts first and groups by second instead of the current query which groups by then sorts.

That would make this issue not a bug report but a feature request. The current behavior is what the query is correct as the default behavior.

That being said, I think the query is niche enough that it can be handled by sequelize.query for now. To be perfectly honest I think supporting group in findAll may be a mistake because it's not returning a model anymore. This feature already feels "shoehorned" into the design of findAll and I don't want to add such an option if we're going to provide a cleaner alternative. I'm really starting to think we need a low-level Query Builder feature à la Knex (something like sequelize.select('id').from('users')) for these sort of queries

Yes,That's what I mean.I thought about it,It's really not a bug , Just group after order and group before order.

Query Builder is good resolve way , I used typeorm in past . Then we can not need to sequelize.query to write code because if statement to long to impact viewing code.

[codecnmc]

If I understand correctly, they would like to have a way to generate the second query, which sorts first and groups by second instead of the current query which groups by then sorts.

That would make this issue not a bug report but a feature request. The current behavior is what the query is correct as the default behavior.

That being said, I think the query is niche enough that it can be handled by sequelize.query for now. To be perfectly honest I think supporting group in findAll may be a mistake because it's not returning a model anymore. This feature already feels "shoehorned" into the design of findAll and I don't want to add such an option if we're going to provide a cleaner alternative. I'm really starting to think we need a low-level Query Builder feature à la Knex (something like sequelize.select('id').from('users')) for these sort of queries

// The following data in database,For example in this case ,I expect get the everyone's best score and rank the result
const data=[ { name:"tom" , score:1} , { name:"tom" , score:2 } , { name:"john" , score:1 } , { name:"john" , score:3 }]

That might be the case ,subQuery sorting Result need to grouping after sorting.

[github-actions]

This bug report has not been verified by maintainers yet, and has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, consider commenting with more information to help us verify it.

[ephys]

I'll close as this is not something we'll do until we have query-building capabilities