get all active record objects filtered for unique by one attribute

Question

Is it possible to query a model object and filter results by one or more columns for uniqueness?

I should qualify this with, taking the first unique record and ignoring the rest is fine.

My usual method is to query the object and pluck uniq ids or whatever and run another query.

Lets say I had a set like this:

Test id: 24997, test_id: 7, group_id: 5408,

Test id: 25001, test_id: 7, group_id: 5412,

Test id: 25002, test_id: 8, group_id: 5413,

Test id: 25004, test_id: 8, group_id: 5415,

Test id: 25007, test_id: 9, group_id: 5417,

Test id: 25008, test_id: 9, group_id: 5299

I would like to have results like this. Unique by the test_id column:

Test id: 24997, test_id: 7, group_id: 5408,

Test id: 25002, test_id: 8, group_id: 5413,

Test id: 25007, test_id: 9, group_id: 5417

I suppose for completeness. Let's say this is a through table with multiple columns. How could I get unique records by two or more columns?

Answer 1

You can use minimum on the id:

Test.group(:test_id).minimum(:id)

These will return a hash with a group key and an id value, which you can pass to a where condition on the scope:

Test.where(id: (Test.group(:test_id).minimum(:id).values))

Answer 2

Yes. You can filter the results based on uniqueness on a column by using distinct.

Consider you have a Post model with title column and you have to get the unique records by title, then you would do

Post.select('DISTINCT title')

EDIT: The above query will only return the title column, but if you want all the columns you'd have to loop through and filter. Basically if you perform uniqueness on one column and there are duplicates, it can't decide which one to keep and which one to discard.