Mysql – Problem with adding custom sql to finder condition

activerecordMySQLruby-on-railssql

I am trying to add the following custom sql to a finder condition and there is something not quite right.. I am not an sql expert but had this worked out with a friend who is..(yet they are not familiar with rubyonrails or activerecord or finder)

status_search = "select p.*
               from policies p
               where exists
                   (select 0 from status_changes sc
                   where sc.policy_id = p.id
                   and sc.status_id = '"+search[:status_id].to_s+"'
                   and sc.created_at between "+status_date_start.to_s+" and "+status_date_end.to_s+")
               or exists
                   (select 0 from status_changes sc
                   where sc.created_at =
                       (select max(sc2.created_at)
                       from status_changes sc2
                       where sc2.policy_id = p.id
                       and sc2.created_at < "+status_date_start.to_s+")
                   and sc.status_id = '"+search[:status_id].to_s+"'
                   and sc.policy_id = p.id)" unless search[:status_id].blank?

My find statement:
Policy.find(:all,:include=>[{:client=>[:agent,:source_id,:source_code]},{:status_changes=>:status}],
:conditions=>[status_search])

and I am getting this error message in my log:

ActiveRecord::StatementInvalid (Mysql::Error: Operand should contain 1 column(s): SELECT DISTINCT `policies`.id FROM `policies`  LEFT OUTER JOIN `clients` ON `clients`.id = `policies`.client_id WHERE ((((policies.created_at BETWEEN '2009-01-01' AND '2009-03-10' OR policies.created_at = '2009-01-01' OR policies.created_at = '2009-03-10')))) AND (select p.*
               from policies p
               where exists
                   (select 0 from status_changes sc
                   where sc.policy_id = p.id
                   and sc.status_id = '2'
                   and sc.created_at between 2009-03-10 and 2009-03-10)
               or exists
                   (select 0 from status_changes sc
                   where sc.created_at =
                       (select max(sc2.created_at)
                       from status_changes sc2
                       where sc2.policy_id = p.id
                       and sc2.created_at < 2009-03-10)
                   and sc.status_id = '2'
                   and sc.policy_id = p.id))  ORDER BY clients.created_at DESC LIMIT 0, 25):

what is the major malfunction here – why is it complaining about the columns?

Best Answer

The conditions modifier is expecting a condition (e.g. a boolean expression that could go in a where clause) and you are passing it an entire query (a select statement).

It looks as if you are trying to do too much in one go here, and should break it down into smaller steps. A few suggestions:

  • use the query with find_by_sql and don't mess with the conditions.
  • use the rails finders and filter the records in the rails code

Also, note that constructing a query this way isn't secure if the values like status_date_start can come from users. Look up "sql injection attacks" to see what the problem is, and read the rails documentation & examples for find_by_sql to see how to avoid them.