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

Question

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") )'

Yes, i added the ror code too. Basically i want to retrieve the jobs where the user is the owner and the jobs where he is applied( has jobs through job_requests) but i want them together for further filtering. — dombesz
– dombesz, Commented Mar 13, 2010 at 0:57

Harish Shetty · Accepted Answer · 2010-03-14 16:07:11Z

1

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)

edited Mar 14, 2010 at 16:07

answered Mar 13, 2010 at 1:34

Harish Shetty

64.4k21 gold badges154 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dombesz Over a year ago

Looks interesting, but my only problem is that i want to further filter the results set with names scopes. Could be applied named scopes here?

CoolBeans · Accepted Answer · 2011-09-18 02:07:03Z

1

SQL query: Documentation

SELECT (
'95.86.122.224', NOW( ) , '95.86.122.224', '95.86.122.224'
)
FROM orders
LEFT JOIN visitors ON visitors.ip = '95.86.122.224'
WHERE visitors.ip IS NULL
LIMIT 0 , 30

MySQL said: Documentation

#1241 - Operand should contain 1 column(s)

edited Sep 18, 2011 at 2:07

CoolBeans

20.9k10 gold badges88 silver badges102 bronze badges

answered Jan 30, 2011 at 8:05

goldi

111 bronze badge

Comments

CResults · Accepted Answer · 2010-03-12 18:24:37Z

0

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.

edited Mar 12, 2010 at 18:24

answered Mar 12, 2010 at 17:43

CResults

5,1051 gold badge24 silver badges29 bronze badges

1 Comment

dombesz Over a year ago

I'm looking for a solution which could be integrated in ror too.

Harish Shetty · Accepted Answer · 2010-03-12 19:58:46Z

0

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])

answered Mar 12, 2010 at 19:58

Harish Shetty

64.4k21 gold badges154 silver badges201 bronze badges

2 Comments

dombesz Over a year ago

Something like this, but the problem is that in this case i wont give back jobs where the user id doesn't match. The criteria is: or the user is owner of the job(jobs.user_id = user_id), or he is applied for a job through job request(job_request.user_id = user_id AND job_request.job_id = jobs.id). Hope this make sense.

Harish Shetty Over a year ago

I have added another answer for your question. It should cover your scenario

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related