0

In order to clean up an old large site from spam, I'd like to delete all duplicate comments from mysql. By duplicate I mean, the comments which are posted by the same user across all nodes of a certain type where content of the comments are exactly the same.

  • What is the magnitude of the purge you need to do? Depending on the magnitude you could do this with a View and Views Bulk Operations. Or this could be done using Drush. Also do you want to delete the users too? – Brady Jan 28 '15 at 18:14
  • Well the order of magnitude is 1e6, and it will be one-off hence raw sql is preferred. Just need to delete comments not the authors. – edi Jan 28 '15 at 18:19
  • Yup you want to do that via SQL. – Brady Jan 28 '15 at 18:26
1

For toying things on the backend in SQL. The best place to start would be to look to see how the API does something in Drupal. For this you want to look at comment_delete_multiple which you can call to delete mass comments. You will need to know the comment IDs of the comments that you want to delete.

This is where this gets complicated. You have to find the CIDs you need before you can start deleting. Your criteria for finding the comments is going to depend on what you want to do. If you just want to delete any and all comments that are dupes then you could run a query like this to find dupes and generate the CIDs you want to delete.

Once you ahve the CIDs, it is very easy.You write a delete statement against the comment table. Using the Drupal API where it would iterate through the list of CIDs

$cids = array( , , , .....) 
db_delete('comment')->condition('cid', $cids, 'IN')->execute();

With the amount of comments you are trying to delete doing this strictly in SQL is going to be hard because you will need to write a statement with a gigantic list in a LIKE clause.

In my humble opinion: when I have had to do massive data changes like this in Drupal I write a PHP CLI script to do it so I can handle the data. I've migrated a website that had 150k users, 2000 blogs, and 110k comments, I did it all with PHP CLI scripts.

| improve this answer | |
  • Thanks for the explanation Brady. But I am not a php programmer and I am migrating out of drupal hence I mentioned that I need a raw sql to do the trick. – edi Jan 28 '15 at 18:58
  • 1
    I would pose your question in a SQL forum rather than here because what you are asking for is a rather complex SQL statement. – Brady Jan 28 '15 at 19:25
  • well the problem is that elsewhere people don't know drupal table relationships. – edi Jan 28 '15 at 19:27
  • @bili comment.cid on field_data_comment_body.entity_id and comment.cid on field_revision_comment_body.entity_id . Unless you have other fields on the comments those are probably the only relationships you'll need. Unless you also want to join users.uid on comment.uid. YMMV if you have comment-related contrib modules installed. Be sure to clear the caches after manipulating entity/field data manually. Constructing that SQL query for you is beyond the scope of Drupal Answers so you might be better off posting it elsewhere if you're on a deadline – Clive Jan 28 '15 at 19:35

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.