How to build conditional Where queries of ActiveRecord and Mongoid
In Ruby on Rails, the default choice for object-relational mapping (ORM) is ActiveRecord, which provides an interface to interact with relational databases. ActiveRecord is widely used in Rails applications and offers a convenient way to define models, handle database operations, and establish relationships between different entities.
On the other hand, Mongoid is an alternative ORM specifically designed for MongoDB, a NoSQL database. Mongoid provides a more object-oriented approach to working with MongoDB and allows developers to interact with the database using Ruby objects and queries.
With both of these ORMs one difficult problem to tackle is to build queries with dynamic where clauses.
Solution #1 — Not Recommended
@posts = Post.all
@posts = @posts.where(published: params[:published]) if params[:published]
@posts = @posts.where(author: params[:author]) if params[:author]
@posts = @posts.where(author: params[:author]) if params[:author]
@posts = @posts.where(created_at: params[:created_at]) if params[:created_at] # If the params[:created_at] is a DateTime
One simple solution is to get all the records and then put where clauses subsequently as above code. But this is messy, hard to read, and gives the impression that all of the records related to the model have been queried.
Solution #2 — Recommended
The below way of adding conditions is a better way to write the code and it uses a hash to store the conditions and at the end, it uses Ruby’s spat operation to send all the conditions that are in the hash to the query.
conditions = {}
conditions.merge!(published: params[:published]) if params[:published]
conditions.merge!(author: params[:author]) if params[:author]
conditions.merge!(author: params[:author]) if params[:author]
conditions.merge!(created_at: params[:created_at]) if params[:created_at] # If the params[:created_at] is a DateTime
# Use spat operator as below
@posts = Post.where(**conditions)
# NOTE: Here I have used merge to add items to the "conditions" Hash.
# This can be done way you perfer like "conditions[:published] = params[:published]" as well.As you can see the code is much cleaner and easy to read as well. The above code is a generic way of doing this in both ActiveRecord and Mongoid. But this might change in certain situations. Some of the possible situations are discussed in the next section.
Different Syntax (ActiveRecord vs Mongoid)
- Range Operations — As we are using hash as the condition collector we can’t use String like conditions.merge!(“created_at and for Mongoid it has its own operations as shown below.
# I have removed the if conditions so it is easier to view
# ActiveRecord
conditions.merge!(created_at: params[:created_at]) # If the params[:created_at] is a DateTime
conditions.merge!(created_at: ..params[:created_at]) # If the params[:created_at] is a DateTime
conditions.merge!(created_at: ..2.days.ago) # If we want less than a DateTime
conditions.merge!(created_at: 2.days.ago..) # If we want more than a DateTime
# Mongoid
conditions.merge!(created_at: params[:created_at]) # If the params[:created_at] is a DateTime
conditions.merge!(:created_at.lte => params[:created_at]) # If the params[:created_at] is a DateTime
conditions.merge!(:created_at.lt => 2.days.ago) # If we want less than a DateTime
conditions.merge!(:created_at.gt => 2.days.ago) # If we want more than a DateTime
- Negation Operations — In Mongoid has ‘ne’ as a negation operation, this type of behavior can be incorporated as shown below.
# I have removed the if conditions so it is easier to view
# ActiveRecord
# Assume that for negation we have negation_conditions as a variable
negation_conditions.merge!(author: params[:author])
@posts = Post.where(**conditions).where.not(**negation_conditions)
# Mongoid
conditions.merge!(:published.ne => params[:published])
- ‘IN’ like operations — The way to handle a list of values to match a field in a record is different in ActiveRecord and Mongoid.
# I have removed the if conditions so it is easier to view
# ActiveRecord
conditions.merge!(tags: params[:list_of_tags]) # if params[:list_of_tags] is a Array
conditions.merge!(tags: ['ruby', 'rails', 'js', 'sql'])
# Mongoid
conditions.merge!(:tags.in => params[:list_of_tags]) # if params[:list_of_tags] is a Array
conditions.merge!(:tags.in => ['ruby', 'rails', 'js', 'sql'])
These are just a few scenarios where some syntax would be different in this approach when it comes to dynamically adding where clauses. Apart from these, there would be simple and complex scenarios that would be different from ORM to ORM.
Conclusion — I found out this way of doing things is much cleaner and would keep code readability high. Maybe ORMs are needed to accommodate this type of dynamically adding conditions to queries, which would give developers that are learning Ruby, more flexibility.
I will be bringing these kinds of exciting articles where I explore nuggets throughout the Ruby ecosystem. Follow me now for a thrilling ride through all things Ruby 💎🤗
LinkedIn: https://www.linkedin.com/in/randika-banura/
Github: https://github.com/randikabanura
Dynamically Build Where Clauses in Ruby on Rails was originally published in Enlear Academy on Medium, where people are continuing the conversation by highlighting and responding to this story.
