20 Oct /Active Record Queries: Specifying Conditions on Associations

Posted by Steph Ricardo

I ran into a ‘gotcha’ this week while setting up an active record query that already had an eager loaded association.

My goal was to only find objects that had an appointment time from the current time until the end of the day. In this case, appointments were the association and the objects were already set up to display in descending order.

Object.where(status: status)
      .includes(:appointments)
      .order('appointments.scheduled_datetime DESC')

The objects also needed to have a status of ‘scheduled’, which was already set to the variable status at the start of the controller action.

Using array conditions in my where statement, I started with the following:

Object.where(status: status)
      .includes(:appointments)
      .where('scheduled_datetime >= ? AND scheduled_datetime <= ?',             
        current_time, end_of_day)
      .order('appointments.scheduled_datetime DESC')

I had set the current_time and the end_of_day time to variables. The string making up the array condition looked good. Yet the above resulted in the following error:

PG::UndefinedTable: ERROR:  missing 
FROM-clause entry for table “appointments”

I realized that a references was needed for the conditions mentioned in the where statement. Using the example above doesn’t join the appointments table.

Object.where(status: status)
      .includes(:appointments)
      .where('scheduled_datetime >= ? AND scheduled_datetime <= ?',             
        current_time, end_of_day)
      .references(:appointments)
      .order('appointments.scheduled_datetime DESC')

Using references allows the query to know that the string in the where statement references appointments. It joins the appointments table to the query.

You may be reading this and wondering, “Couldn’t you also use joins here?”. You are right, you can use joins here, it would look something like this:

Object.where(status: status)
      .joins(:appointments)
      .where(
        'appointments.scheduled_datetime >= ? AND ' + 
          'appointments.scheduled_datetime <= ?',             
        current_time, end_of_day
       )

This looks cleaner, yet I also want to use order here. You cannot simply use order and joins when ordering on associations. You would need to use merge like this:

Object.where(status: status)
      .joins(:appointments)
      .where(
        'appointments.scheduled_datetime >= ? AND ' + 
          'appointments.scheduled_datetime <= ?',             
         current_time, end_of_day
      ).merge(Appointment.order(scheduled_datetime: :desc))

There you have it! If you are using includes in queries don’t forget the references!

TAGS:Rails