Gotcha with hand-rolled sql in rails associations – eg and has_many

Sometimes in rails associations (eg has_many) you can’t quite do what you want with the existing options and have to do a bit of hand-rolled sql. And, often you want to refer to the object that the association will be called on. BUT – the following doesn’t work. (i’ve used a trivial example here, we wouldn’t normally hand-roll in this case)

has_many :foos, :finder_sql => "select * from foos where bar_id = #{}"

The reason this doesn’t work is that the string has double quotes, which means that #{} will be evaluated when the string is evaluated, which, because this is a class method, is at class load time! That means that will come out as the id of the class – not what we want.

To get around this, use single quotes instead:

has_many :foos, :finder_sql => 'select * from foos where bar_id = #{}'

With single quotes, the #{} block isn’t evaluated and sits there, waiting for the association to be called. When that happens, the finder_sql is combined, and the eval block evaluated, and at that point self IS the current instance, and the association will work. Bear in mind that, because we’re now using single quotes to wrap the string, any single quotes you might have in your sql will now need to be escaped:

has_many :foos, :finder_sql => 'select * from foos where flavour = \'chicken\' and bar_id =#{}'

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s