Perform JOIN query with multiple columns in Sequelize.js

Overview

Sequelize.js is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. One of its strengths is the ability to perform complex SQL queries using a clean and readable API. In this guide, we’ll explore the process of performing join queries with multiple columns in Sequelize. Join queries are an integral part of relational database operations as they allow you to retrieve related data from multiple tables. We’ll discuss how to define associations in Sequelize and then leverage these associations to perform multi-column join queries using the Sequelize ‘include’ option.

Understanding joins is critical for dealing with relational data, and performing this action with Sequelize.js brings both ease and scalability to database operations within a Node.js application.

Defining Models and Associations

Before we can discuss the details of join queries, we first need to define the models and their relationships within Sequelize. Assume we are working with a ‘User’ model and a ‘Project’ model in a many-to-many relationship.

const User = sequelize.define('user', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: Sequelize.STRING
});

const Project = sequelize.define('project', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: Sequelize.STRING
});

User.belongsToMany(Project, { through: 'UserProjects' });
Project.belongsToMany(User, { through: 'UserProjects' });

This setup creates a new ‘UserProjects’ join table which includes foreign keys to both ‘User’ and ‘Project’ tables.

Simple Join Query

With the associations set up, you can perform a simple join query to get users along with the projects they’re associated with.

User.findAll({
  include: [
    {
      model: Project,
      as: 'Projects',
      required: true
    }
  ]
});

This will produce a SQL query that gets all users and includes related project data by performing an INNER JOIN operation based on the foreign keys specified in the ‘UserProjects’ join table.

Multi-Column Join Query

Performing a join through multiple columns, typically to form complex filtering conditions, requires some additional steps. Imagine we need to find projects based on a user’s ‘id’ and ‘name’.

Project.findAll({
  include: [
    {
      model: User,
      as: 'Users',
      where: {
        id: 1,
        name: 'John Doe'
      },
      required: true
    }
  ]
});

This query not only joins ‘User’ and ‘Project’ tables but also filters the projects by specific user typing a combined ‘WHERE’ clause operating on multiple user columns.

Using Aliases and Advanced Filtering

Aliases are crucial when dealing with SQL join queries and Sequelize allows custom naming for joined models for clarity/ease of use. Lets’s realias our User association inside Projects.

Project.findAll({
  include: [
    {
      model: User,
      as: 'Contributors',
      where: {
        name: Sequelize.where(Sequelize.fn('LOWER', Sequelize.col('Contributors.name')), 'LIKE', '%john doe%')
      }
    }
  ]
});

This code uses aliased tables along with the Sequelize functions ‘fn’ and ‘where’ to create a case-insensitive search on user names while joined with a project data.

Handling Joins with Pagination

Accessing large datasets requires pagination which is another aspect we can handle naturally with Sequelize while using joins. We’d add limit and offset to the findAll query, properly adjusting the ‘required’ filter to account for the LEFT OUTER JOIN that ensures complete results for the page boundaries.

User.findAll({
  include: [
    {
      model: Project,
      as: 'Projects',
      where: {
        title: {
          [Sequelize.Op.like]: '%database%'
        }
      }
    }
  ],
  limit: 10,
  offset: 0,
  where: {
    createdAt: {
      [Sequelize.Op.gte]: new Date(new Date() - 24 * 60 * 60 * 1000)
    }
  },
  required: false
});

This customized query combines filtering on the join table, and limiting the number of User rows returned.

Conclusion

In this guide, we covered how you can construct complex join queries with Sequelize.js. Starting from defining associations to the intricate conditions on joined tables, we learned the flexibility Sequelize offers for such operations. Correctly leveraging multi-column joins in rich database-driven applications requires diligence in managing relationships and understanding SQL. With Sequelize.js, this complexity can reside in the user-friendly JavaScript environment, allowing for an effective bridge over traditional Node.js and RDBMS interactions.