CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4
Rob
Rob

Reputation: 1855

how to add uniqueness on a .where call in rails

I cant find this in the docs and searching for it only shows results for adding uniqueness in the model.

How can I add uniqueness to a .where call. I want to have the below command not grab CategoryItemValues with the same category_item_id.

last_updated_items = CategoryItemValue.where(guide_id: @guide.id).order(updated_at: :desc).limit(5)

So the command grabs 5 of the most recently updated CategoryItemValues without getting 5 of them with the same category_item_id. if the category_item_id is the same as another most recently updated it skips it and adds the next most recently updated to the 5 results returned.

Update

doing this using the select method and using distinct still gives duplicate category_item_id for some reason

CategoryItemValue.select(:category_item_id).distinct.where(guide_id: @guide.id).order(updated_at: :desc).limit(5).pluck(:category_item_id, :updated_at)

[[6, Wed, 11 May 2016 10:24:39 UTC +00:00], [3, Wed, 11 May 2016 10:21:20 UTC +00:00], [3, Wed, 11 May 2016 10:20:43 UTC +00:00], [6, Wed, 11 May 2016 09:14:14 UTC +00:00], [5, Wed, 11 May 2016 09:14:04 UTC +00:00]]

notice the second and third array values both have 3 at the start. (plus im not sure if the comma after the day i the date time is going to effect pulling these values from the array)

Upvotes: 2

Views: 105

Answers (2)

Michael Gaskill
Michael Gaskill

Reputation: 8042

Try something like this:

CategoryItemValue.select(:category_item_id, "MIN(updated_at) as updated_at").distinct.where(guide_id: @guide_id).group(:category_item_id).order(updated_at: :desc).limit(5)

Both of these group the results set by category_item_id, and should return one record for each category item, with the oldest (MIN) value for updated_at of that category item. The get the newest updated records, use MAX instead of MIN.

Upvotes: 1

toddmetheny
toddmetheny

Reputation: 4443

you can pass SQL to an active record where clause. The method you're looking for is DISTINCT http://www.w3schools.com/sql/sql_distinct.asp

You can also use the rails distinct method.

Try: CategoryItemValue.select(:category_id).distinct.where(guide_id: @guide.id)

This answer might also help: Rails 3 DISTINCT QUERY

Upvotes: 1

Related Questions