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

[giza]

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. :-P

Comments

[toumal.livejournal.com]

Ew, joins. SQL statements separated by God should not be joined by Man. :P

[giza.livejournal.com]

What's wrong with SQL joins? :-)

[wolffit.livejournal.com]

delete from term_node where nid in (
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%'
)
)

[taral.livejournal.com]

DELETE FROM term_node WHERE EXISTS (
    SELECT 1
    FROM node, term_data
    WHERE term_node.nid = node.nid AND
          FROM_UNIXTIME(node.created) < '2007-08%' AND
          term_node.tid = term_data.tid AND
          term_data.name IN ('Room Share', 'Ride Share')
)

[taral.livejournal.com]

This gets prettier if your DB supports DELETE USING or something equivalent.