Ruby
Article

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"
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:

def self.send_chain(methods)
  methods.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.

  • mpugach

    Thank for the great article, the idea and the nifty inject :)

    Am I able to end up with this?

    User.send_chain(['last', 'admin!'])

    Your code will be copied. Please let those people:
    1) write

    where.not(author: nil)
    where(active: 1)

    2) be aware of autogenerated enum scopes
    3) white list unknown user input which they are going to send into their very application with strong params

    • http://new2code.com/ William Kennedy

      This is a great comment and I wish more people like you would comment on sites where tutorials are posted. The above is an example application and uses an single article model so there is no user input. The main aim is to demonstrate that you can pass an array of methods into a model for a scope which is very useful. In a serious setting, you can simply restrict what methods can be passed in with strong params or with a custom method.

      e.g

      User.send_chain(methods) if methods.permitted?

      Great comment.

  • http://new2code.com/ William Kennedy

    Thanks for commenting. Great point. This is an example application to demonstrate a different way of chaining large sql queries together. Only allowing permitted scopes is best practice. I was unaware of that Gem. I think I will check it out. Thanks for the tip.

  • Daniel

    Aren’t you creating a vector of attack with this technique? What if the _params_ contain `delete_all`?

Recommended

Learn Coding Online
Learn Web Development

Start learning web development and design for free with SitePoint Premium!

Get the latest in Ruby, once a week, for free.