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'>

Anaxagoras

so which book object should it return, 1 or 3? – Counterinsurgency

@Counterinsurgency doesn't matter, either works. – Anaxagoras

Answer 1

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.

Goodnight

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') – Biconvex

This is brilliant! I have been looking for this everywhere :-) – Dustheap

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!!! – Ocieock

Anyone knows how can I achieve this with mysql? – Acquirement

In mysql: SELECT distinct on (name) * FROM items – Goodnight

For Mysql see my answer – Godinez

Answer 2

Please try this:

Item.select('distinct name')

Leaguer

That kind of works. It returns the objects with just the 'name' attribute. I want to return the whole object. I get this: <Item id: nil, name: 'Book'>, <Item id: nil, name: 'Car'> – Anaxagoras

Try Item.select('id', 'name') – Monia

@Anaxagoras It's a bit late, but see my answer below. – Goodnight

Answer 3

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)

Yclept

Answer 4

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) *')

Roney

Answer 5

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.

Monia

This is great for small queries, but terrible performance for large datasets. – Valkyrie

Really poor idea to .to_a a query which loads the data into memory. – Longlegged

Answer 6

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?

Godinez