named_scope or find_by_sql?

Question

I have three models:

• User
• Award
• Trophy

The associations are:

• User has many awards
• Trophy has many awards
• Award belongs to user
• Award belongs to trophy
• User has many trophies through awards

Therefore, user_id is a fk in awards, and trophy_id is a fk in awards.

In the Trophy model, which is an STI model, there's a trophy_type column. I want to return a list of users who have been awarded a specific trophy -- (trophy_type = 'GoldTrophy'). Users can be awarded the same trophy more than once. (I don't want distinct results.)

Can I use a named_scope? How about chaining them? Or do I need to use find_by_sql? Either way, how would I code it?

Answer 1

I am always comfortable with the "find_by_sql" You can use it Using find_by_sql as follows

User.find_by_sql("select u.id, u.name, t.trophy_type 
                    from users u, awards a, trophies t
                    where a.user_id=u.id and 
                    t.trophy_id=a.id and 
                    t.trophy_type = 'GoldTrophy'"
                 )

I am not sure using "named_scope" But try this

class User < ActiveRecord::Base

    named_scope :gold_trophy_holder, 
                :select=>" users.id, users.name, trophies.trophy_type",       
                :joins => :awards, "LEFT JOIN awards ON awards.id = trophies.award_id"
                :conditions => ['trophies.trophy_type = ?', 'GoldTrophy']

end

Answer 2

If you want to go down the named_scope route, you can do the following:

Add a has_many :users to Trophy, such as:

has_many :users, :through => :awards

And the following named_scope:

named_scope :gold, :conditions => { :trophy_type => 'GoldTrophy' }

You can call the following:

Trophy.gold.first.users

You need to call '.first' because the named_scope will return a collection. Not ideal. That said, in your case it's probably perfectly appropriate to use neither find_by_sql or named_scope. How about using good old:

Trophy.find_by_trophy_type('GoldTrophy').users

This will do exactly what you want without having to dig down into the SQL.