By William Kennedy

Rails: Dynamically Chain Scopes to Clean up SQL Queries

By William Kennedy

Text SQL in white hexagon, repeated on orange background

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"
 if second_condition
  sql += "and important=1"

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

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)

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)

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")) }

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:

def self.send_chain(methods)
  methods.inject(self, :send)

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)
    @articles = Article.all

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)


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"
... tons of other conditions ...


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 =

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.

The most important and interesting stories in tech. Straight to your inbox, daily. Get Versioning.
Login or Create Account to Comment
Login Create Account