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

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:

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to node.js

Hide Signs that Meteor.js was Used Querying date field in MongoDB with Mongoose SyntaxError: Cannot use import statement outside a module Server Discovery And Monitoring engine is deprecated How to fix ReferenceError: primordials is not defined in node UnhandledPromiseRejectionWarning: This error originated either by throwing inside of an async function without a catch block dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.62.dylib error running php after installing node with brew on Mac internal/modules/cjs/loader.js:582 throw err DeprecationWarning: Buffer() is deprecated due to security and usability issues when I move my script to another server Please run `npm cache clean`

Examples related to orm

How to select specific columns in laravel eloquent Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] How to query between two dates using Laravel and Eloquent? Laravel - Eloquent "Has", "With", "WhereHas" - What do they mean? How to Make Laravel Eloquent "IN" Query? How to auto generate migrations with Sequelize CLI from Sequelize models? How to fix org.hibernate.LazyInitializationException - could not initialize proxy - no Session Select the first 10 rows - Laravel Eloquent How to make join queries using Sequelize on Node.js What is Persistence Context?

Examples related to sequelize.js

sequelize findAll sort order in nodejs Prevent Sequelize from outputting SQL to the console on execution of query? How to auto generate migrations with Sequelize CLI from Sequelize models? Sequelize, convert entity to plain object Sequelize OR condition object How to make join queries using Sequelize on Node.js Sequelize.js delete query? How to update a record using sequelize for node?