Evaluating instance variables in the has_many :finder_sql option

If you are one of the Ruby On Rails developers , and  trying to make a complex has_many relation , you have one option .. the :finder_sql and :count_sql options.

these options allows you to write a custom query for your relation maintaining the remaining association options.

So if you were quering using the instance variables of the object like this ,

:finder_sql => “select * from attendance_items a where a.employee_id = #{id}”

This will give you wrong results , even more if your query was like this

:finder_sql => “select * from attendance_items a, where a.email = #{email}”

where email is an attribute in Employee… you will get a syntax error !!!

Cause : writing variables in the query between double quotes , makes the variable to be expanded once it was parsed , so in the first case the returned  id will be the object id in the memory not the active record id and in the second case , it will find that no attribute is name email as the object was not loaded.

Solution : Simply replace the double quotes with single quotes , this cause rails to skip expanding the variables.

:finder_sql => ’select * from attendance_items a ‘+
‘where a.employee_id = #{self.hr_id}’