How to remove duplicates in MySQL using Rails?

Question

I have to following table:

Relations

[id,user_id,status]
1,2,sent_reply
1,2,sent_mention
1,3,sent_mention
1,4,sent_reply
1,4,sent_mention

I am looking for a way to remove duplicates, so that only the following rows will remain:

1,2,sent_reply
1,3,sent_mention
1,4,sent_reply

(Preferably using Rails)

You want to return only uniq items or you want remove all duplicates? — fl00r
– fl00r, Commented Apr 12, 2011 at 16:33
So you want only one single (id, user_id) pair, regardless of the status? How do you decide which 'status' message to keep? Last one recorded? First one? Random? — Marc B
– Marc B, Commented Apr 12, 2011 at 16:44
I want to remove duplicates as judged by the first two fields (id, user_id). My example is a bit misleading in that id isn't a primary key (which would be unique) but some other id (think of it as member_id) — MikeMarsian
– MikeMarsian, Commented Apr 14, 2011 at 9:29

Erik J · Accepted Answer · 2013-04-21 17:56:14Z

I know this is way late, but I found a good way to do it using Rails 3. There are probably better ways, though, and I don't know how this will perform with 100,000+ rows of data, but this should get you on the right track.

# Get a hash of all id/user_id pairs and how many records of each pair
counts = ModelName.group([:id, :user_id]).count
# => {[1, 2]=>2, [1, 3]=>1, [1, 4]=>2}

# Keep only those pairs that have more than one record
dupes = counts.select{|attrs, count| count > 1}
# => {[1, 2]=>2, [1, 4]=>2}

# Map objects by the attributes we have
object_groups = dupes.map do |attrs, count|
  ModelName.where(:id => attrs[0], :user_id => attrs[1])
end

# Take each group and #destroy the records you want.
# Or call #delete instead to save time if you don't need ActiveRecord callbacks
# Here I'm just keeping the first one I find.
object_groups.each do |group|
  group.each_with_index do |object, index|
    object.destroy unless index == 0
  end
end

fl00r · Accepted Answer · 2011-04-12 16:43:40Z

-1

It is better to do it through SQL. But if you prefer to use Rails:

(Relation.all - Relation.all.uniq_by{|r| [r.user_id, r.status]}).each{ |d| d.destroy }

or

 ids = Relation.all.uniq_by{|r| [r.user_id, r.status]}.map(&:id)
 Relation.where("id IS NOT IN (?)", ids).destroy_all # or delete_all, which is faster

But I don't like this solution :D

answered Apr 12, 2011 at 16:43

fl00r

83.7k33 gold badges222 silver badges239 bronze badges

1 Comment

MikeMarsian Over a year ago

This would be very slow and memory consuming (My relations table is 100,000+ rows. Is there any more SQLish way to do this. In this point, it's not very important to wrap it in rails.

Collectives™ on Stack Overflow

How to remove duplicates in MySQL using Rails?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related