代码之家 › 专栏 › 技术社区 › dombesz

SQL查询错误:操作数在Rails中应包含1列

activerecord ruby-on-rails mysql sql

dombesz · 技术社区 · 16 年前

选择* 从 jobs 其中(选择不同的作业。* 从作业,作业请求其中(jobs.user_id=1)或 (job_requests.user_id=1和job_requests.job_id=jobs.id) )

这个sql给了我:

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

如果我执行select from the 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)

有人能解释我为什么吗?此查询由rails activerecord生成,因此需要主select。

错误代码:

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

4 回复 | 直到 14 年前

Harish Shetty 16 年前

我将根据提供的其他信息提交我的答复。下面的查询应该满足您的需求。

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

如果您想要同一查询的有效版本,那么应该使用联合。

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

第一个查询可以转换为 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

现在您可以使用命名的范围,如下所示:

Jobs.for_user(current_user)

CoolBeans Jake 14 年前

SQL查询:文档

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说:文档

#1241 - Operand should contain 1 column(s)

CResults 16 年前

如果我对你的理解正确的话,你需要重组为

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

我猜您只想从内部查询为true的作业中进行选择,上面的操作将为您完成此操作。

Harish Shetty 16 年前

如果您想获得 jobs 具有 job requests 对于给定的 user :

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