Rails: Dynamically Chain Scopes to Clean up SQL Queries

Key Takeaways

Dynamically chaining scopes in Rails can help clean up SQL queries, making them more manageable and readable. This technique involves creating small pieces of code, known as scopes, that can be chained together to form more complex queries.
The Ruby ‘send’ method can be used in combination with Rails scopes to reduce the size of controller code. This is achieved by pushing business logic down into the model and using the controller to decide what information is rendered.
The ‘send_chain’ method can be used to dynamically call as many scopes as required. This method takes an array of methods and calls ‘send’ on each one, allowing for the dynamic chaining of scope methods.
Using a combination of scopes and the Ruby ‘send’ method can make controller actions shorter and easier to refactor. It also promotes code reusability and reduces the likelihood of errors.
Be careful and ensure that you are not opening up a security hole in your application. Ensure that you are only allowing whitelisted methods to be passed into the inject method.

One of the worst things that can happen to a Rails application is SQL queries becoming a huge complex conditional mess. I have come across controller actions that build query strings using a type of “chain of conditionals” method, like so:

sql = "active= 1"
if condition
  sql += "and important=1"
end
 if second_condition
  sql += "and important=1"
end
Article.where(sql)

As applications become more complicated, retrieving the required information can become a hassle as you dip into utilizing SQL queries. This quickly becomes cumbersome when for you want to execute different queries for reporting, etc. As teams get bigger, more staff require greater access to the application data. As the data required becomes more complex, you can quickly end up with a very convoluted controller action and a model laced with special methods.

In this tutorial, I hope to demonstrate how to chain scopes with the Ruby send method to build a smaller controller. As a part of the goal, I’d like to keep the convenience of scopes.

If you are wondering what scopes are, or even pondering what the heck the Ruby send method is, then never fear. They are quite simple.

An Active Record scope is a Proc that you create inside a model used just like a method call:

class Article < ActiveRecord::Base
  enum status: [ :draft, :pending_review,:flagged, :published]

  scope :drafts, -> { (where("`status` = ? ", 0)) } # 0 is :draft in the enum
end

drafts = Article.drafts

The Ruby send method is a way to send messages to a Ruby object. You can think of send as just another way to call a method on an object:

drafts = Article.send("drafts")

If it doesn’t make sense, don’t worry. I am going to touch on this again so you get a better idea. Let’s build a miniature application to learn more about these two concepts.

To start, we are going to build a simple blog application:

$ rails new blog

Change into that directory and generate a quick blog scaffold with lots of attributes:

$ rails generate scaffold Article title:string description:text status:integer author:string website:string meta_title:string meta_description:text

Migrate the database:

$ rake db:migrate

With our very basic scaffold, we just need to populate it with data. For this, I highly recommend the Faker gem. In your Gemfile, add the following:

gem 'faker'

Go ahead and bundle install to grab the gem.

Now in your db/seeds.rb file, add the following to generate a dataset:

10.times do
  Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 0)
  Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 1)
  Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 2)
  Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 3)
end

10.times do
 Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 0,:website => Faker::Internet.domain_name)
 Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 1,:author => Faker::Name.first_name)
 Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 2,:meta_title => Faker::Lorem.word)
 Article.create(:title => Faker::Lorem.word, :description => Faker::Lorem.sentence, :status => 3,:meta_description => Faker::Lorem.sentence)
end

In the terminal, we need to type in the following to populate our development database:

$ rake db:seed

Fire up the server (rails s), visit the /articles page, and you’ll see lots of different articles with totally random names. With some data in place, we can now start utilizing scopes to retrieve relevant data from our application. In our app/models/article.rb file, add an enum and some scopes, like so:

 class Article < ActiveRecord::Base
   enum status: [ :draft, :pending_review,:flagged, :published]

   scope :with_author, -> {(where("`author` IS NOT NULL ")) }
   scope :with_website, -> {(where("`website` IS NOT NULL ")) }
   scope :with_meta_title, -> {(where("`meta_title` IS NOT NULL ")) }
   scope :with_meta_description, -> {(where("`meta_description` IS NOT NULL")) }
end

Before we build the user-facing part of the reporting application, I always like to make sure that things works from the Rails console. Go back to the terminal, fire up a Rails console, and test out one of the new scopes:

$ rails c
> Article.draft
=> #<ActiveRecord::Relation [#<Article id: 1, title: "labore", description: "Tempora debitis nihil illum vel vero suscipit cupi...", status: "draft", created_at: "2016-09-04 12:15:39", updated_at: "2016-09-04 12:15:39", author: nil, website: nil, meta_title: nil, meta_description: nil>]

A great thing about scopes is that we can chain them together to build a bigger SQL statement:

> Article.published.with_meta_title
=> #<ActiveRecord::Relation [#<Article id: 1, title: "labore", description: "Tempora debitis nihil illum vel vero suscipit cupi...", status: "draft", created_at: "2016-09-04 12:15:39", updated_at: "2016-09-04 12:15:39", author: nil, website: nil, meta_title: "A meta Title to remember", meta_description: nil>]

Note: The status enum adds a scope for each value of the enum, which is where we get Article.published.

Hopefully, you are beginning to see the power of scopes. Since scopes are exactly like methods, we can take advantage of the incredibly powerful Ruby send method which allows us to pass in a method name as a string for an argument:

> Article.send("draft")
=> #<ActiveRecord::Relation [#<Article id: 1, title: "labore", description: "Tempora debitis nihil illum vel vero suscipit cupi...", status: "draft", created_at: "2016-09-04 12:15:39", updated_at: "2016-09-04 12:15:39", author: nil, website: nil, meta_title: nil, meta_description: nil>]

However, chaining methods together using the send method is a little more difficult. We need to create another method for our model to be able to chain methods dynamically. In app/models/article.rb, add the following and be sure to only allow certain methods:

def self.send_chain(methods)
  allowed_methods = ['with_author', 'with_website', 'with_meta_title', 'with_meta_description'] 
  methods.filter {|method| allowed_methods.include? method }.inject(self, :send)
end

This method takes an array of methods and calls send on each one. `send_chain allows us to dynamically call as many scopes as we want. For example:

> Article.send_chain(["with_author", "pending_review"])
=> #<ActiveRecord::Relation [#<Article id: 82, title: "quia", description: "Adipisci nisi tempora culpa atque vel quo.", status: "pending_review", created_at: "2016-09-04 12:16:37", updated_at: "2016-09-04 12:16:37",. .  .]

Allow me now to demonstrate how we can use this in our views and our controller. At the top of app/views/articles/index.html.erb, put in the following:

<%= form_tag("/articles", method: "get") do %>
  With Author<%= check_box_tag "article[methods][]", "with_author"  %>
  Pending Review<%= check_box_tag "article[methods][]", "pending_review"  %>
  Draft<%= check_box_tag "article[methods][]", "draft"  %>
  Flagged<%= check_box_tag "article[methods][]", "flagged"  %>
  Published<%= check_box_tag "article[methods][]", "published"  %>
  With Website<%= check_box_tag "article[methods][]", "with_website"  %>
  With Meta Title<%= check_box_tag "article[methods][]", "with_meta_title"  %>
  With Meta Description<%= check_box_tag "article[methods][]", "with_meta_description"  %>
 <%= submit_tag("Search") %>
<% end %>

Now for the real magic. In app/controllers/articles_controller.rb, change the index action to match what I have here:

def index
  if params[:article]
    methods = params[:article][:methods]
    @articles = Article.send_chain(methods)
  else
    @articles = Article.all
  end
end

The action will build a huge SQL query simply by ticking the boxes. If you tick all the boxes, you’ll see the following in your Rails development logs:

Processing by ArticlesController#index as HTML
 Parameters: {"utf8"=>"✓", "article"=>{"methods"=>["with_author", "pending_review", "draft", "flagged", "published", "with_website", "with_meta_title", "with_meta_description"]}, "commit"=>"Search"}
 Rendering articles/index.html.erb within layouts/application
 Article Load (1.0ms)  SELECT "articles".* FROM "articles" WHERE (`author` IS NOT NULL ) AND (`status` = 1 ) AND (`status` = 0 ) AND (`status` = 2 ) AND (`status` = 3 ) AND (`website` IS NOT NULL ) AND (`meta_title` IS NOT NULL ) AND (`meta_description` IS NOT NULL)
Rendered articles/index.html.erb within layouts/application (60.8ms)

Conclusion

A lot of Rails applications I have encountered in the wild have used huge controller actions that try and dictate what to return. I have even seen patterns where people build strings to pass into the Rails where argument:

sql = "active =1"
if condition
  sql += "and important=1"
end
... tons of other conditions ...

Article.where(sql)

With scopes, we are just chaining the methods together to create a SQL query on the fly:

scope :active, -> {(where("`active` = 1")) }
scope :important, -> {(where("`important` = 1")) }

We can then use these scopes to build the same SQL query in a clean, intuitive manner:

if condition
  query = Article.active.important
end

In our example blog application, combining the Ruby send method with Rails scopes reduced the size of the controller code, which in turn made it easier to refactor. This is because business logic is pushed down into the model and the controller is being used to decide what information is rendered. Usually, with the Ruby send method, we can only send one message to the object. Adding a way to chain methods together (send_chain) allows us to dynamically chain scope methods together as well, making our controllers a bit leaner:

$ methods = ["with_author", "pending_review", "draft", "flagged", "published", "with_website", "with_meta_title", "with_meta_description"]
$ Article.send_chain(methods)

Hopefully, I have made a good case for combining scopes and the Ruby send method to shorten the size of your controller actions. If you have any feedback, I would love to hear in the comments below.

Frequently Asked Questions (FAQs) on Dynamically Chaining Scopes to Clean Up Large SQL Queries

What is the concept of dynamically chaining scopes in SQL queries?

Dynamically chaining scopes in SQL queries is a technique used to clean up large SQL queries and make them more readable and manageable. This technique involves creating small, reusable pieces of code (known as scopes) that can be chained together to form more complex queries. This approach not only makes your code cleaner and easier to understand, but it also promotes code reusability and reduces the likelihood of errors.

How can I create a scope in Rails?

In Rails, you can create a scope using the scope method inside your model. The scope method takes two arguments: the name of the scope and a lambda function that contains the query you want to encapsulate. Here’s an example:

class Post < ApplicationRecord
scope :published, -> { where(published: true) }
end

In this example, published is a scope that returns all published posts.

How can I chain scopes together?

You can chain scopes together by calling them one after the other. Each scope narrows down the result set of the previous scope. Here’s an example:

Post.published.recent

In this example, the published scope is chained with the recent scope to return all published posts that are recent.

Can I use conditions with scopes?

Yes, you can use conditions with scopes. You can do this by passing arguments to the lambda function in your scope. Here’s an example:

class Post < ApplicationRecord
scope :published_in_year, ->(year) { where(published: true, year: year) }
end

In this example, the published_in_year scope takes a year as an argument and returns all posts published in that year.

What are the benefits of using scopes?

Scopes offer several benefits. They make your code cleaner and more readable by encapsulating complex queries into reusable pieces of code. They promote code reusability, as you can use the same scope in different parts of your application. They also reduce the likelihood of errors, as you’re less likely to make mistakes when writing complex queries.

Can I chain scopes with different models?

Yes, you can chain scopes with different models. This is possible because scopes return ActiveRecord relations, which can be chained with other scopes, even those defined in different models. However, you need to ensure that the models are related in some way, either through associations or joins.

What is the difference between a class method and a scope?

Both class methods and scopes in Rails can be used to define custom queries. However, there are some differences. Scopes are always chainable and will always return an ActiveRecord relation, even when there are no records found. On the other hand, class methods are more flexible and can return anything, but they may break the chainability if they return nil or false.

Can I use scopes with associations?

Yes, you can use scopes with associations. This allows you to write more complex queries that involve multiple models. For example, you can define a scope in one model and then use it in the context of an association with another model.

How can I handle optional parameters in scopes?

You can handle optional parameters in scopes by using conditional logic in your lambda function. If the parameter is nil or not provided, you can return all records. Here’s an example:

class Post < ApplicationRecord
scope :published_in_year, ->(year) { year ? where(published: true, year: year) : all }
end

In this example, the published_in_year scope returns all posts if the year is not provided.

Can I use scopes in controllers?

Yes, you can use scopes in controllers. This allows you to keep your controllers thin by moving complex queries to the model layer. You can call a scope in a controller action just like any other class method.