Rails 4 query unique by single attribute

ruby-on-rails postgresql activerecord arel
goddamnyouryan picture goddamnyouryan · Aug 5, 2013 · Viewed 16.5k times · Source

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

Answer

BananaNeil picture BananaNeil · Jun 1, 2015

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.

Related questions

Issue when retrieving records with empty array

I have a table of around 100 Users and I also have an array of user ids. What I wanted to do is show all users who are not a part of this array of user ids. When I do something …

Read More
Rails 4 LIKE query - ActiveRecord adds quotes

I am trying to do a like query like so def self.search(search, page = 1 ) paginate :per_page => 5, :page => page, :conditions => ["name LIKE '%?%' OR postal_code like '%?%'", search, search], order => 'name' end …

Read More
Find rows with multiple duplicate fields with Active Record, Rails & Postgres

What is the best way to find records with duplicate values across multiple columns using Postgres, and Activerecord? I found this solution here: User.find(:all, :group => [:first, :email], :having => "count(*) > 1" ) But it doesn't seem to work …

Read More