CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Austin Wang
Austin Wang

Reputation: 919

How to select from a table that has been joined with same model/class/table?

I'm trying to get a count of how many subcontacts every contact has.

Class Contacts  
has_many :subcontacts, class_name: "Contact",foreign_key: "supercontact_id" 
belongs_to :supercontact, class_name:"Contact"

And here's the activerecord part i have so far that's roughly what i'm trying to do. 

Contact.joins{subcontacts.outer}.select(subcontacts.count as subcontact_count)

I think the problem is that the joins portion is looking for a association name and the select part is looking for a table name. The trouble is that the table name is the same table... What's the best way to do this so that it stays as a relation or using SQL so that we can minimize the number of queries so that it isn't an N+1 problem?

Upvotes: 2

Views: 97

Answers (2)

Nitin Kumar
Nitin Kumar

Reputation: 184

Contacts.all.each do |contact|
  puts contact.name =>  contact.subcontacts.count
end

OR

Contacts.all.map{|contact| [contact.name => contact.subcontacts.count]}

The above will provide you the hash like answer{contact_name => subcontacts.count}

Upvotes: 1

user2801
user2801

Reputation: 314

Try using 

results = Contact.joins(:subcontacts).select("count(subcontacts.id) as count, contacts.id").group("contacts.id")

and count can be fetched as 

results.map do |result|
  "Contact ID: #{result.id} - Subcontacts Count: #{result['count']}"
end

Upvotes: 1

Related Questions