CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
therealrodk
therealrodk

Reputation: 444

How to Write ActiveRecord Query For Special Case

I am using Ruby on Rails 5.1, and I have an Entry model that has a word attribute. There can be multiple Entries with the same word, as their uniqueness is based on word plus another field. In the controller, I am creating a dropdown selector @unique_entries that should only include the first instance of an Entry.word.

I have attempted to do this from multiple angles, but just can't seem to get it right.

The last thing I tried was something like this pseudocode (sorry, I deleted the actual code after it failed):

all_entries = Entry.all.order(word: :asc)
unique_entries = []
all_entries.each do |entry|
  if unique_entries.include?(entry.word)
    use the index to delete this record from all_entries
  else
    unique_entries << entry.word
  end
end

In my head at least, this should loop through the all_entries collection, checking to see if each entry.word is in unique_entries, which starts out as an empty array. If not, the entry.word gets added to unique_entries. If so, it removes the entry from the ActiveRecord collection all_entries, because that means one already exists. This approach failed hard, removing contents from a sample array inconsistently.

I realize this is a slow way to go, and will only be fine while I have a small number of records, so I am open to suggestions of other ways to approach this. I am still relatively new to RoR development and haven't had to deal with very complex ActiveRecord queries yet, so please don't step on my neck for not having already done what would have been "obvious" to you. Thanks.

Upvotes: 1

Views: 39

Answers (2)

jvillian
jvillian

Reputation: 20253

Try: 

@unique_entries = Entry.pluck(:word).uniq

Or: 

@unique_entries = Entry.all.order(word: :asc).pluck(:word).uniq

To have it sorted.

To be a bit more descriptive, consider:

@unique_entry_words = Entry.all.order(word: :asc).pluck(:word).uniq

(since the array is, well, a set of unique entry words, not unique entries)

Of course, mu is too short is correct and this would be better as:

Entry.distinct.order(:word).pluck(:word)

If, OTOH, you're trying to get an array comprised of the first instance of each Entry for a given word (not sure if that's what you're after, but that comment about 'use the index to delete...' suggests it might be), you could try something like:

Entry.all.order(:word).group_by(&:word).map{|k,v| v.first}

Upvotes: 1

mu is too short
mu is too short

Reputation: 434985

You can let the database do the work by using distinct and pluck together:

Entry.distinct.pluck(:word)

And if you want the database to do the sorting:

Entry.distinct.order(:word).pluck(:word)

The last one should end up using SQL like this:

select distinct word from entries order by word

Upvotes: 2

Related Questions