CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
Skiapex
Skiapex

Reputation: 153

PG::UndefinedTable: ERROR: missing FROM-clause entry for table when using SELECT DISTINCT ON

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

Upvotes: 1

Views: 1228

Answers (1)

MurifoX
MurifoX

Reputation: 15109

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.

Upvotes: 4

Related Questions