Prepend DISTINCT ON in select statement in an active record scope

Question

I am trying to use distinct on in rails with a scope, I've created a method in my model like this:

def self.fetch_most_recent_by_user(scope)
  scope.where(guid: scope.except(:select).select("DISTINCT ON (eld_logs.user_id) user_id, eld_logs.guid").order("user_id, eld_logs.created_at desc").map(&:guid))
end

When I execute this I get and error like:

TestModel.fetch_most_recent_by_user(TestModel.includes(:user))

ERROR:  syntax error at or near "DISTINCT"
LINE 1: SELECT guid, DISTINCT ON (user_id) user_id...

On searching on DISTINCT ON I found out that it should be the first element in a select statement for postgres to make it work.

I want to prepend the DISTINCT ON in the select statement. I have tried clearing the old select statements using except(:select) which I got from here, but it doesn't work because the includes(:user) prepends users attributes first while doing a left join.

I am using Rails 4.0.13 and Postgres 9.4.12. Any help is appreciated.

score 0 · Answer 1 · answered Jul 09 '17 at 09:28

I found that if the includes was meddling with the distinct my sub query, because which DISTINCT ON failed. I modified my method to this and it works:

  def self.fetch_most_recent_eld_log_by_user(scope, include_associations = { })
    scope.where(guid: scope.except(:select).select("DISTINCT ON (eld_logs.user_id) eld_logs.user_id, eld_logs.guid").order("eld_logs.user_id, eld_logs.created_at desc").map(&:guid))
        .includes(include_associations)
  end

Still it'll be good if someone can provide a way to prepend something in the select statement of active record scope.

Prepend DISTINCT ON in select statement in an active record scope

1 Answers1