Home Count distinct records while plucking from another table
Reply: 2

Count distinct records while plucking from another table

Meltemi Published in 2017-11-11 01:32:14Z

Given Rails app where:

class Team < ActiveRecord::Base
  belongs_to :captain, class_name: User

I'm trying to succinctly query database and get a summary of Captains and the number of Teams they belong to:

[[23, "Pat", "Smith"]]  Teams: 1
[[46, "Kate", "Jones"]]  Teams: 1
[[107, "John", "Doe"]]  Teams: 3

I can get a hash with each Captain's ID and the number of teams:

> Team.group(:captain_id).count
=> {85=>3, 106=>1, 81=>1, 25=>1, 32=>1, 8=>3, 1=>1, 79=>2, 26=>1}

But then it gets ugly. The following works but seems ugly and has n+1 SQL queries:

Team.group(:captain_id).count.sort.to_h.each { |key,val| puts "#{User.where(id: key).pluck(:id, :first, :last)}  Teams: #{val}" }

What's the Rails way?

cschroed Reply to 2017-11-11 02:00:26Z

Since you are trying to get detailed info about users, you may want to define a relation like this on your User model:

class User < ActiveRecord::Base
  has_many :captained_teams, foreign_key: :captain_id, class_name: Team

Then you can work with User for your query:

User.joins(:captained_teams).includes(:captained_teams).each do |user|
  puts "#{[user.id, user.first, user.last]}  Teams: #{user.captained_teams.length}"
soyphea Reply to 2017-11-12 06:18:53Z

sql = "**Select count then group by then having count(...)**"

result = ActiveRecord::Base.connection.execute(sql);

You can use the raw sql to get what you want.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310829 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO