Rails - May 2009 - Alas! Poor old MySql version 4.0.2 and the IN Clause

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.
-- 
Posted via http://www.ruby-forum.com/.

Abm Abm wrote:
> 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.
Find a new host.  Seriously.  MySQL 5 has been out for a long time, and 
if your host can''t support it, then they are not doing their job.

MySQL 5 is bad enough as a DB server; mySQL 4.0 is obsolete, owing to 
the lack of essential features such as subqueries. Your host should at 
least be able to upgrade to mySQL 4.1, which does support subqueries.

Better yet, forget about mySQL entirely and use PostgreSQL.  It''s a 
*much* better database server, and you''ll have fewer headaches.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.