1

In Rails, how do I create a scope that filters on a related has_one attribute? I have two models, Patient and Appointment. Patient declares a has_many relationship on Appointments. Now I am adding a next_appointment relationship:

class Appointment < ActiveRecord::Base

end

class Patient < ActiveRecord::Base
  has_many :appointments, class_name: "::Appointment", foreign_key: :patient_id, inverse_of: :patient

  has_one(
    :next_appointment,
    -> { where("appointment_date >= now()").order(:appointment_date).limit(1) },
    class_name: "::Appointment",
    foreign_key: :patient_id
  )

  scope :by_range_next_appointment_date, lambda { |from, to|
    where(...)
  }
end

Now I want to create a scope that returns all patients who have their next appointment within a given range. How can I fill in the where() to accomplish this?

Here's an example to illustrate:

Let's say Bruce Banner has an appointment on 11/2/2021 and Peter Parker has appointments on 10/27/2021 and 11/3/2021. Now I want this scope to return all patients who's next appointment (as of 10/26/2021) which is between 11/1/2021 and 11/7/201. This should only return Bruce Banner since his next appointment is in that range. Peter Parker's next appointment is tomorrow, so he shouldn't be included.

Code-Apprentice
  • 81,660
  • 23
  • 145
  • 268
  • By next - do you mean the "next upcoming"? From example lets say I have one appointment in one week and another in three weeks then should I be included in query for `1.day.from_now..2.weeks.from.now`? – max Oct 27 '21 at 07:49
  • @max Yes, I mean "next upcoming". So in your example, you should be included. However, if you have an appointment tomorrow and I filter on `1.week.from_now..2.weeks.from.now` then you won't be included. – Code-Apprentice Oct 27 '21 at 15:18

2 Answers2

2

A simple polyglot solution would be to use a co-related query:

Patient.where(
  "(
     SELECT MIN(a.appointment_date)
     FROM   appointments a
     WHERE  a.appointment_date >= NOW() 
     AND    a.patient_id = patients.id
  ) BETWEEN ? AND ?", to, from
)

On Postgres you could use a lateral join as a better performing alternative:

Patient.joins(%q{
  JOIN LATERAL(
    SELECT appointment_date
    FROM appointments a
    WHERE a.patient_id = patients.id -- lateral reference
    AND   a.appointment_date >= NOW()
    ORDER BY a.appointment_date
    LIMIT 1
  ) aa ON true
}).where("aa.next_appointment_date" => to..from)
max
  • 96,212
  • 14
  • 104
  • 165
  • Thanks for the options. I'm using Postgres, so I'll look at the second option more closely. – Code-Apprentice Oct 27 '21 at 15:19
  • You can also compose the subquery with ActiveRecord/Arel. https://stackoverflow.com/a/60568914/544825 – max Oct 27 '21 at 15:21
  • Thanks. Found some time to come back to this and saw those small details. Now to incorporate this simplified and somewhat contrived example back into the actual code and database schema... – Code-Apprentice Oct 27 '21 at 20:44
1

Since the appointment_date is in another table, a .join is necessary to access this column. Querying for a range of values can be done by simply passing a Range to hash parameters for where (it will generate the SQL clause using BETWEEN operator).

  scope :by_range_next_appointment_date, lambda { |from, to|
    joins(:appointments).where(appointment_date: from..to)
  }

This should return all patients with appointments in given date range. If you want only future appointments - you can chain additional .where.

  scope :by_range_next_appointment_date, lambda { |from, to|
    joins(:appointments)
      .where(appointment_date: from..to)
      .where("appointment_date >= now()")
  }

Be careful about types of values passed to .where - convert Date values with .to_datetime if the column type is datetime.

Bartosz Pietraszko
  • 1,367
  • 9
  • 9
  • Is there a way to use the `has_many` relationship so I don't have to duplicate `.where("appointment_date >= now()")`? – Code-Apprentice Oct 26 '21 at 23:43
  • Also, this code doesn't quite do what I want. Let me give an example to clarify. Let's say Bruce Banner has an appointment on 11/2/2021 and Peter Parker has appointments on 10/27/2021 and 11/3/2021. Now I want this scope to return all patients who's next appointment (as of 10/26/2021) which is between 11/1/2021 and 11/7/201. This should only return Bruce Banner since his next appointment is in that range. Peter Parker's next appointment is tomorrow, so he shouldn't be included. Your code here will return both patients. – Code-Apprentice Oct 26 '21 at 23:48
  • Thanks for the tips about using `.join()` and date conversion. The conversion is actually even more complex than just date vs datetime but not relevant to this question. – Code-Apprentice Oct 27 '21 at 16:29