CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
linkyndy
linkyndy

Reputation: 17928

Pluck unique values from an ActiveRecord has_many association

I have the following association: Artist has_many Songs. Hence, I can get an artist's songs by doing:

artist.songs

However, I would like to get only the songs' genre:

artist.songs.pluck(:genre)

But, this genre may appear multiple times in the results; I would only like to get unique genre values. Unfortunately, pluck isn't of any help here, since it returns an array, and calling uniq on it won't tweak the ActiveRecord query, but the plain Array#uniq.

I can do it like this:

artist.songs.select(:genre).uniq.pluck(:genre)

but I feel there must be a better way.

P.S.: From some minimal benchmarks, however, pluck + Array#uniq seems a bit faster than select + uniq + pluck.

Upvotes: 3

Views: 6773

Answers (5)

Jesse Sravya
Jesse Sravya

Reputation: 121

This is in addition to the other answers. If you want to select unique rows based on a particular column, you can also use

artist.pluck('distinct on (col1) col1, col2, col3')

which is basically

select distinct on (col1) col1, col2, col3 from artist

you can also do this with select

artist.select('distinct on (col1) col1, col2, col3')

pluck gives array of values, where as select gives array of records.

Upvotes: 0

Tom Lord
Tom Lord

Reputation: 28305

Note that as of Rails 5, Relation#uniq is deprecated; you should use Relation#distinct instead.

I recently came across this same issue myself. Ideally, I would like the following code to work - but it does not generate SQL to fetch distinct values only:

artist.songs.distinct.pluck(:genre)

As a workaround however, you can instead do:

Song.where(artist: artist).distinct.pluck(:genre)

This will generate the following SQL:

SELECT DISTINCT "songs"."genre" FROM "songs" WHERE "songs"."artist_id" = 123

For convenience, you could also consider adding this as a model method such as:

class Artist < ApplicationRecord
  def genres
    Song.where(artist: self).distinct.pluck(:genre)
  end
end

This achieves optimal performance, as the query is entirely in SQL - there are no ruby operations such as Array#map or Array#uniq being used.

Upvotes: 2

dnsh
dnsh

Reputation: 3633

I do it like this. 

artist.songs.pluck('DISTINCT genre')

I am still in search of better way. I feel this is cleaner than select(:col).uniq.pluck(:col)

Upvotes: 0

sbecker
sbecker

Reputation: 464

If using the artist's songs association, you can select distinct on genre, then map over the results to just return the strings:

artist.songs.select('distinct genre').map(&:genre)
# or...
artist.songs.select(:genre).uniq.map(&:genre) # uniq or distinct work

resulting query:

(0.2ms) SELECT distinct genre FROM "songs" WHERE "songs"."artist_id" = ? [["artist_id", 1]]

You could also use uniq if calling on the Song model directly while narrowing to the artist:

Song.where(artist: artist).uniq.pluck(:genre)

resulting query:

(0.2ms) SELECT DISTINCT "songs"."genre" FROM "songs" WHERE "songs"."artist_id" = 1

Both are equally and efficient and do the uniqueness operation in SQL and not in Ruby.

Upvotes: 6

mrvncaragay
mrvncaragay

Reputation: 1260

Model.uniq.pluck(:genre)

this generates SQl query SELECT DISTINCT rather than querying .uniq to an array again.

Upvotes: 0

Related Questions