I'm working on a review site, where there is a minor issue with almost duplicate reviews across items. Just a few words are changed. It would be very nice to be able to uncover these duplicates before they are approved by a moderator, and I'm hoping someone could chime in on the best strategy to get there.

The site is running Ruby on Rails on a Postgres database and using Thinking Sphinx for search (all on Heroku), and so far the best option I see is to be pulling all the reviews out of the db and using a module like amatch to compare the strings. Not very efficient, so in this case I guess I'll have to limit the number/age of reviews to scan for dupes.

Anyone got a better idea?

  • 1
    Not sure if this helps you, but Solr has some feature called "Clustering", that should at least give you groups of similar documents very fast and maybe even some numeric value (haven't used myself yet). Have a look: Solr Clustering, some blog, maybe Carrot2. Could at least help to ilmit the amount of data to search. Nov 15 '12 at 9:46
  • I don't think throwing Solr into the mix is something I want to do right now, but thanks for the info, Thorsten!
    – rogerkk
    Nov 23 '12 at 10:25
  • Oh, and if anyone feels like telling me why the question was downvoted, that'd also be awesome. :-)
    – rogerkk
    Nov 23 '12 at 10:25

You could put together a SQL query using its own string matching functions: http://www.postgresql.org/docs/8.3/static/fuzzystrmatch.html

You'd need to join the table against itself (or take a cross product by doing SELECT ... FROM table AS t1, table AS t2 ...) and then filter based on the string conditions that you want.

But, at the end of the day, Postgres will need to do table-scans in order to produce the results, so do be careful if you have a large database. Either limit the scope, chunk up the query, or run it at an off-peak time.

  • Aha, I was not awere of Postgres having stuff like Levenshtein built in! Awesome, that helps a lot.
    – rogerkk
    Nov 23 '12 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.