title: Rails scalar query
body: <p>I need to display a UI element (e.g. a star or checkmark) for employees that are 'favorites' of the current user (another employee).</p>
  
  <p>The Employee model has the following relationship defined to support this:</p>
  
  <pre><code>  has_and_belongs_to_many :favorites, :class_name =&gt; "Employee", :join_table =&gt; "favorites",
      :association_foreign_key =&gt; "favorite_id", :foreign_key =&gt; "employee_id"
  </code></pre>
  
  <p>The favorites has two fields: employee_id, favorite_id.</p>
  
  <p>If I were to write SQL, the following query would give me the results that I want:</p>
  
  <pre><code>SELECT  id, account, 
      IF(
      (
      SELECT  favorite_id
      FROM    favorites
      WHERE   favorite_id=p.id
      AND employee_id = ?
      ) IS NULL, FALSE, TRUE) isFavorite
  FROM        employees
  </code></pre>
  
  <p>Where the '?' would be replaced by the session[:user_id].</p>
  
  <p>How do I represent the isFavorite scalar query in Rails?</p>
  
  <p>Another approach would use a query like this:</p>
  
  <pre><code>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 = ?
  </code></pre>
  
  <p>Again, the '?' is replaced by the session[:user_id] value.</p>
  
  <p>I've had some success writing this in Rails:</p>
  
  <pre><code>ee=Employee.find(:all, :joins=&gt;"LEFT OUTER JOIN favorites ON employees.id=favorites.favorite_id AND favorites.employee_id=1", :select=&gt;"employees.*,favorites.favorite_id")
  </code></pre>
  
  <p>Unfortunately, when I try to make this query 'dynamic' by replacing the '1' with a '?', I get errors.</p>
  
  <pre><code>ee=Employee.find(:all, :joins=&gt;["LEFT OUTER JOIN favorites ON employees.id=favorites.favorite_id AND favorites.employee_id=?",1], :select=&gt;"employees.*,favorites.favorite_id")
  </code></pre>
  
  <p>Obviously, I have the syntax wrong, but can :joins expressions be 'dynamic'?  Is this a case for a Lambda expression?</p>
  
  <p>I do hope to add other filters to this query and use it with will_paginate and acts_as_taggable_on, if that makes a difference.</p>
  
  <p><strong>edit</strong></p>
  
  <p>errors from trying to make <code>:joins</code> dynamic:</p>
  
  <pre><code>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'
  </code></pre>