Returning array of objects via named_scope -- has_many...belongs_to association; UNION ALL query

Question

I'm looking for an answer that will return an array of user objects via (preferably) a named_scope or via a class method on the User model that does some manipulation.

So without further ado...

I have two tables: users and fights.

• User has many fights (has_many :fights, :foreign_key => 'challenger_id or challengee_id')
• Fight belongs to user (belongs_to :challenger, :class_name => 'User'...belongs_to :challengee, :class_name => 'User')

Fight has the following columns of concern:

• challenger_id (user_id fk)
• challengee_id (user_id fk)
• challenger_won (boolean)

As you can see, a user can be a challenger or a challengee, but not both.

• If the user is a challenger and the challenger_won = true, then it is considered a win.
• If the user is a challengee and the challenger_won = false, then it is considered a win.
• If the challenger_won = null, then just disregard it.

I have a raw SQL statement that returns a fighter attribute (the user_id) grouped by most wins attribute:

SELECT a.fighter, COUNT(*) AS wins
  FROM (SELECT challenger_id AS fighter
          FROM fights
         WHERE challenger_won = TRUE
        UNION ALL
        SELECT challengee_id AS fighter
          FROM fights
         WHERE challenger_won = FALSE
       ) AS a
 GROUP BY a.fighter;

So given this info, how can I return an array of user objects via (preferably) a named_scope or via a class method on the User model that does some manipulation?

Answer 1

I think you can try something like this to recreate the result of you query:

class User
    named_scope :winners, 
        :select => 'users.*, COUNT(fight.id) AS wins', 
        :join => :fights,  
        :conditions => ['(fights.challenger_id = user_id AND fights.challenger_won = TRUE) OR (fights.challengee_id = user_id AND NOT fights.challenger_won = FALSE)']
        :group => 'user_id'
end

However, for caching puposes, you might want to consider adding a win_count field to the User model. If you create a setter method for the winner of a fight, you can increment the win_count right at that moment when it changes.

Answer 2

Or, without the join (mysql specific)

class User
  named_scope :winners, 
    :select => 'if(challenger_won = FALSE,challenger,challengee) as winner,COUNT(id) AS wins ', 
    :group => 'user_id'
end