CODEWITHSUNDEEP
ruby-on-railspostgresql
Ryan
Ryan

Reputation: 3181

rails 4 select distinct on multiple columns in postgres with where and limit

How do you select multiple fields with distinct values, and other non-distinct fields with them, all in one call with where and limit? I tried .pluck (which supports multiple fields in rails 4), .uniq (which didn't work in my case).

Upvotes: 10

Views: 10722

Answers (2)

VAD
VAD

Reputation: 2401

Here's a bit less verbose and more ActiveRecord-centered approach which should work not only for Postgres but for MySQL as well.

Model.select('field1,field2').distinct.where(field3: 'value').limit(10)

Upvotes: 0

Ryan
Ryan

Reputation: 3181

This is what worked for me, when used in the controller action

@models = Model.select('DISTINCT ON (field1,field2,field3) *')
     .where(id: params[:id])
     .limit(100)

Upvotes: 22

Related Questions