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