91

How can I get unique values from column in the table? For example, I have this Products table:

ID NAME CATEGORY
1 name1 1st_cat
2 name2 2nd_cat
3 name3 1st_cat

Here I want to get only 2 values - 1st_cat and 2nd_cat:

<%Products.each do |p|%>
<%=p.category%>
<%end%>
Oleg Pasko
  • 2,831
  • 5
  • 35
  • 44

8 Answers8

200

Two more ways:

Product.select(:category).map(&:category).uniq # Ruby does the work

Product.uniq.pluck(:category) # DB does the work (superior)

For Rails >= 5.1 use: 

Product.distinct.pluck(:category) # DB does the work (superior)

...because Relation#uniq was deprecated.

user664833
  • 18,397
  • 19
  • 91
  • 140
  • 2
    I was exactly looking for this : Products.pluck(:category).uniq Superb! +1 :) – K M Rakibul Islam Nov 05 '12 at 20:56
  • 7
    This will pull all the products into a Ruby array, rather than doing the work in the database. – superluminary Apr 09 '13 at 14:05
  • 1
    @superluminary is pulling all the products into a Ruby array, rather than having the DB do the work recommended? – 8bithero Sep 24 '13 at 04:54
  • Normally you want the db to do the job - Ruby array means extra memory consumption, and the db would do it faster normally – Yo Ludke Mar 19 '14 at 12:29
  • 13
    `User.uniq.pluck(:source)` produces `SELECT DISTINCT "users"."source" FROM "users"` so I don't think it is loading all records into a ruby array. – Rafael Oliveira Sep 01 '14 at 21:48
  • 1
    @RafaelOliveira Correct, but that only applies to the second of the two ways shown. `Products.uniq.pluck(:category)` makes the db do the work, but the first way of `Products.select(:category).map(&:category).uniq` is equivalent to `Products.pluck(:cateogry).uniq` (notice the `pluck` and `uniq` methods are reversed), which pulls all the categories into an array in memory and makes Ruby do the work. – jangosteve Dec 19 '16 at 17:50
  • 1
    To clarify my last comment, the reason for the behavior described is because there are two different `uniq` methods being called. ActiveRecord has a `uniq` method that converts into `DISTINCT` in SQL, but then Ruby's Array class also has a `uniq` method that selects unique elements from an array. Meanwhile, AR's `pluck` and `map` methods execute the converted SQL statement and return the results converted to an array, so when you call `uniq` after `pluck` or `map`, you're actually calling the array method instead of the AR method. – jangosteve Dec 19 '16 at 17:54
  • 4
    For **Rails >= 5.1** use `Products.distinct.pluck(:category)` – davegson May 12 '18 at 12:12
  • @davegson I edited in your comment. Thanks for the update - you just helped me. Edit: Seeing as you have edit privileges too, may I kindly suggest that you edit in the correct answer should you ever come across a similar situation? – yas4891 Jun 11 '19 at 19:38
  • @Gary, thanks - updated. I followed suite on OP, but should have corrected from the beginning. – user664833 Dec 03 '19 at 23:52
27

I think you can do this:

<% Products.select("DISTINCT(CATEGORY)").each do |p| %>
<%= p.category %>
<% end %>

Source: http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

ThatOtherPerson
  • 834
  • 6
  • 18
13

This does all the work in the database server. The result is a simple array.

<% Product.distinct(:category).pluck(:category).each do |category|
    <%= category %>
<% end %>

Rails will generate SQL that works on any database (Postgres, MySQL, etc).

SELECT DISTINCT "products"."category" FROM "products"
jelder
  • 2,220
  • 2
  • 19
  • 17
9

I suggest to use Products.all.distinct.pluck(:category) because uniq has been deprecated since rails 5 and it will be removed on rails 5.1

Fabio Ros
  • 153
  • 3
  • 7
6

Try this (in the rails console)

Product.group(:category)

Product.group(:category).each { |p| p.name }
Marek Příhoda
  • 11,108
  • 3
  • 39
  • 53
4

For postgres

<% Product.select("DISTINCT ON (category) *").each do |category|
    <%= category %>
    <%= name %>
<% end %>

Update

even better

<% Product.select(%(DISTINCT ON (category) "#{Product.table_name}".*)).each do |category|
    <%= category %>
    <%= name %>
<% end %>

because it can return wrong columns when you do joins (e.g. returns id column from joined table, but not products)

srghma
  • 4,770
  • 2
  • 38
  • 54
  • Voted up because when you require the whole active record relation instead of plucked values array, this solution worked for me. – Arth Thakkar Apr 13 '20 at 08:05
1

If you or anyone want to get two or more attributes from a table like products, based on a distinct feature of an attribute, only this solution will help you for Rails >= 5.1

distinct_products = Product.select("DISTINCT ON (category) *")

# it's an active record relation class.
> distinct_products.class
=> Product::ActiveRecord_Relation

N.B. Don't use .pluck() on the distinct_products. It will reselect from the products table and the distinct feature will not work anymore.

Tamal Chakroborty
  • 71
  • 3
0

Needed to get unique output and was trying the 'uniq' method unsuccessfully. Tried several solutions posted here unsuccessfully. I'm using devise which gives me access to the current_user method and working with two tables, one being a join (an item has_many :things).

This solution ultimately worked for me :

@current_user.things.select(:item_fk).distinct.each do |thing|
 <%= thing.item.attribute %>
<% end %>
Phil_ish
  • 95
  • 1
  • 1
  • 9