Rails (or maybe SQL): Finding and deleting duplicate AR objects.

Question

ActiveRecord objects of the class 'Location' (representing the db-table Locations) have the attributes 'url', 'lat' (latitude) and 'lng' (longitude).

Lat-lng-combinations on this model should be unique. The problem is, that there are a lot of Location-objects in the database having duplicate lat-lng-combinations.

I need help in doing the following

1. Find objects that share the same lat-lng-combination.
2. If the 'url' attribute of the object isn't empty, keep this object and delete the other duplicates. Otherwise just choose the oldest object (by checking the attribute 'created_at') and delete the other duplicates.

As this is a one-time-operation, solutions in SQL (MySQL 5.1 compatible) are welcome too.

Answer 1

If you have 2 MySQL columns, you can use the CONCAT function.

SELECT * FROM table1 GROUP BY CONCAT(column_lat, column_lng)

If you need to know the total

SELECT COUNT(*) AS total FROM table1 GROUP BY CONCAT(column_lat, column_lng)

Or, you can combine both

SELECT COUNT(*) AS total, table1.* FROM table1 
GROUP BY CONCAT(column_lat, column_lng)

But if you can explain more on your question, perhaps we can have more relevant answers.

Answer 2

If it's a one time thing then I'd just do it in Ruby and not worry too much about efficiency. I haven't tested this thoroughly, check the sorting and such to make sure it'll do exactly what you want before running this on your db :)

keep = []
locations = Location.find(:all)

locations.each do |loc|
  # get all Locations's with the same coords as this one
  same_coords = locations.select { |l| l.lat == loc.lat and \
                                       l.lng == loc.lng }
  with_urls = same_coords.select { |l| !l.url.empty? }

  # decide which list to use depending if there were any urls
  same_coords = with_urls.any? ? with_urls : same_coords

  # pick the best one
  keep << same_coords.sort { |a,b| b.created_at <=> a.created_at }.first.id
end

# only keep unique ids
keep.uniq!

# now we just delete all the rows we didn't decide to keep
locations.each do |loc|
  loc.destroy unless keep.include?( loc.id )
end

Now like I said, this is definitely poor, poor code. But sometimes just hacking out the thing that works is worth the time saved in thinking up something 'better', especially if it's just a one-off.