How to make join queries using Sequelize on Node.js

Question

I am using sequelize ORM; everything is great and clean, but I had a problem when I use it with join queries. I have two models: users and posts.

var User = db.seq.define('User',{
    username: { type: db.Sequelize.STRING},
    email: { type: db.Sequelize.STRING},
    password: { type: db.Sequelize.STRING},
    sex : { type: db.Sequelize.INTEGER},
    day_birth: { type: db.Sequelize.INTEGER},
    month_birth: { type: db.Sequelize.INTEGER},
    year_birth: { type: db.Sequelize.INTEGER}

});

User.sync().success(function(){
    console.log("table created")
}).error(function(error){
    console.log(err);
})


var Post = db.seq.define("Post",{
    body: { type: db.Sequelize.TEXT },
    user_id: { type: db.Sequelize.INTEGER},
    likes: { type: db.Sequelize.INTEGER, defaultValue: 0 },

});

Post.sync().success(function(){
    console.log("table created")
}).error(function(error){
    console.log(err);
})

I want a query that respond with a post with the info of user that made it. In the raw query, I get this:

db.seq.query('SELECT * FROM posts, users WHERE posts.user_id = users.id ').success(function(rows){
            res.json(rows);
        });

My question is how can I change the code to use the ORM style instead of the SQL query?

Related: stackoverflow.com/questions/22958683/…

Ciro Santilli OurBigBook.com
– Ciro Santilli OurBigBook.com

2021-12-03 21:26:09 +00:00
Commented Dec 3, 2021 at 21:26 — Ciro Santilli OurBigBook.com
– Ciro Santilli OurBigBook.com, Commented Dec 3, 2021 at 21:26

Ryan Shillington · Accepted Answer · 2018-03-09 14:27:49Z

291

While the accepted answer isn't technically wrong, it doesn't answer the original question nor the follow up question in the comments, which was what I came here looking for. But I figured it out, so here goes.

If you want to find all Posts that have Users (and only the ones that have users) where the SQL would look like this:

SELECT * FROM posts INNER JOIN users ON posts.user_id = users.id

Which is semantically the same thing as the OP's original SQL:

SELECT * FROM posts, users WHERE posts.user_id = users.id

then this is what you want:

Posts.findAll({
  include: [{
    model: User,
    required: true
   }]
}).then(posts => {
  /* ... */
});

Setting required to true is the key to producing an inner join. If you want a left outer join (where you get all Posts, regardless of whether there's a user linked) then change required to false, or leave it off since that's the default:

Posts.findAll({
  include: [{
    model: User,
//  required: false
   }]
}).then(posts => {
  /* ... */
});

If you want to find all Posts belonging to users whose birth year is in 1984, you'd want:

Posts.findAll({
  include: [{
    model: User,
    where: {year_birth: 1984}
   }]
}).then(posts => {
  /* ... */
});

Note that required is true by default as soon as you add a where clause in.

If you want all Posts, regardless of whether there's a user attached but if there is a user then only the ones born in 1984, then add the required field back in:

Posts.findAll({
  include: [{
    model: User,
    where: {year_birth: 1984}
    required: false,
   }]
}).then(posts => {
  /* ... */
});

If you want all Posts where the name is "Sunshine" and only if it belongs to a user that was born in 1984, you'd do this:

Posts.findAll({
  where: {name: "Sunshine"},
  include: [{
    model: User,
    where: {year_birth: 1984}
   }]
}).then(posts => {
  /* ... */
});

If you want all Posts where the name is "Sunshine" and only if it belongs to a user that was born in the same year that matches the post_year attribute on the post, you'd do this:

Posts.findAll({
  where: {name: "Sunshine"},
  include: [{
    model: User,
    where: ["year_birth = post_year"]
   }]
}).then(posts => {
  /* ... */
});

I know, it doesn't make sense that somebody would make a post the year they were born, but it's just an example - go with it. :)

I figured this out (mostly) from this doc:

http://docs.sequelizejs.com/en/latest/docs/models-usage/#eager-loading

edited Mar 9, 2018 at 14:27

answered Jan 6, 2018 at 22:21

Ryan Shillington

25.5k17 gold badges103 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

hanzichi Over a year ago

so what represents posts.user_id = users.id in your sequelize? thanks

Ryan Shillington Over a year ago

@hanzichi That the posts.user_id column matches the users.id column is set up in your sequelize model definition.

Solid_Metal Over a year ago

required false still gave me inner join for some reason

Ryan Shillington Over a year ago

Maybe you spelled required wrong or put it in the wrong place?

Ryan Shillington Over a year ago

@JasonG You want the "attributes" key. Search for "attributes" in this doc: sequelize.org/master/manual/model-querying-basics.html

|

Philipp Kyeck · Accepted Answer · 2017-12-18 09:56:14Z

172

User.hasMany(Post, {foreignKey: 'user_id'})
Post.belongsTo(User, {foreignKey: 'user_id'})

Post.find({ where: { ...}, include: [User]})

Which will give you

SELECT
  `posts`.*,
  `users`.`username` AS `users.username`, `users`.`email` AS `users.email`,
  `users`.`password` AS `users.password`, `users`.`sex` AS `users.sex`,
  `users`.`day_birth` AS `users.day_birth`,
  `users`.`month_birth` AS `users.month_birth`,
  `users`.`year_birth` AS `users.year_birth`, `users`.`id` AS `users.id`,
  `users`.`createdAt` AS `users.createdAt`,
  `users`.`updatedAt` AS `users.updatedAt`
FROM `posts`
  LEFT OUTER JOIN `users` AS `users` ON `users`.`id` = `posts`.`user_id`;

The query above might look a bit complicated compared to what you posted, but what it does is basically just aliasing all columns of the users table to make sure they are placed into the correct model when returned and not mixed up with the posts model

Other than that you'll notice that it does a JOIN instead of selecting from two tables, but the result should be the same

15 Comments

clemlatz Over a year ago

What if I want to join only Users who are born in 1984 ? In SQL I would do : SELECT * FROM posts JOIN users ON users.id = posts.user_id WHERE users.year_birth = 1984

Manwal Over a year ago

All links are dead not :-(

theptrk Over a year ago

Did that question ever get posted in an answered question?

antew Over a year ago

Do we need both of them or one is enough : User.hasMany(Post, {foreignKey: 'user_id'}) Post.belongsTo(User, {foreignKey: 'user_id'})

Ryan Shillington Over a year ago

@antew When you call User.hasMany(Post) you add methods / attributes to the User object, and when you call Post.belongsTo(User) you add methods to the Post class. If you're always calling from one direction (ex. User.getPosts()) then you don't need to add anything to the Post object. But it's nice to have the methods on both sides.

|

Floern · Accepted Answer · 2017-04-11 14:26:44Z

7

Model1.belongsTo(Model2, { as: 'alias' })

Model1.findAll({include: [{model: Model2  , as: 'alias'  }]},{raw: true}).success(onSuccess).error(onError);

edited Apr 11, 2017 at 14:26

Floern

34k24 gold badges106 silver badges122 bronze badges

answered Apr 11, 2017 at 14:25

Raja JLIDI

711 silver badge1 bronze badge

Comments

Renish Gotecha · Accepted Answer · 2019-07-22 16:14:00Z

3

In my case i did following thing. In the UserMaster userId is PK and in UserAccess userId is FK of UserMaster

UserAccess.belongsTo(UserMaster,{foreignKey: 'userId'});
UserMaster.hasMany(UserAccess,{foreignKey : 'userId'});
var userData = await UserMaster.findAll({include: [UserAccess]});

answered Jul 22, 2019 at 16:14

Renish Gotecha

2,56026 silver badges24 bronze badges

Comments

pankajmadaan · Accepted Answer · 2023-02-13 10:04:06Z

0

Create associations in model file and then use include for joins you can use inner includes too join tables.

----model.js

blog1.hasMany(blog2, {foreignKey: 'blog_id'})
blog2.belongsTo(blog1, {foreignKey: 'blog_id'})

-----controller.js

blog2.find({ where: {blog_id:1}, include: [blog1]})

answered Feb 13, 2023 at 10:04

pankajmadaan

112 bronze badges

Collectives™ on Stack Overflow

How to make join queries using Sequelize on Node.js

5 Answers 5

9 Comments

15 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

15 Comments

Comments

Comments

Comments

Linked

Related