Rails + postgres: Using Group query on a has_many through

Question

I'm struggling with understanding how group works in Rails. There doesn't really appear to be any good tutorials either...

class Doctor
  has_many :appointments
  has_many :patients, through: :appointments
end

class Appointment
  has_many :doctors
  has_many :patients
end

class Patient
  has_many :appointments
  has_many :doctors, through: :appointments
end

The Doctor class has a field primary_doctor. A patient can have many doctors, but only one primary_doctor.

Given a specific doctor, I want a list of all patients that doctor sees, grouped by the primary_doctor for each patient.

doctor.patients.joins(:appointments).where(appointments: { is_primary: true }).group("patients.id, appointments.doctor_id")

is what I feel should work, but that doesn't do any grouping. If I add a .count to the end, it almost gives me what I want, but instead of the actual objects, I get a hash of {doctor_id=>patient_count}.

Thoughts? Thanks!

I think you need to select the fields you need after grouping. — Alfie
– Alfie, Commented Apr 28, 2016 at 14:40

Woahdae · Accepted Answer · 2016-04-28 15:33:43Z

3

If I understand your question correctly, you need to be using Ruby's in-memory group_by function. Unless I've missed something the last 10 years, ActiveRecord can't marshal a database query straight into the type of representation you're looking for.

So, to get a list of all patients that doctor sees, grouped by the primary_doctor for each patient, you could do:

doctor.patients.joins(:appointments).where(appointments: { is_primary: true }).
  group_by(&:primary_doctor)

This would give you a result like:

{
  <Doctor id: 1, name: "Dr Bob"> =>
    [<Patient id: 1, name: "Joe">,
     <Patient id: 2, name: "Jane">],
  <Doctor id: 2, name: "Dr Spock"> =>
    [<Patient id: 3, name: "Jack">,
     <Patient id: 4, name: "Jill">,
     <Patient id: 5, name: "Scotty">]
}

Note, this might be mildly inefficient if you have to go back to the database each time to get the primary_doctor, so if this is a critical path in your app you will probably also use includes (http://apidock.com/rails/ActiveRecord/QueryMethods/includes) somewhere in there.

edited Apr 28, 2016 at 15:33

answered Apr 28, 2016 at 15:10

Woahdae

5,0512 gold badges30 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

max Over a year ago

Are you sure about this? The API documentation says its quite possible to get a collection of records. api.rubyonrails.org/classes/ActiveRecord/…

Woahdae Over a year ago

The collection returned by AR is a single-dimensional array, even in the documentation you linked to, except sometimes in some edge cases like count. You stated you'd like a map (so in Ruby, a hash structure) of primary doctor to an array of patients. SQL group is for compiling many rows into a single representation before marshalling; that's not what you asked for here, at least.

Woahdae Over a year ago

Added sample output to my answer so you can see how it's different than the AR documentation and whether it's what you meant.

XML Slayer Over a year ago

Beautiful, group_by is what I need. So group is really only used for aggregate functions (sum, avg, etc)?

Woahdae Over a year ago

Personally, yes. I recently used it as part of a query to find duplicate records, as another example, but in my experience it's extremely uncommon in standard CRUD operations (as your example seems to be a part of).

Collectives™ on Stack Overflow

Rails + postgres: Using Group query on a has_many through

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related