Select from multiple tables in sequelize

Question

I'm struggling in how to select from two tables using the Sequelize.

Actually I'm trying to do it:

SELECT * FROM users, clients WHERE user.id = clients.user_id

I have no idea how to user two tables as I described, the only thing I did that got some results were:

const clients = await Client.findAll({
    attributes: ["user_id"],
});

const users = [];
for (const client of clients) {
    let user = await User.findAll({
        where: {
            id: {
                [Op.eq]: client.user_id
            }
        }
    });
    users.push(user);
}

Which return me something:

[
    [
        {
            "id": 1,
            "first_name": "Velda",
            "middle_name": "Zboncak",
            "last_name": "Kris",
            "email": "vkris10@hotmail.com",
            "created_at": "2020-02-07T20:09:29.484Z",
            "updated_at": "2020-02-07T20:09:29.484Z"
        }
    ]
];

Answer 1

Model and Assossiation

First of all, you need to create the correct associations in the model of your table. In this case for the User and the Client, it's supposed to be an Client.belongsTo(...)

Take a look at User model:

const { Model, DataTypes } = require("sequelize");
class User extends Model {
    static init(sequelize) {
        super.init({
            first_name: DataTypes.STRING,
            middle_name: DataTypes.STRING,
            last_name: DataTypes.STRING,
            email: DataTypes.STRING
        }, { sequelize });
    }
}
module.exports = User;

Take a look at Client model:

const { Model, DataTypes } = require("sequelize");

class Client extends Model {
    static init(sequelize) {
        super.init({
            user_id: DataTypes.INTEGER // The foreign key
        }, { sequelize });
    }

    static associate(models) {
        Client.belongsTo(models.User, {
            foreignKey: "id", // Column name of associated table
            as: "user" // Alias for the table
        });
    }
}
module.exports = Client;

When associating tables you need to have in mind those values inside the associate method, being the foreignKey: "id" the column name inside the models.ModelName, which will be used to make the joins, and the as: "user" which are used as an alias for the table like SELECT t.column1 FROM table AS t;

Controller

Okay, now you have set your models, you need to set your controller, where the magic happens. As you said you want to fetch results using:

SELECT * FROM users, clients WHERE user.id = clients.user_id

But to achieve the same result you can follow the sql join method to fetch the results from db, so it will be something like this:

SELECT
    "user"."first_name", "user"."middle_name", "user"."last_name", "user"."email"
FROM "clients" AS "client"
LEFT JOIN "users" AS "user"
ON "client"."id" = "user"."id";

Knowing that we can talk about including tables in sequelize, which is the same as associations

const Client = require("./path/to/models/Client");
module.exports = {
    async fetchAll(req, res) {
        const results = await Client.findAll({
            limit: 25,
            include: [
                {
                    association: "user",
                    attributes: ["first_name", "middle_name", "last_name", "email"]
                }
            ]
        });
        return res.json(results);
    },
};

Now lets talk about what is going on in the code:

• The Model.findAll({}) will fetch all the result inside the specified table, in this case clients table.
• The limit: 25 will limit your results in only 25 rows, you are free to remove or edit as you need.
• The include: [], it will do the joins through the tables you specify, as you need only the users table, we are going to use only one object, so the assossiation: "user" will make this connection between tables, you must use the same alias you set inside the model. And at least the attributes: ["columns"] is where you set all the fields you want to fetch.

And that's it, you make you request, and the result of this will be exactly the same join as I mentioned. And the results will be:

[
    {
        "id": 1,
        "user_id": 1,
        "user": {
            "first_name": "John",
            "middle_name": "Ironsight",
            "last_name": "Doe",
            "email": "johndoe@example.com"
        }
    }, {...}
]

Answer 2

Can use where in include. Find the document at here

let user_id = client.user_id;
users = await User.findAll({
    include: [
        {
            model: Client,
            as: 'client',
            where: {
                user_id: user_id
            }
        }
    ]
});