Sql query Error: Operand should contain 1 column(s) in rails

Question

Hi everyone,

SELECT * 
FROM   `jobs` 
WHERE  (SELECT DISTINCT jobs.* 
        FROM   jobs, job_requests 
        WHERE (jobs.user_id = 1) OR 
              (job_requests.user_id = 1 AND job_requests.job_id =   jobs.id)
       )

This sql gives me:

Mysql::Error: Operand should contain 1 column(s). 

If I execute the select from the where clause it works

SELECT DISTINCT jobs.* FROM jobs, job_requests 
WHERE  (jobs.user_id = 1) OR 
       (job_requests.user_id = 1 AND job_requests.job_id = jobs.id)

Could somebody explain me why? This query is generated by rails activerecord so the main select is needed.

The ror code:

has_many :my_jobs, :class_name=>"Job", :finder_sql =>
    'SELECT DISTINCT jobs.* ' +
    'FROM jobs, job_requests ' +
    'WHERE (jobs.user_id = #{id}) OR ' +
    '(job_requests.user_id = #{id} AND job_requests.job_id = jobs.id AND job_requests.request_status IN ("requested", "confirmed") )'

Answer 1

If I have understood you correctly you need to restructure as

SELECT * FROM jobs 
 WHERE EXISTS 
       (SELECT DISTINCT jobs.* 
          FROM jobs, job_requests 
         WHERE (jobs.user_id = 1) 
            OR (job_requests.user_id = 1 AND job_requests.job_id = jobs.id))

I'm guessing you only want to select from jobs where the inner query is true, the above will do this for you.

Answer 2

Following code will work, if you want to get the jobs with job requests for a given user:

Job.all( :joins      => :jobs_requests, 
         :conditions => ["job_requests.user_id = ?", user_id])

Answer 3

I am submitting my answer based on the additional information provided. The query below should address your requirement.

Job.all(:conditions=> [ " 
 jobs.user_id = ? OR 
 EXISTS (
   SELECT * 
   FROM   job_requests AS B
   WHERE  B.job_id = jobs.id AND B.user_id = ?
 )", user_id, user_id ]
)

If you want an efficient version of the same query then you should go with UNIONs.

sql = "
  SELECT * 
  FROM   jobs A WHERE A.user_id = ?
 UNION
  SELECT * 
  FROM   jobs A, job_requests B 
  WHERE  A.id = B.job_id AND B.user_id = ?
 "    
Job.find_by_sql(Job.send(:sanitize_sql_array, [sql, user_id, user_id]))

The first query can be converted to a named_scope.

class Job < ActiveRecord::Base

  named_scope :for_user, lambda { |user_id| { :conditions=> [ " 
     jobs.user_id = ? OR 
     EXISTS (
       SELECT * 
       FROM   job_requests AS B
       WHERE  B.job_id = jobs.id AND B.user_id = ?
     )", user_id, user_id ] }
  }
end

Now you can use the named_scope as follows:

Jobs.for_user(current_user)