Rails Scopes, Using Strings for Queries

Scopes in Rails are great, but there is a certain way to write scopes that will prevent debugging and possible hair-loss in the future. If you practice TDD, this seems ‘premature’ but I see it as writing the solution to the problem differently, not prematurely.

You will experience problems with scopes when you start to create advanced queries, queries that leverage joins and use strings.

For example, let’s look at the Post model as used on the Ruby On Rails Guide:

class Post < ActiveRecord::Base
scope :created_before, ->(time) { where("created_at < ?", time) }

This will work great, until we perform a query like the following:
Since we are working with the Post object, we can use the created_before scope. However, when we look at the actual query executed, we will see the following:

SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE (created_at < '2014-05-06')

As you see, the created_at is not provided a table name, so it isn’t ‘scoped’ to the Posts table, so it will throw the following error (I’m using SQLite for the example):

SQLite3::SQLException: ambiguous column name: created_at

If this were a scope that simply needed the Posts on a specific date, we could bypass the string query and use the assignment type, by using the hash syntax:
class Post < ActiveRecord::Base
scope :created_before, ->(time) { where(created_at: time) }

But it’s not, unfortunately.
So, the way to solve this issue, is to limit your scopes to the table your working on.

class Post < ActiveRecord::Base
scope :created_before, ->(time) { where("posts.created_at < ?", time) }

By adding the “posts.” to created_at, we can now use this scope in a joins clause, or in another crazy query that is still built on top of ActiveRecord and it will not break anything due to the nature of how ActiveRecord generates queries.
TL;DR: Add the “<table_name>.” to the column names in your scopes that use strings. I’ve posted the project on GitHub, for testing pleasure.

Leave a Reply

Your email address will not be published.