CODEWITHSUNDEEP
ruby-on-railsjoingroup-by
Dodinas
Dodinas

Reputation: 6805

Rails - Get Column Value with Group By

I have the following tables with entries that might look like:

 Table: sessions

 ID | CREATED_AT              | UPDATED_AT              | USER_ID | GROUP_ID 
 ---|-------------------------|-------------------------|---------|----------  
 27 | 2014-07-01 23:02:16     | 2014-07-01 23:03:18     | 1       | 1     
 28 | 2014-07-02 16:55:25     | 2014-07-02 17:31:40     | 1       | 2       
 29 | 2014-07-07 20:31:13     | 2014-07-07 20:34:17     | 1       | 3        

 Table: groups

 ID | NAME              | CREATED_AT              | UPDATED_AT             
 ---|-------------------|-------------------------|------------------------
 1  | Marching          | 2013-12-17 19:45:28     | 2013-12-17 19:45:28    
 2  | Reaching          | 2014-02-07 17:29:59     | 2014-02-07 17:29:59    
 3  | Picking           | 2014-03-11 21:38:56     | 2014-03-11 21:38:56

And I have the following query in Rails:

 Session.joins(:group).select('groups.name').where(:user_id => 1).group('sessions.group_id').count

Which returns the following keys and values:

  => {2=>7, 1=>3, 3=>1}  (The "key" is the group_id and the "value"
                          is the # of times it occurs).

My question is: Instead of returning the "id" as the key, is it possible for me to return the groups.name instead? Which would look like:

  => {"Reaching"=>7, "Marching"=>3, "Picking"=>1}

If not, would I have to loop through and re-query again based on each group_id?

Thanks very much.

Upvotes: 0

Views: 1188

Answers (1)

Eyeslandic
Eyeslandic

Reputation: 14900

This should work if you have everything set up in your models.

data = Group.joins(:sessions).select('name, count(*) as occurrence_count').where('sessions.user_id = ?', 1).group('groups.name')

Then you can access it like this

data.first.occurrence_count

Upvotes: 2

Related Questions