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

User · Accepted Answer

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  Further reading    http   docs sequelizejs com en latest docs associations  one-to-one-associations http   docs sequelizejs com en latest docs associations  one-to-many-associations http   docs sequelizejs com en latest docs models-usage  eager-loading

User · Answer

Model1 belongsTo Model2    as   alias      Model1 findAll  include    model  Model2    as   alias        raw  true   success onSuccess  error onError

User · Answer

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

User · Answer

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   gt                      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   gt                      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   gt                      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   gt                      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   gt                      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   gt                      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

[mysql] How to make join queries using Sequelize on Node.js

Examples related to mysql

Examples related to node.js

Examples related to orm

Examples related to sequelize.js