Menu
I have User and Post table.
User has Many Posts relationship. I want to query User and order the query by the number of posts that the users made. How do I write this sequelize query?
Post.belongsTo(User, {foreignKey: 'userId'});
User.hasMany(Post, {foreignKey: 'userId'});
User.findAll( {
order: 'COUNT(Post) DESC', //???
include: [Post]
});
How do I get this User query sorted by Post?
285
To add an ORDER BY clause to the Sequelize query methods, you need to specify the order option inside the query methods you used for retrieving data from your database. Now you are tasked with retrieving the data from the table, ordering the result with the following rules: Sort by population column in descending order.
Sequelize Model. count() method is used to generate and execute a SELECT COUNT SQL query to your connected database. The method accepts an object of options that you can define to modify the generated query. You need to create a Sequelize Model for the above table and call the count() method from there.
findByPk The findByPk method obtains only a single entry from the table, using the provided primary key. const project = await Project. findByPk(123); if (project === null) {
The Sequelize setters and getters might not be used here. So setting raw to true provides me the desired output with a cleaner look & feel.
There is probably a better way, but you can use a .literal() to create a subquery and assign an alias. Then, you can reference the alias when ordering, sample:
User.findAll({
attributes: [
'User.username',
[sequelize.literal('(SELECT COUNT(*) FROM Posts WHERE Posts.userId = User.id)'), 'PostCount']
],
order: [[sequelize.literal('PostCount'), 'DESC']]
});
This would produce the following SQL:
SELECT
`User`.`username`,
(SELECT COUNT(*) FROM Posts WHERE Posts.userId = User.id) AS `PostCount`
FROM
`Users` AS `User`
ORDER BY
PostCount DESC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
