Cleaning out old forum posts in Drupal

It's that time of the year again, to clean out Anthrocon's ride share and room share forums.

I dislike deleting old content, because that just causes lots of bad links from Google (and possibly other sites). Not to mention that my logs fill up with 404s unnecessarily. Instead, this SQL will do the job nicely.

CREATE TABLE temp_nids (
   nid INT(10) UNSIGNED, 
   term_name VARCHAR(255), 
   title VARCHAR(255)
   );

INSERT INTO temp_nids (
   SELECT DISTINCT term_node.nid, node.title
      FROM term_node 
      LEFT JOIN node ON term_node.nid = node.nid
      LEFT JOIN term_data
         ON term_node.tid=term_data.tid
   
      WHERE term_node.nid IN (
         SELECT n.nid FROM node AS n
         LEFT JOIN term_node AS tn 
            ON n.nid=tn.nid
         LEFT JOIN term_data AS td 
            ON tn.tid=td.tid
         WHERE
         (
            td.name LIKE '%Room Share%'
            OR
            td.name LIKE '%Ride Share%'
         )
         AND n.status = 1
         AND FROM_UNIXTIME(n.created) <= '2013-07%'
      )

);

DELETE FROM term_node WHERE nid IN (
   SELECT nid FROM temp_nids
   );

The innermost SELECT statement will return the posts made from those forums before last year's convention. The NIDs are then matched to their forum names and post titles and written to the temp_nids table. The reason for the forum names and post titles is to allow for manual inspection of the temp_nids table.

The temp_nids table is then used by the DELETE statement at the end to remove all terms from those nodes, effectively "detaching" those posts from the forums they were originally made in.

End result: the posts aren't removed from the site, just from the forums they were originally in.

3.3
Average: 3.3 (10 votes)
Your rating: None