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.
3
-
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?– BradyJan 28, 2015 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.– ediJan 28, 2015 at 18:19
-
Yup you want to do that via SQL.– BradyJan 28, 2015 at 18:26
Add a comment
|
1 Answer
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.
-
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.– ediJan 28, 2015 at 18:58
-
1I would pose your question in a SQL forum rather than here because what you are asking for is a rather complex SQL statement.– BradyJan 28, 2015 at 19:25
-
well the problem is that elsewhere people don't know drupal table relationships.– ediJan 28, 2015 at 19:27
-
@bili
comment.cidonfield_data_comment_body.entity_idandcomment.cidonfield_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 joinusers.uidoncomment.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, 2015 at 19:35