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.