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?

Consider changing the challenger_won column in your fights table, to a winner_id (user_id fk) that will make finding wins a lot cleaner. You could then have a has_many :wins, :class_name => 'Fight', :foreign_key => 'winner_id' which will allow you to do user.wins.count to find total wins — joshnuss

Stim Stim · Accepted Answer · 2011-02-10T13:46:54

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.

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

2 Answers