How to Perform Multiple Inner Joins in Sequelize Postgresql

Question

Am newbie to RDBMS and Sequelize as well wanted to explore more in those now am struck up with JOINS. I don't know how to perform JOINS via SEQUELIZE. I have 3 tables USERS,ORDERS,PRODUCTS ORDERS table contains USERS,PRODUCTS primary key as its foreign key. Am attaching my model code below User Model

const Sequelize = require('sequelize');
const sequelize = require('../config');
let Users = sequelize.define('users', {
  id : {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: Sequelize.STRING,
  },
  password: {
    type: Sequelize.STRING
  }
});
module.exports = Users;

Products Model

const Sequelize = require('sequelize');
const sequelize = require('../config');
let products=sequelize.define('products', {
  id : {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  category : {
    type: Sequelize.STRING,
    allowNull: false
  },
  name : {
    type: Sequelize.STRING,
    allowNull: false
  },
  price: {
    type: Sequelize.INTEGER,
    allowNull: false
  }
});
module.exports= products;

Orders Model

const Sequelize = require('sequelize');
const sequelize = require('../config');
let users=require('./user');
let products=require('./product');
let orders=sequelize.define('orders', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  user_id: {
    type: Sequelize.INTEGER,
    references: {
        model: 'users',
        key: 'id'
    }
  },
  product_id: {
    type: Sequelize.INTEGER,
    references: {
        model: 'products',
        key: 'id'
    }
  },
  price: {
    type: Sequelize.INTEGER,
    allowNull: false
  }
});
module.exports= orders;

I want this following raw query to be performed via SEQUELIZE

SELECT * FROM ((orders INNER JOIN users ON users.id=orders.user_id) INNER JOIN products ON products.id=orders.product_id);

I have looked at the documentation but i couldn't figure out how to do it. ANy help is appreciated. Thanks

Solution

First thing you need to do is set up your Associations.

So lets break this up in to parts. We know that your ORDERS table contains the id for a USER and a PRODUCT. So this is how you would set up your associations for these tables.

1. I am assuming that a user has many orders. We make the associations in both directions.

   User.hasMany(Orders, {foreignKey: 'user_id'}); Order.belongsTo(User, {foreignKey: 'user_id'});

You have the model correctly defined it seems.

2. Now in order to do a join, after setting up the associations, we want to set up a query to do joins for tables. Now keep in mind this would be done in your controller.

// Make sure you import your models up here to use below

export function getRequestsByWeek(req, res) {
  return order.findAll({
    include: [
      {model: users, attributes: []}, // nothing in attributes here in order to not import columns from users
      {model: products} // nothing in attributes here in order to not import columns from products
    ],
    attributes: ['id'], //in quotes specify what columns you want, otherwise you will pull them all
    // Otherwise remove attributes above this line to import everything. 
  })
    .then(respondWithResult(res))
    .catch(handleError(res));
}

Comments

[Kannan T]

Hi thanks allot for your detailed response but am having few queries don't we need to associate the orders table and products table? Why because i want to fetch few details from product tables such as category,name. So can you help me out?

[Kannan T]

I have tried running without associating orders with products it thrown me an error "products not associated with orders"

[Xavid Ramirez]

That error is because you have not set up the association between Products and Orders. Set it up similar to the way I showed you how to set it up for Users and Orders, but between Product and orders.

[Kannan T]

Thanks its working now i got the solution which i was looking for. But an user can order many products at a time. So how can i make M:M association. Am a newbie to RDBMS so i don't know much about associations and relations too. It would be great help if you send me some tutorials for that

[Xavid Ramirez]

There are a couple of things to take into account. A user can have many orders, but an order can not have many users. Yes there can be many orders, but each order is only bound to one user. If you were to change this to many to many. That would mean an single order has many users in it, but this is not the case. One user has many orders, one order belongs to a single user. Same with the product. You can watch this video here youtube.com/watch?v=isk0JR0t_VQ&t=514s

[Tvde1]

Hi, welcome to StackOverflow! Could you add a description of why your answer solves the problem in the question? I also notice "them all" in your code which is probably a typo.