Active Record Joins

Here's a quick reference to running join operations in Rails. See Guide to Web Development for a full course on web development.

You can use Active Record joins to query one model based on data from a related table. For example you can return specific categories based on each category's products.

Technical note: joins in Rails runs an SQL 'inner join' operation and returns the records from the model you're operating on.

Models

The previous page discussed setting up relationships between different models. We'll run queries on those models, so here's their code again:

class Category < ActiveRecord::Base
  has_many :products
end

class Product < ActiveRecord::Base
  belongs_to :category
  has_many :likes
end

class User < ActiveRecord::Base
  has_many :likes
  has_many :liked_products, through: :likes, source: :product
end

class Like < ActiveRecord::Base
  belongs_to :user
  belongs_to :product
end

Here's sample data for categories and products:

Simple Joins

A simple joins statement lets you return records that have an associated record.

Category.joins(:products)

SQL: SELECT "categories".* FROM "categories" INNER JOIN "products" ON "products"."category_id" = "categories"."id"

This query returns all categories that have products. It will return a category for every match it finds, which means there can be many duplicates.

id	name
1	animals
2	foods
1	animals
1	animals

To get rid of duplicates, use uniq:

 Category.joins(:products).uniq

This will return all categories that have products, without duplicating any categories.

id	name
1	animals
2	foods

You can also run the query in reverse:

Product.joins(:category)

SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories"."id" = "products"."category_id"

This will return all products that have a category. .joins works for all the Rails relationship types.

Multiple Associations

Here's some new sample data:

You can run a join with multiple tables:

Product.joins(:category, :likes) 

This will return all products that have a category and at least one like. To avoid returning duplicate products, you can again use .uniq:

Product.joins(:category, :likes).uniq 

SELECT DISTINCT "products".* FROM "products" INNER JOIN "categories" ON "categories"."id" = "products"."category_id" INNER JOIN "likes" ON "likes"."product_id" = "products"."id"

This chart summarizes the different join queries you can run on products:

Nested Associations

Sometimes you'll want to get records based on a nested association:

Category.joins(products: :likes).uniq

This returns all categories that have products that were liked.

id	name
1	animals
2	foods

SELECT DISTINCT "categories".* FROM "categories" INNER JOIN "products" ON "products"."category_id" = "categories"."id" INNER JOIN "likes" ON "likes"."product_id" = "products"."id"

You could also run a query based on multiple associations. For example, let's say Product has_many reviews and likes.

Category.joins(products: [:reviews, :likes]).uniq

This query would return all categories with products that have reviews and likes.

where

Before looking at more advanced joins queries, let's look at querying a single model.

.where lets you retrieve all items that match a specific condition. For example, this query will return all products with a quantity of 0:

Product.where(quantity: 0)

SELECT "products".* FROM "products" WHERE "products"."quantity" = ? [["quantity", 0]]

You can also specify a range:

Product.where(quantity: 2..5)

Sometimes, you'll need to pass in a string condition to .where which will be directly converted into SQL:

Product.where('quantity > 3')

SELECT "products".* FROM "products" WHERE (quantity > 3)

This will return all products with a quantity greater than 3.

(Note: Be careful not to put user input inside such a query String. See the Rails Guide for more info.)

Conditional Joins

The goal of joins is often to get one model based on specific conditions in another model. For example, you may want to get all Categories that have products with a quantity equal to 0:

Category.joins(:products).where(products: {quantity: 0})

SELECT "categories".* FROM "categories" INNER JOIN "products" ON "products"."category_id" = "categories"."id" WHERE "products"."quantity" = ? [["quantity", 0]]

You can pass in any SQL conditions as a string:

Category.joins(:products).where('products.quantity > 3')

SELECT "categories".* FROM "categories" INNER JOIN "products" ON "products"."category_id" = "categories"."id" WHERE (products.quantity > 3)

This returns all categories that have products with a quantity greater than 3.

As before, you'll generally want to add .uniq to these queries avoid returning duplicate categories:

Category.joins(:products).where('products.quantity > 3').uniq