views:

71

answers:

3

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'
A: 

I imagine both ways are possible, but normally, I'd stick the condition in the WHERE clause (: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'
)
Samir Talwar
Left outer join returns NULL when no matching rows found. If you move the condition to `where` the logic will not work as expected.
KandadaBoggu
I tried this approach and it didn't, as KandadaBoggu says, return the correct results.
Craig
@Craig: Does it work if you change the conditions to `['favorites.employee_id IS NOT NULL AND favorites.employee_id = ?', 1]`? I ask more for my own education, as you've already got an answer.
Samir Talwar
I tried ["(favorites.employee_id IS NULL OR favorites.employee_id = ?)", 1]. It didn't return the correct results, unfortunately.
Craig
@Craig: sorry, apparently I'm more confused than I thought. Guess I've got some reading to do.
Samir Talwar
@Samir: it was a good idea. other than switching the IS NOT NULL to NOT NULL and adding some (), I thought it would be equivalent. For some reason, it returned *almost* the correct recordset. I wasn't able to determine the cause of the variability.
Craig
A: 

:joins expects either a raw string or a symbol (association name), or an array of associations. So you can't have dynamic conditions there.

See parameters section here.

Voyta
+2  A: 

Try this:

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

Or to be exact:

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 )

Second approach addresses the SQL injection issues.

Edit 1

To test these calls in irb do the following:

Simulate the session object by creating hash:

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

Execute the finder:

>> 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]}")
KandadaBoggu
The first option works great. Thanks.Is there a way to simulate #{session[:user_id]} in the IRB?
Craig
Thanks for the session tip. I'd vote you up a second time if I could.
Craig