×
Join two tables and get count in mysql:Example with hasMany relations in sequelize


Join two tables and get count in mysql:Example with hasMany relations in sequelize

August 15, 2018

Consider we have two tables ‘users‘ and ‘comments‘.Each user can have multiple comments so that ‘users‘ and ‘comments‘  have ‘hasMany‘ relation. The foreign key is user_id in the comments table.

The structure of both tables are as follows

Users

id    | name |    email

1    userone    user1@gmail.com

2    usertwo    user2@gmail.com

Comments

id    | comment        | user_id

1      commentone      1

2      commenttwo      1

3      commentthree    2

4      commentfour     2

5      commentfive      2

6      commentfive      2

As you can see, the user with id 1 has two comments and the user with id 2 has  4 comments. To get all users with their respective number of comments, we can use the following query.

The result will be

1 userone user1@gmail.com 2
2 usertwo user2@gmail.com 4

Sequelize Example

 

To learn more about sequelize, see the following article

https://truetocode.com/orm-using-sequelize-orm-with-express-node-js/418/

For our example, we need to create two model files

usersModel.js

 

commentsModel.js

 

hasMany relation

Now we need to define hasMany relations from users to comments, in your db.js file. You need to import and use both the models in db.js file

 

Sequelize query

Here usersModel and commentsModel are the imported models

 

 

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *