CODEWITHSUNDEEP
postgresqlsequelize.js
proton
proton

Reputation: 1787

How to implement JOIN in Sequelize

I have three tables, and the structure is described as follows:

User table contains fields: id, name, date_created

Library table contains fields: id, capacity, date_created, user_id

user_id is a foreign key for User table and each instance of Library must belong to a user.

The last table is Books table which contains fields: id, status, date_created, library_id.

library_id is a foreign key for Library table and each instance of a book must belong to a Library.

What I'm trying to achieve is a way to query the DB for all instances of a book that belongs to user and has a status of for example: borrowed

Upvotes: 1

Views: 11432

Answers (2)

Kshateesh
Kshateesh

Reputation: 609

To implement joins first you need to establish associations between sequelize models. So, in your case

models.Library.hasMany(models.Books, {
    foreignKey: 'library_id'
})

models.Books.belongsTo(models.Library, {
    foreignKey: 'library_id'
})

models.Library.belongsTo(models.User, {
    foreignKey: 'user_id'
})

models.User.hasMany(models.Library, {
    foreignKey: 'user_id'
})

here, models is a sequelize instance. You can find more about sequelize associaitions here http: //docs.sequelizejs.com/manual/tutorial/associations.html

Now, To find all instances of Books that belongs to User

models.User.findAll({
    include: [{
        model: models.Library,
        required: true, // do an INNER Join 
        include: [{
            model: models.Books,
            required: true,
            where: {
                status: 'borrowed'
            }
        }] t
    }],
    where: {
       user_id: 'some_user_id'
    }
})

Upvotes: 4

Vivek Doshi
Vivek Doshi

Reputation: 58613

I think include is what you need , please check below query : 

Books.findAll({
    include : { //<------ By this you can use association
        model : User ,
        where : { id : YOUR_USER_ID }
    },
    where : { status : 'borrowed' }    
}).then(books => {
    if(books.length > 0) {
        console.log(books);
    } else {
        console.log("No books found");
    }
})

Upvotes: 1

Related Questions