While using IN clause in find_by_sql's parametrized query, I got the error message from MySQl. The idea is to put a join on number of tables describe as:
contacts -> id, status_id profiles -> id, contact_id, first_name, last_name subprofiles -> id, profile_id, title, level_id, company_id companies -> id, company_name levels -> id, name
contacts has one profile contacts_users has many subprofiles
The ruby statements looks like:
if params[:company].blank? && params[:title].blank? && checkempty && params[:last_name].blank? && params[:first_name].blank? return (flash[:notice] = "Enter atleast one search parameter!") end query_builder = "SELECT * FROM `contacts` WHERE 1=1 AND `contacts`.`status_id` = 1 "
if !params[:company].blank? || !params[:title].blank? || !checkempty
!params[:first_name].blank? || !params[:last_name].blank?
query_builder += " AND `contacts`.`id` IN ( SELECT `contact_id` FROM `profiles` WHERE 1=1" if !params[:company].blank? || !checkempty || !params[:title].blank? query_builder += " AND `profiles`.`id` IN ( SELECT `profile_id` FROM `subprofiles` WHERE 1=1 " if !params[:company].blank? query_builder += " AND `subprofiles`.`company_id` IN ( SELECT `id` FROM `companies` WHERE `company_name` like '%#{params[:company]}%' )" end if !checkempty query_builder += " AND `subprofiles`.`level_id` IN ( SELECT `id` FROM `levels` WHERE `levels`.`name` IN #{get_level_values} )"
end if !params[:title].blank? query_builder += " AND `subprofiles`.`title` like '%#{params[:title]}%'" end query_builder+=")" end unless params[:first_name].blank? query_builder += " AND `profiles`.`first_name` like '%#{params[:first_name]}%'" end unless params[:last_name].blank? query_builder += " AND `profiles`.`last_name` like '%#{params[:last_name]}%'" end query_builder += ")" end
puts "---------------------------------------------#{query_builder}---------------------------------------------" @records = Contact.find_by_sql(query_builder)
Providing all the parameters, the resultant Query looks like: SELECT * FROM `contacts` WHERE 1=1 AND `contacts`.`status_id` = 1 AND `contacts`.`id` IN ( SELECT `contact_id` FROM `profiles` WHERE 1=1 AND `profiles`.`id` IN ( SELECT `profile_id` FROM `subprofiles` WHERE 1=1 AND `subprofiles`.`company_id` IN ( SELECT `id` FROM `companies` WHERE `company_name` like '%sadd%' ) AND `subprofiles`.`title` like '%sad%') AND `profiles`.`first_name` like '%sad%' AND `profiles`.`last_name` like '%sad%')
The statement runs on mysql v. "5.1.30-community-log" (comes with wamp) perfectly fine, but the hosting guys r still sticked with v "4.0.27-standard". Now when I run the script there, mysql return error saying: "Mysql::Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `contact_id` FROM `profiles` WHERE 1=1 AND `profiles`....."
Seems like the subquery is returning NULL and in version 5.1, it returns an empty string '' so that in bigger picture the outer queries are not affected, while in older v 4.0.2 it returns NOTHING so that it returns this error. Please, remedy the problem by giving the subsitute in orm or improving the query so that it could run on older version aswell, owing to the fact, it would be nearly imposible for the hosting guys to update the mysql version.