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.