Problem with adding custom sql to finder condition

Question

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?

Answer 1

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.

Answer 2

Ok, I've managed to retool this so it is more friendly to a conditions modifier and I think it is doing the sql query correctly.. however, it is returning policies that when I try to list the current status (the policy.status_change.last.status) it is set to the same status used in the query - which is not correct

here is my updated condition string..

status_search = "status_changes.created_at between ? and ? and status_changes.status_id = ?) or 
      (status_changes.created_at = (SELECT MAX(sc2.created_at) FROM status_changes sc2 
              WHERE sc2.policy_id = policies.id and sc2.created_at < ?) and status_changes.status_id = ?"

is there something obvious to this that is not returning all of the remaining associated status changes once it finds the one in the query?

here is the updated find..

Policy.find(:all,:include=>[{:client=>[:agent,:source_id,:source_code]},:status_changes],
  :conditions=>[status_search,status_date_start,status_date_end,search[:status_id].to_s,status_date_start,search[:status_id].to_s])