mysql - 如何使用 Rails 删除 MySQL 中的重复项？

Question

我必须下表:

关系

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

我正在寻找一种删除重复项的方法，以便只保留以下行:

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

(最好使用 Rails)

Answer 1

我知道这已经晚了，但我找到了一个使用 Rails 3 的好方法。虽然可能有更好的方法，但我不知道这将如何处理 100,000 多行数据，但是这应该让你走上正确的 rails 。

# 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