Rails 8, postgres, distinct on and ordering

Question

This feels so much harder than I think it should!

I have a Rails model: MarketplaceMessage

Every message has an initial_record_id - if it's the first message, it's the ID of the record and if it's a reply it is the first record in the thread.

I want to show the distinct threads, ordered by the most recent message in the threads descending - so:

Message id: 12, initial_record_id: 3
Message id: 11, initial_record_id: 4
Message id: 10, initial_record_id: 10

If it wasn't distinct, it would just be order(id: :desc) - but that returns multiple messages from the thread.

This mostly works - it returns the most recent messages from each thread - but the returned messages are ordered asc:

scope :distinct_threads, -> {
  select("DISTINCT ON (marketplace_messages.initial_record_id) *")
    .order("marketplace_messages.initial_record_id, marketplace_messages.id desc")
}

How do I get this to work and avoid the error:

ActiveRecord::StatementInvalid:
       PG::InvalidColumnReference: ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

I assume I have to use some sql, but would prefer to do it as Railsy as possible

engineersmnky · Accepted Answer · 2025-05-21 18:50:33Z

2

+50

While in the comments I originally recommended:

scope :distinct_threads, -> {
  where(
    id: select(arel_table[:id].maximum).group(:initial_record_id)
  ).order(id: :desc)
}

Result:

SELECT 
  marketplace_messages.*
FROM 
  marketplace_messages
WHERE 
  marketplace_messages.id IN (
   SELECT 
     MAX(marketplace_messages.id) 
   FROM 
     marketplace_messages
   GROUP BY 
     marketplace_messages.initial_id
) 
ORDER BY 
 marketplace_messages.id

If you want to use DISTINCT ON we can do so using a bit of Arel to construct a source subquery as follows:

scope :distinct_threads, -> {
  messages_table = arel_table
  sub_query = messages_table 
     .project(messages_table[Arel.star])
     .distinct_on(messages_table[:initial_record_id])
     .order(messages_table[:initial_record_id].asc, messages_table[:id].desc)

  from(sub_query.as(table_name)).order(id: :desc)
}

This should result in

SELECT 
  marketplace_messages.*
FROM 
  (
    SELECT DISTINCT ON (marketplace_messages.initial_record_id)
      marketplace_messages.*
    FROM 
      marketplace_messages
    ORDER BY 
      marketplace_messages.initial_record_id ASC,  marketplace_messages.id DESC
  ) AS marketplace_messages
ORDER BY 
  marketplace_messages.id DESC

edited May 21 at 18:50

answered May 19 at 19:06

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sethherr May 21 at 16:14

This code exactly solved it! Thanks @engineersmnky

Erwin Brandstetter · Accepted Answer · 2025-05-20 01:49:43Z

2

The query burns down to just:

SELECT *
FROM  ( 
   SELECT DISTINCT ON (initial_record_id)
   FROM   marketplace_messages
   ORDER  BY initial_record_id, id DESC
   ) sub
ORDER  BY id DESC;

The tricky bit is that leading ORDER BY expressions must cover DISTINCT ON expressions. So sort a second time in the outer query.

See:

answered May 20 at 1:49

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Rails 8, postgres, distinct on and ordering

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related