CODEWITHSUNDEEP
sqlruby-on-railsactiverecordareldistinct-on
t6d
t6d

Reputation: 2623

How to use "DISTINCT ON" in conjunction with Arel / ActiveRecord?

I have a Message ActiveRecord Model and I want to select all messages that have a distinct conversation_partner_id. I'm currently specifying the DISTINCT ON clause as a string but I was wondering if it could be provided as an Arel expression instead and whether there would be a benefit. My statement looks as follows:

Message.select('DISTINCT ON ("messages"."conversation_partner_id") messages.*').from(t)

where t is a union of the messages table and specified as a somewhat complex Arel expression. I leave it out as it is independent of the SELECT issue and works just fine.

The following code does not work:

Message.select([
  Arel::Nodes::DistinctOn.new(Message.arel_table[:conversation_partner_id]),
  Message.arel_table[Arel.star]
]).from(t)

It results in malformed SQL as it places a comma after the DISTINCT ON () clause:

SELECT DISTINCT ON ("messages"."conversation_partner_id"), "messages".* ...
                                                         ^

Upvotes: 5

Views: 2096

Answers (1)

Elena Unanyan
Elena Unanyan

Reputation: 1209

If you try this: 

Message.arel_table
       .project(Arel.star)
       .distinct_on(Message.arel_table[:conversation_partner_id])
       .to_sql

You get this:

SELECT DISTINCT ON ( \"messages\".\"conversation_partner_id\" ) * FROM ...

Upvotes: 5

Related Questions