How transform this find_by_sql to named_scope?

Question

How can I possibly turn into named_scope?

def self.hero_badge_awardees
        return User.find_by_sql("select users.*, awards.*, badges.badge_type 
          from users, awards, badges 
          where awards.user_id = users.id and badges.id = awards.badge_id and badges.badge_type = 'HeroBadge'")
      end

Answer 1

class Badge
  has_many :awards
end

class Award
  belongs_to :badge
  belongs_to :user
end

class User
 has_many :awards
 has_many :badges, :through => :awards
 named_scope :with_badge,
   lambda { |badge_type|
     :include => :badges,
     :conditions => ["badges.badge_type = ?", badge_type]
   }
end

then you can try:

User.with_badge("HeroBadge")

This looks like it should work to me, but I haven't tested it. Hopefully this sparks something for you though.

Answer 2

To specifically answer your question, try this:

class Badge
  has_many :awards
end

class Award
  belongs_to :badge
  belongs_to :user
end

class User
  has_many :awards
  has_many :badges, :through => :awards

  named_scope :hero_badge_awardees,
    :include => [:awards, :badges],
    :conditions => "badges.badge_type = 'HeroBadge'"
end