Rails 4 query unique by single attribute

Question

So this is more of an arel question than anything but here's what I am trying to do.

I have three objects lets say, called Items

<Item id: 1, name: 'Book'>
<Item id: 2, name: 'Car'>
<Item id: 3, name: 'Book'>

I want to do a query that will just return only one of each unique "name" attributes.

Something like Item.select('distinct(name), items.*')

This doesn't work though, it still returns all three items.

How can I form this query so that it only returns:

<Item id: 1, name: 'Book'>
<Item id: 2, name: 'Car'>

so which book object should it return, 1 or 3? – sevenseacat Aug 05 '13 at 04:24 — sevenseacat, Aug 05 '13 at 04:24
@sevenseacat doesn't matter, either works. – goddamnyouryan Aug 05 '13 at 04:39 — goddamnyouryan, Aug 05 '13 at 04:39

score 35 · Accepted Answer · answered Jun 01 '15 at 03:09

35

If you want to get the entire model back, but still maintain uniqueness, you can use this:

Item.select('distinct on (name) *')

I've only tested this with a Postgres database. It may or may not work with mysql.

answered Jun 01 '15 at 03:09

BananaNeil

10,322
7
46
66

2

the answer above will return all column, if you want select only some column you can try with `Item.select('distinct on (name) name, id')` – Mada Aryakusumah Apr 19 '16 at 15:09
1

This is brilliant! I have been looking for this everywhere :-) – Abdo May 24 '16 at 18:22
1

I've been freaking out because I realised I needed to grab only the most recent records on a DB, and I was due to show something to my boss but for the life of me couldn't get it to work until I found this. You are a life saver, that you!!! – edhog Nov 08 '16 at 05:47
2

Anyone knows how can I achieve this with mysql? – mswiszcz Apr 05 '17 at 13:21
In mysql: `SELECT distinct on (name) * FROM items` – BananaNeil Apr 05 '17 at 21:25
For Mysql see my [answer](https://stackoverflow.com/a/47528353/5615038) – Fangxing Nov 28 '17 at 09:50

score 11 · Answer 2 · answered Aug 05 '13 at 03:51

11

Please try this:

Item.select('distinct name')

answered Aug 05 '13 at 03:51

vee

38,255
7
74
78

5

That kind of works. It returns the objects with just the 'name' attribute. I want to return the whole object. I get this: , – goddamnyouryan Aug 05 '13 at 03:53
Try Item.select('id', 'name') – TalkativeTree Jun 05 '14 at 04:55
@goddamnyouryan It's a bit late, but see my answer below. – BananaNeil Jul 09 '15 at 17:52

Frexuz · Answer 3 · 2015-01-11T17:31:27.350

4

If you only need an array with the names, without the ID, you can do:

Item.pluck(:name).uniq

SQL query result:

#=> SELECT `items`.`name` FROM `items`

** edit **

The uniq is ran on the array of records. Be careful if you expect a lot of records. SQL Distinct is much faster.

If so, use vee's answer above, with a map:

Item.select('distinct name').map(:name)

edited Jan 11 '15 at 17:31

answered Jan 11 '15 at 17:24

Frexuz

4,732
2
37
54

rwxdash · Answer 4 · 2019-03-07T13:00:32.717

3

I can't comment on posts yet, so, putting this as another answer for the question.

In case of someone still searches for this, @BananaNeil's answer is correct. However, putting distinct in select didn't work for me (Rails 5.2.2). Separating these two did fix my problem.

klass.where(
  # Your query or whatever
).distinct.select('on (attribute) *')

edited Mar 07 '19 at 13:00

answered Mar 07 '19 at 10:42

rwxdash

66
6

score 0 · Answer 5 · edited May 19 '15 at 05:51

0

In Rails 4 try Items.all.to_a.uniq { |item| item.name }

In Rails 3 you should be able to just do Items.uniq_by { |item| item.name }

When you call uniq on an array, you can pass it a block to dictate how to determine uniqueness. In Rails 3 you used to be able to use uniq_by, but it became deprecated in Rails 4. So one method I found is to just convert the ActiveRecord Relation to an array and call uniq on that.

edited May 19 '15 at 05:51

jvperrin

3,368
1
23
33

answered Mar 14 '14 at 01:32

TalkativeTree

609
5
10

14

This is great for small queries, but terrible performance for large datasets. – Sean Jun 25 '15 at 21:34
5

Really poor idea to `.to_a` a query which loads the data into memory. – bcackerman Jun 06 '17 at 18:57

score 0 · Answer 6 · answered Nov 28 '17 at 09:46

0

For Mysql, your can use group with ONLY_FULL_GROUP_BY disabled,

Item.group(:name).to_sql
=> "SELECT `items`.* FROM `items`  GROUP BY name"

See Is there ANY_VALUE capability for mysql 5.6?

answered Nov 28 '17 at 09:46

Fangxing

5,716
2
49
53

Rails 4 query unique by single attribute

6 Answers6

Linked