rails 3: database query

Question

I have an Artists model with name:string and other attributes. BUT I have multiple Artist entries under the SAME name.

Is there a way to pull an array of artist objects without any duplicates of name?

I've found ways to do with with only the name attribute but nothing where I can get the entire artist object.

These both do just the name attribute:

@artists = Artist.select('DISTINCT name').all
@artists = Artist.all.collect{ |a| a.name }.uniq

Sameer C · Accepted Answer · 2011-07-30 07:17:57Z

2

Activerecord group does what you're looking for: Artist.group(:name).all

answered Jul 30, 2011 at 7:17

Sameer C

2,7971 gold badge18 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

trying_hal9000 Over a year ago

Very nice thanks, I give this the answer because it benchmarks 50% better than the above answer. Thanks.

Sam 山 · Accepted Answer · 2011-07-29 22:54:47Z

0

My rails 3 is not so good but it still has rails 2 syntax.

@artists = Artist.find(:all, :select => 'DISTINCT name')

And then we can get some rails 3 love.

answered Jul 29, 2011 at 22:54

Sam 山

42.9k36 gold badges116 silver badges158 bronze badges

2 Comments

trying_hal9000 Over a year ago

maybe be too outdated, I get: ArgumentError: Unknown key(s): distinct

trying_hal9000 Over a year ago

I think thats the same as what I did above, it returns [#<Artist name: "jeff">, #<Artist name: "jim hendrix">] but not the entire artist object, or am I missing something?

Kyle Fleming · Accepted Answer · 2011-07-30 00:29:51Z

0

One way is to grab the ids of distinct rows and grab the rest of the data from there:

Artist.where('artists.id IN (SELECT MIN(a.id) FROM artists AS a GROUP BY a.name)').all

edited Jul 30, 2011 at 0:29

answered Jul 29, 2011 at 23:24

Kyle Fleming

5794 silver badges8 bronze badges

2 Comments

trying_hal9000 Over a year ago

Hi this does look to be completing what I was looking for, could you explain a little more whats happening and how many requests there are?

Kyle Fleming Over a year ago

It first groups everything by name (duplicate names are all in 1 group) and grabs the lowest id from those groups (the inner select) and then grabs all artists with those ids. It's still just 1 sql query but the database does a bit more processing to avoid duplicates. Be sure to have an index on the name column. You might want to consider preventing duplicate artists in the first place if you are always ignoring them.

Collectives™ on Stack Overflow

rails 3: database query

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related