finder_sql does not parse string with Rails

Question

I have the problem that the query I use for finder_sql is not parsed correctly before it is handed over to PostgreSQL resulting in a database syntax error.

To illustrate the problem I just used the example code from here:

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

I only changed class_name to "User" as I don't have a person model but this does not matter here.

has_many :subscribers, :class_name => "User", :finder_sql =>
'SELECT DISTINCT people.* ' +
'FROM people p, post_subscriptions ps ' +
'WHERE ps.post_id = #{id} AND ps.person_id = p.id ' +
'ORDER BY p.first_name'

When I use this, I get the following error:

User Load (0.3ms)  SELECT DISTINCT people.* FROM people p, post_subscriptions ps WHERE
ps.post_id = #{id} AND ps.person_id = p.id ORDER BY p.first_name
PGError: ERROR:  Syntaxerror near »{« 
LINE 1: ...ople p, post_subscriptions ps WHERE ps.post_id = #{id} AND p...
                                                         ^

As you can see #{id} is not replaced with the id of the object which then raises the PostgreSQL error.

Environment

• Rails 3.1
• rvm
• PostgreSQL 9.1
• Ubuntu 11.10
• Ruby 1.9.2p290 (2011-07-09 revision 32553) [x86_64-linux]

Answer 1

I think what you're actually looking for is this:

has_many :posts, :finder_sql =>
    proc {"SELECT p.* from posts p join topics t on p.topic_id = t.id where t.id=#{id}"}

As of Rails 3.1 you have to use a proc instead of a string to use fields like #{id}.

See the issue here: https://github.com/rails/rails/issues/3920

Answer 2

The documentation for :finder_sql is woefully incomplete and the example code is broken. As you have discovered, this:

has_many :subscribers, :class_name => "User", :finder_sql =>
  'SELECT DISTINCT people.* ' +
  'FROM people p, post_subscriptions ps ' +
  'WHERE ps.post_id = #{id} AND ps.person_id = p.id ' +
  'ORDER BY p.first_name'

will not work and, based on the ActiveRecord source, cannot work. If you check the source, you'll see things like this:

def custom_finder_sql
  interpolate(options[:finder_sql])
end

and then interpolate does this:

def interpolate(sql, record = nil)
  if sql.respond_to?(:to_proc)
    owner.send(:instance_exec, record, &sql)
  else
    sql
  end
end

so if your :finder_sql is just a string (such as in the example), then it is used as-is with no interpolation at all and you end up with broken SQL. If you want the interpolation, then you'll have to get interpolate to enter the first branch so you'd want a lamba for :finder_sql and a double quoted string inside the lambda so that #{id} will work:

has_many :subscribers, :class_name => "User", :finder_sql => ->(record) do
      "SELECT DISTINCT people.* " +
      "FROM people p, post_subscriptions ps " +
      "WHERE ps.post_id = #{id} AND ps.person_id = p.id " +
      "ORDER BY p.first_name"
end

That should get into the first branch inside interpolate so that the instance_exec call will be evaluated and interpolate the string within the context of the object in question. I'm not sure when record will not be nil so you might want this instead:

has_many :subscribers, :class_name => "User", :finder_sql => ->(record) do
      record = self if(record.nil?)
      "SELECT DISTINCT people.* " +
      "FROM people p, post_subscriptions ps " +
      "WHERE ps.post_id = #{record.id} AND ps.person_id = p.id " +
      "ORDER BY p.first_name"
end

And while we're here, please use explicit join conditions instead of implicit ones:

has_many :subscribers, :class_name => "User", :finder_sql => ->(record) do
      record = self if(record.nil?)
      "SELECT DISTINCT people.* " +
      "FROM people p " +
      "JOIN post_subscriptions ps on p.id = ps.person_id " +
      "WHERE ps.post_id = #{record.id} " +
      "ORDER BY p.first_name"
end

The blog you found about single/double quotes and :finder_sql:

http://tamersalama.com/2007/05/17/finder_sql-single-vs-double-quotes/

is out of date and doesn't seem to apply to Rails 3+. The excerpts above are from 3.1 but the behavior you're seeing indicates that the code and behavior probably changed in 3.0 but the documentation wasn't updated.