CODEWITHSUNDEEP
ruby-on-rails
grafi33
grafi33

Reputation: 327

How do I order records based on associated records

class Conversation < ApplicationRecord
  has_many :messages, dependent: :destroy
  belongs_to :organization

  has_one :latest_message, -> {
    order(created_at: :desc)
  }, class_name: "Message", foreign_key: "conversation_id"
end

class Message < ApplicationRecord
  belongs_to :conversation
end

How do I order conversation list with the latest_message created_at time.

I tried 

conversations.order("latest_message.created_at desc").to_a

but I'm getting the error 

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "latest_message"
12:33:07 web.1       | LINE 1: ...E "conversations"."organization_id" = $1 ORDER BY latest_mes...
12:33:07 web.1       |                                                              ^
12:33:07 web.1       | 
12:33:07 web.1       | from /home/rafi/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/activerecord-6.0.2.2/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params'
12:33:07 web.1       | Caused by PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "latest_message"
12:33:07 web.1       | LINE 1: ...E "conversations"."organization_id" = $1 ORDER BY latest_mes...
12:33:07 web.1       |                                                              ^

possibly because the latest message is not a present in DB; but only in the model.

Upvotes: 0

Views: 55

Answers (1)

max
max

Reputation: 101811

In ActiveRecord the arguments to .where, .group and .order need to be the actual table names and not associations as they generate SQL.

You don't really need that association at all though:

Conversation.joins(:messages)
            .group(:id) 
            .order("MAX(messages.created_at) DESC")

You may need to use .group('conversations.id') on some drivers like TinyTDS that will produce an ambiguous GROUP BY id query instead of GROUP BY "conversations"."id". If you want to include conversations that don't have any messages use left_joins:

Conversation.left_joins(:messages)
            .group(:id) 
            .order("MAX(messages.created_at) DESC")

Upvotes: 1

Related Questions