Rails 标量查询

Question

我需要为当前用户（另一名员工）“最喜欢”的员工显示一个 UI 元素（例如星号或复选标记）。

Employee 模型定义了以下关系来支持这一点：

  has_and_belongs_to_many :favorites, :class_name => "Employee", :join_table => "favorites",
    :association_foreign_key => "favorite_id", :foreign_key => "employee_id"

favorites 有两个字段：employee_id、favorite_id。

如果我要编写 SQL，以下查询将给出我想要的结果：

SELECT  id, account, 
    IF(
    (
    SELECT  favorite_id
    FROM    favorites
    WHERE   favorite_id=p.id
    AND employee_id = ?
    ) IS NULL, FALSE, TRUE) isFavorite
FROM        employees

'?' 在哪里将被会话[:user_id]取代。

如何在 Rails 中表示 isFavorite 标量查询？

另一种方法将使用如下查询：

SELECT  id, account, IF(favorite_id IS NULL, FALSE, TRUE) isFavorite
FROM        employees e
LEFT OUTER JOIN favorites f ON e.id=f.favorite_id
    AND employee_id = ?

同样，“？”被 session[:user_id] 值替换。

我已经在 Rails 中成功编写了此内容：

ee=Employee.find(:all, :joins=>"LEFT OUTER JOIN favorites ON employees.id=favorites.favorite_id AND favorites.employee_id=1", :select=>"employees.*,favorites.favorite_id")

不幸的是，当我尝试通过将“1”替换为“？”来使此查询“动态”时，我收到错误。

ee=Employee.find(:all, :joins=>["LEFT OUTER JOIN favorites ON employees.id=favorites.favorite_id AND favorites.employee_id=?",1], :select=>"employees.*,favorites.favorite_id")

显然，我的语法错误，但是 :joins 表达式可以是“动态的”吗？这是 Lambda 表达式的情况吗？

我确实希望向此查询添加其他过滤器，并将其与 will_paginate 和acts_as_taggable_on 一起使用（如果这会产生影响）。

编辑错误：

尝试使 :joins 动态化时

ActiveRecord::ConfigurationError: Association named 'LEFT OUTER JOIN favorites ON employees.id=favorites.favorite_id AND favorites.employee_id=?' was not found; perhaps you misspelled it?
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/associations.rb:1906:in `build'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/associations.rb:1911:in `build'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/associations.rb:1910:in `each'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/associations.rb:1910:in `build'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/associations.rb:1830:in `initialize'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:1789:in `new'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:1789:in `add_joins!'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:1686:in `construct_finder_sql'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:1548:in `find_every'
    from /Users/craibuc/.gem/ruby/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:615:in `find'

Answer 1

试试这个：

ee=Employee.all( 
  :select=>"employees.*,favorites.favorite_id",
  :joins=>"LEFT OUTER JOIN favorites AS favorites 
           ON employees.id=favorites.favorite_id AND
              favorites.employee_id = #{session[:user_id]}")

或者准确地说：

joins = Employee.send(:sanitize_sql_array, 
             ["LEFT OUTER JOIN favorites AS favorites 
               ON employees.id=favorites.favorite_id AND
                  favorites.employee_id = ? ", session[:user_id]
             ])

ee=Employee.find(:all, 
  :select=>"employees.*,favorites.favorite_id",
  :joins=> joins )

第二种方法解决了 SQL 注入问题。

编辑 1

要在 irb 中测试这些调用，请执行以下操作：

通过创建哈希来模拟会话对象：

>> session = {:user_id => "1" }
session = {:user_id => "1" }
=> {:user_id=>"1"}

执行查找器：

>> ee=Employee.find(:all, 
      :select=>"employees.*,favorites.favorite_id",
      :joins=>"LEFT OUTER JOIN favorites AS favorites 
               ON employees.id=favorites.favorite_id AND
                  favorites.employee_id = #{session[:user_id]}")

Answer 2

我想这两种方式都是可能的，但通常情况下，我会将条件放在 WHERE 子句中 (:conditions)：

ee = Employee.find(:all,
    :select => 'employees.*, favorites.favorite_id',
    :conditions => ['favorites.employee_id = ?', 1],
    :joins => 'LEFT OUTER JOIN favorites ON employees.id = favorites.favorite_id'
)

Answer 3

:joins 需要原始字符串或符号（关联名称）或关联数组。所以那里不可能有动态条件。

请参阅此处的参数部分。