Geek Answers Handbook

The returned array of objects through named_scope - has_many ... belongs_to association; UNION ALL request

I am looking for an answer that will return an array of user objects through (preferably) named_scope or through a class method in the User model that does some manipulation.

So, without further ado ...

I have two tables: users and fights.

  • The user has many fights (has_many: fights ,: foreign_key => 'challenger_id or challengee_id')
  • The fight belongs to the user (belongs to_to: challenger ,: class_name => 'User' ... belongs toto: challengee ,: class_name => 'User')

The fight has the following columns of concern:

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

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

  • If the user is the challenger and challenger_won = true, then he is considered a victory.
  • If the user is a challenge, and challenger_won = false, then it is considered a victory.
  • If challenger_won = null, just ignore it.

I have the original SQL statement that returns the fighter attribute (user_id), grouped by most win attributes:

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 information, how can I return an array of user objects through (preferably) named_scope or through a class method in the User model that does some manipulation?

+5
source share
2 answers

, - , :

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

win_count User. setter , win_count , .

+4

, ( mysql)

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

More articles:


All Articles