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

user_6811801 · Answer

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.

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.

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));
}

user_9222349 · Answer

let users=require('./user');
let products=require('./product');

export function getOrders(req, res) {
  return order.findAndCountAll({
    include: [
      {model: users, required: true}, // true for INNER JOIN
      {model: products, required: false} // false for LEFT OUTER JOIN
    ],
them all
    
  })   
}

How to Perform Multiple Inner Joins in Sequelize Postgresql

2 Answers