1

I am trying to get a list of uniq patients who have submitted an esas_assessment, sorted by the time the esas_assessment was created_at; for a clinician that has_many patients :through a table called care_group_assignments.

What I have here was working but stopped working when I made it so that clinicians has_many :esas_assessments, :through => :patients instead of having esas_assessment having a clinician_id field and being tied directly to a clinician. I needed to make this choice so that multiple clinicians could be associated with an esas_assessment.

This is what I had before changing how esas_assessments were associated with clinicians. This was based on this answer.

<% @esas_assessments.order("patient_id, created_at DESC").select('ON (patient_id) *').uniq.limit(99).each_with_index do |esas_assessment, index| %>

This now gives me:

PG::AmbiguousColumn: ERROR: ORDER BY "patient_id" is ambiguous

I don't actually understand why these are ambiguous since neither patients or clinicians have the fields patient_id or created_at.

To try to deal with this I used this answer and added esas_assessment. before the ambiguous fields.

<% @esas_assessments.order("esas_assessment.patient_id, esas_assessment.created_at DESC").select('ON (esas_assessment.patient_id) *').uniq.limit(99).each_with_index do |esas_assessment, index| %>

This gives me error:

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "esas_assessment"

: SELECT  DISTINCT ON (esas_assessment.patient_id) * FROM "esas_assessments"
INNER JOIN "patients" ON "esas_assessments"."patient_id" = "patients"."id"
INNER JOIN "care_group_assignments"
ON "patients"."id" = "care_group_assignments"."patient_id"
WHERE "care_group_assignments"."clinician_id" = $1
ORDER BY esas_assessment.patient_id, esas_assessment.created_at DESC LIMIT 99

I have also tried using this answer since it seems like a similar problem but can't figure it out.

Any help would be great.

Rails 4.1.8, ruby 2.2.1p85, PostgreSQL

Community
  • 1
  • 1
Skiapex
  • 153
  • 3
  • 14

1 Answers1

4

I believe you are just missing an s on your table name: 

<% @esas_assessments.order("esas_assessments.patient_id, 
  esas_assessments.created_at DESC")
  .select('ON (esas_assessments.patient_id) *')
  .uniq.limit(99).each_with_index do |esas_assessment, index| %>

The table name is esas_assessments, not esas_assessment.

MurifoX
  • 14,991
  • 3
  • 36
  • 60
  • a beautifully simple answer - thanks. do you know why it was ambiguous without adding `esas_assessments.` before the column names? – Skiapex Sep 22 '15 at 18:21
  • Well, i assume `@esas_assessments` is already populated by some query. Maybe this query brings a `patient_id` from another table, so when you try to use `order` in a previous `ActiveRecordRelation` it complains that it doesn't know which `patient_id` you are talking about. But this is just a guess anyway. =] – MurifoX Sep 22 '15 at 18:31