CODEWITHSUNDEEP
ruby-on-rails
zseikyocho
zseikyocho

Reputation: 671

How to get unique record by special column from a where method with Rails?

For example, this method return records as:

Post.where(valid: true)
# [
    [0] #<Post:11113892841> { :id => 1, :valid => 1, :user_id => 1},
    [1] #<Post:21113892841> { :id => 2, :valid => 1, :user_id => 2},
    [2] #<Post:31113892841> { :id => 3, :valid => 1, :user_id => 2},
    [3] #<Post:41113892841> { :id => 4, :valid => 1, :user_id => 3},
    ...
]

Want to get only unique user_id recoreds as:

[
    [0] #<Post:11113892841> { :id => 1, :valid => 1, :user_id => 1},
    [1] #<Post:21113892841> { :id => 2, :valid => 1, :user_id => 2},
    [2] #<Post:41113892841> { :id => 4, :valid => 1, :user_id => 3},
    ...
]

How to do?

Upvotes: 0

Views: 320

Answers (2)

max
max

Reputation: 102368

While you can use .distinct this will only return the user_id column:

irb(main):037:0> Post.select(:user_id).distinct
  Post Load (0.6ms)  SELECT  DISTINCT "posts"."user_id" FROM "posts" LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Post id: nil, user_id: 1>]>

Which is kind of useless since you can't really do anything with the result set.

Unfortunately there is no good polyglot solution for this relatively simple problem due to difference in how the SQL standards are interpreted.

On MySQL you can use a GROUP BY statement:

Post.group(:user_id)

However this does not work on Postgres. (column "posts.id" must appear in the GROUP BY clause). You could say that the only reason this works on MySQL is its allowance for potentially ambiguous queries - it does not comply to the SQL 1999 or 2003 standard.

On Postgres you should instead use DISTICT ON(user_id) as it will not allow the above query:

irb(main):058:0> Post.select("DISTINCT ON (posts.user_id) posts.*")
  Post Load (0.9ms)  SELECT  DISTINCT ON (posts.user_id) posts.* FROM "posts" LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Post id: 3, user_id: 1, created_at: "2018-09-14 11:46:12", updated_at: "2018-09-14 11:46:12">]>

Upvotes: 2

Ben Toogood
Ben Toogood

Reputation: 479

The most reliable way of doing this is:

Post.where(valid: true).select('distinct(user_id)')

Upvotes: 2

Related Questions