Jeff Dean

Advanced acts_as_list scope with multiple columns

March 23, 2008

This blog post was written in 2008. Information and links in this post may be outdated.

I just ran across a problem that I'm sure very few other people will encounter, but if I can help just one googler....

Scenario

You are using acts_as_list, and you want to scope your list by more than one column and one of the columns is a reserved word. You went to the rails documentation page for acts_as_list

class Element < ActiveRecord::Base
   belongs_to :page
   acts_as_list :scope => 'page_id = #{page_id} and `group` = \'#{group}\' '
end

Whoa - that's some ugly code. Let's see what's happening:

  • The string you pass in to :scope is used to dynamically create a method called "scope_condition" with the contents of the string
  • The :scope has to be in single quotes so that the contents of the #{} blocks don't get evaluated
  • The word "group" has to be in special quotes, otherwise it will throw a MYSQL error

There are a number of problems with the code above - namely:

  • The combination of single, double and angled quotes makes it very difficult to read
  • The angled-quotes will not work for every database adapter
  • The string contains #{} blocks, but they are not evaluated - instead they are evaluated later

The fix

Making the code database-independent is easy. Rails provides a method on every connection called "quote_column_name" which adds the correct quotes for whichever database adapter you are using. The connection object is a part of every Active Record instance, so the following line does the trick:

class Element < ActiveRecord::Base
  belongs_to :page
  acts_as_list :scope => 'page_id = #{page_id} AND #{connection.quote_column_name("group")} = \'#{group}\' '
end

Making the code easier to read is almost as simple - just define your own "scope_condition" method. The final code will look like this:

class Element < ActiveRecord::Base
  belongs_to :page
  acts_as_list

  # scope_condition for acts_as_list
  def scope_condition
    "page_id = #{page_id} AND #{connection.quote_column_name("group")} = #{quote_value(group)}"
  end
end

Now we're talkin'. The scope_condition is now database-independent, the quotes make sense and programs like TextMate will highlight the syntax appropriately.

References

Tags