Rails - ActiveRecord - Includes vs Joins

Joining tables is a common scenario in a project. We have to join tables for few reasons and there are corresponding joins in the database level. Inner join, full outer join, left outer join, right outer join and cross join are them. How would you do it in Rails / ActiveRecord?

While you can leverage raw SQL, rails provides 2 neat ways to join tables. includes and joins.

joins()

includes()

Practical use

You have 3 models in a SaaS application. User, Role and Organization. User has many Roles and Role belongs to `Organization.

  1. Get all roles with or without users/organizations (left outer join)

     Role.includes(:users, :organizations)
    

    Keep in mind that not always will the included table data exist. You may want to use &. or .try() to skip nil situations.

  2. Get all roles with users/organizations

     Role.includes(:users, :organizations).joins(roles: :organizations)
    

    includes() uses LEFT OUTER JOIN but when you pair it with joins() it becomes an INNER JOIN which is the trick to get the desired result set.

  3. Query through relation ships, you only need Role data

     Role
       .joins(:users, :organizations)
       .where(
         users: { active: true },
         organizations: { active: true }
       )
    

    Keep in mind that if you now do a role.organization.name you will have to pull the additional data using a new query. This is true if lets say in a view you iterate through every role and print users name, organization name etc. In that case just through an includes() which will use eager loading and fetch all data at once.

I think this 3 use cases cover the average uses of joins() and includes().