Forum: Ruby on Rails Alas! Poor old MySql version 4.0.2 and the IN Clause

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Abm A. (Guest)
on 2009-05-18 08:31
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.
Marnen L. (Guest)
on 2009-05-18 18:49
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
removed_email_address@domain.invalid
This topic is locked and can not be replied to.