Writing subqueries can be a tedious job, when working with Rails ActiveRecord. Rails ActiveRecord can handle subqueries. Subqueries can be written by passing ActiveRecord Relation in where clause. This article discusses how to write subqueries with ActiveRecord in Rails.

Subqueries with ActiveRecord is less used feature. Coming from SQL background, people prefer writing code given below.

Schema

Let’s say, we have a Post and User models. Listing down the schema for these models.

• post belongs_to user.

class Post < ApplicationRecord {
                       :id => :integer,
                  :user_id => :integer,
                    :title => :string,
              :description => :text,
               :created_at => :datetime,
               :updated_at => :datetime
}

• user has_many posts.

class User < ApplicationRecord {
                       :id => :integer,
                     :name => :string,
                    :email => :text,
                    :age   => :integer,
       :encrypted_password => :string,
               :created_at => :datetime,
               :updated_at => :datetime
}

Aim

Get all posts of users with age < 20

To get the posts of users with age < 20, we can write SQL something like,

SELECT * FROM posts WHERE user_id IN (SELECT id FROM users WHERE age < 20);

When implementing this with Rails ActiveRecord ORM, there are two logical steps.

• Get user ids with the age < 20.
• Query posts table with the user ids obtained in step 1.

Post.where(user_id: User.where('age < ?', 20).pluck(:id))

Problem

It fires two queries.

• One to fetch user ids when pluck is applied on User.
• Another query is fired on posts when getting posts for the user ids obtained.

Solution

Rails ActiveRecord ORM supports subqueries. We just have to pass ActiveRecord Relation (AREL) in order to achieve the subquery.

The same code can be re-written as,

Post.where(user_id: User.where('age < ?', 20))

It generates single query with a subquery as given below.

Post Load (1.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (SELECT "users"."id" FROM "users" WHERE (age < 20))

ActiveRecord by default does subquerying part. It gets the primary_key of the ActiveRecord Relation passed for subquerying.

Summary

• Subqueries can be performed by passing ActiveRecord relation in where clause against required column.

• Nesting of subqueries can be obtained with this workflow.

• If we pass incorrect column, the query will result in an error when executed against database. E.g.

Post.where(title: User.where('age < ?', 20))

The above code will result in a query given below.

Post Load (1.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."titile" IN (SELECT "users"."id" FROM "users" WHERE (age < 20))