CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlarel
svoop
svoop

Reputation: 3454

DISTINCT and ORDER

Here's one tricky relation:

I'm having the following AR relation:

# Doctor
has_many :patients, through: :meetings, uniq: true
has_many :meetings

@doctor.patients works as expected, nothing spectacular there. But now I'd like to get the patients for a doctor ordered by the created_at of the assignment (newest first).

# Doctor
has_many :patients, through: :meetings, uniq: true, order: 'meetings.created_at DESC'
has_many :meetings

Now that doesn't work (on Postgres 9): "ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list". Alright let's do that:

# Doctor
has_many :patients, through: :meetings, select: 'DISTINCT patients.*, meetings.created_at', order: 'meetings.created_at DESC'
has_many :meetings

This works again, but patients are not unique anymore since the DISTINCT is working on the entire SELECT clause.

Is it possible to fetch all patients for a doctor with the duplicate patients removed but still ordere by the newest meeting of each patient in one JOINed query?

Thanks for your hints!

UPDATE:

Using Oliver's hint I've updated the relation as follows:

# Doctor
has_many :patients,
  through: :assignments,
  select: 'patients.*, MAX(assignments.created_at) AS last_assignment', 
  group: 'patients.id',
  order: 'last_assignment DESC',
  counter_sql: proc { "SELECT COUNT(DISTINCT patients.id) FROM patients INNER JOIN assignments ON patients.id=assignments.patient_id WHERE assignments.doctor_id=#{id}" }

The separate counter_sql apears to be necessary since Rails replaces the entire select clause to build the SQL for #count.

Any way to make this less bulky?

Upvotes: 0

Views: 340

Answers (2)

maniek
maniek

Reputation: 7307

Try this:

has_many :patients, 
    through: :meetings, 
    order: 'meetings.created_at DESC', 
    conditions: 'row_number() over(partition by meetings.doctor_id, meetings.patient_id) order by meetings.created_at desc) = 1'

another attempt:

has_many :patients, 
    through: :meetings, 
    order: 'max(meetings.created_at) DESC', 
    group: 'patients.*',
    select: 'patients.*'

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112279

Try using 

    MAX(meetings.created_at)

everywhere. Like this, you get unique patient records, even if patients have several meetings. My example will select the latest meeting. Use MIN(meetings.created_at) if you prefer to select the first one instead.

Upvotes: 3

Related Questions