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.
Let’s say, we have a
Listing down the schema for these models.
Get all posts of users with age < 20
To get the posts of users with age < 20, we can write SQL something like,
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.
It fires two queries.
- One to fetch user ids when
pluckis applied on
- Another query is fired on
postswhen getting posts for the user ids obtained.
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,
It generates single query with a subquery as given below.
ActiveRecord by default does subquerying part.
It gets the
primary_key of the ÅctiveRecord Relation passed for subquerying.
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.
The above code will result in a query given below.