0

Having trouble with the below query:

  • I have a surveys table
  • The surveys table has a foreign key to a contact (via contact_id)
  • There are mutiple surveys per contact
  • The survey has a column called scheduled_at with time data

I want a query off Surveys with one instance per contact, where that survey has the recent most scheduled_at compared to other instances with the same contact foreign key.

While this seems like a good SQL answer, wondering if there is a cleaner ActiveRecord solution?

Community
  • 1
  • 1
barnett
  • 1,572
  • 2
  • 13
  • 25

1 Answers1

1

Try running following.

@contact.surveys.order(scheduled_at: :desc).first(5)

This will return the 5 most recent surveys of that order.

Assumption: @contact is the an object of your Contact model

Muhammad Ali
  • 63
  • 5
  • The end goal is only 1 `survey` instance per `contact_id` to be returned and that instance has the most recent `scheduled_at`. This only returns it for one `Contact`. I need it to be `Survey` based. – barnett May 03 '16 at 17:56
  • I am assuming you have `params[:contact_id]` variable and you want most recent survey for this `contact_id` . Try running this code. `Survey.where(contact_id: params[:contact_id]).order(scheduled_at: :desc).first` – Muhammad Ali May 03 '16 at 18:16
  • I have no params. I need the most recent survey for each contact, not just one. – barnett May 03 '16 at 18:28
  • Use this `Contacts.find_each do |contact| latest_survey_array << contact.surveys.order(scheduled_at: :desc).first end` I hope this will work for you. `latest_survey_array` will have all the latest surveys for each contact – Muhammad Ali May 04 '16 at 10:28
  • While that retrieves the proper records I want to do so with `ActiveRecord` (as outlined above). – barnett May 07 '16 at 10:59