Model scope with has_one relationship-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

You should be able to joins the next appointment and check just that record for inclusivity in the range.

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

You need to use appointments in the where clause because that's the table name but it will only be examining next_appointment