Get the latest child messages and also parent messages that are childless

The following is the Message model

class Message < ApplicationRecord
  belongs_to :parent_message, class_name: 'Message', optional: true
  has_many :child_messages, foreign_key: :parent_message_id, class_name: "Message"
  has_many :message_participants

  scope :latest_messages_by_participant, -> (user_id) do
    select("DISTINCT ON (parent_message_id) messages.*").
        joins(:message_participants).
        where(message_participants: { user_id: user_id }).
        order("parent_message_id, created_at DESC")
  end
end

message_participants has a record for each message and the various people who have sent or received that message. It has a user_id on it.

The problem with the above latest_messages_by_participant scope is that it is able to get all the child messages BUT it gets only the last parent message. That is because we are calling DISINTICT ON on parent_message_id, and for childless parent messages this value is NULL and so it’s just calling distinct on NULL and returns on 1 value (the last childless parent message).

How can I fetch all the latest messages including the latest child messages AND the latest childless parent message in a single query?

I’m using Rails 6 and Postgres 11.

P.S: I should also point out a secondary problem which is that the messages are returned in created_at ASC. The created_at DESC is able to get the latest child message but does not sort the overall collection. I can solve this by calling .reverse, but wondering if there was a way to fix that as well.

Answer

Use a COALESCE expression in DISTINCT ON and ORDER BY.
And sort the result in an outer query to get your desired sort order:

SELECT *
FROM  (
   SELECT DISTINCT ON (COALESCE(m.parent_message_id, m.id))
          m.*
   FROM   messages m
   JOIN   message_participants mp ON ...
   WHERE  mp.user_id = ...
   ORDER  BY (COALESCE(m.parent_message_id, m.id)), created_at DESC
   )
ORDER  BY created_at;

See (with detailed explanation):

• SELECT DISTINCT ON, ordered by another column
• Select first row in each GROUP BY group?

Performance?

For few rows per user and message ID, DISTINCT ON is typically among the fastest possible solutions. For many rows, there are (much) faster ways. Depends on more information, as commented.