Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
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. –  thorsten müller 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

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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