Rails find duplicate records

find_dups.rb

columns_that_make_record_distinct = [:some_id, :another_name]

duplicate_records = Model.where.not(id: Model.select("MIN(id) as id").group(columns_that_make_record_distinct))

A more succinct way of getting ids: distinct_ids = AppPlugin.select('MIN(id) as id').group(:app_id, :plugin_id).ids

Also the .map(&:id) makes the whole query more inefficient, note the difference between these 2 snippets:

• Current version:

  columns_that_make_record_distinct = [:some_id, :another_name]
  distinct_ids = Model.select("MIN(id) as id").group(columns_that_make_record_distinct).map(&:id)
  duplicate_records = Model.where.not(id: distinct_ids)
  # =>   Model Destroy (0.5ms)  DELETE FROM "models" WHERE "models"."id" NOT IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["id", 9], ["id", 11], ["id", 7], ["id", 3], ["id", 4], ["id", 5], ["id", 12], ["id", 13], ["id", 14], ["id", 15], ["id", 16], ["id", 17], ["id", 18], ["id", 19], ["id", 20], ["id", 21], ["id", 23]]

• Improved version:

  columns_that_make_record_distinct = [:some_id, :another_name]
  distinct_records = Model.select("MIN(id) as id").group(columns_that_make_record_distinct)
  duplicate_records = Model.where.not(id: distinct_records)
  # =>  Model Destroy (0.7ms)  DELETE FROM "models" WHERE "models"."id" NOT IN (SELECT "models"."id" FROM "models" GROUP BY "models"."some_id", "models"."another_name")

That is, the current version does a first query to load the IDs that later are passed to the 2nd query as an array of IDs (which can be very large). The improved version, instead, does not eager load the result of the 1st query, it just does a single query passing the IDs as a subquery, which is more efficient.