Why I love relational databases (oh, and Drupal too)

The problem

After I brought Anthrocon's room share and ride share forums online, I noticed that last year's posts were still present. This was a problem because people needing rides or rooms for this year's conventions did not notice the date and were replying to those posts, thus wasting everyone's time.

Now, I dislike removing content from any website I manage, since that can potentially hurt Google's PageRank on the site. If only there were some way of removing the old posts from those forums without actually deleting the posts...

Then I remembered that Drupal's database is in third-normal form and came up with this query after about 15 minutes of fiddling:

CREATE TABLE temp_nids (nid INT(10) UNSIGNED);

INSERT INTO temp_nids (
   SELECT nid FROM term_node WHERE 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 IN ('Room Share', 'Ride Share') 
         AND FROM_UNIXTIME(n.created) < '2007-08%'

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

The innermost query (SELECT n.nid FROM node...) selects node IDs that belong to the Room Share or Ride Share forums with a creation date before August, 2007. The query around that (SELECT nid FROM term_node...) I originally had in to make sure that we got valid node IDs from term_node. Given how the query evolved, that's probably no longer necessary. The outermost query (INSERT INTO temp_nids) stored the matching node IDs in our temporary table for later use.

The final query (DELETE FROM term_node...) deletes the offending node IDs from the term_node table, which is responsible for linking nodes to taxonomy terms.

In other Drupal news, I stumbled across a nice little article the other day called 10 Reasons to Use Drupal CMS. While I knew some of the things mentioned in that article, I had no idea that entities such as The United Nations, Forbes, The Discovery Channel, AOL, and most surprisingly of all--The Grateful Dead.. all use Drupal. Fascinating stuff.

Also, I found the website Drupal Dojo which contains lots of tutorials on how to perform different tasks in Drupal. It looked just like another how-to type site (not that there's anything wrong with that!) until I came to the article on patch rolling and saw this:

Um, yeah... I sure wasn't expecting to see that particular graphic. Sticking out tongue

Average: 3 (2 votes)
Your rating: None