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.
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)
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.