drupal.notify & emailing unpublished comments

Updated Mar 13, 2007 to add instructions for Drupal 5.x.

The notify module (Drupal) is supposed to let you know via email when new content and/or new comments are available on your site. This is good from an adminstrators viewpoint since you don't need to periodically scan your logs to see if anything new has been submitted.

However, there is one problem I'm having with it: It won't notify you of unpublished comments. So if an anonymous user posts a comment, and it goes into the approval queue, you will not get an email notification. Coming from Wordpress, I see this as a big problem since Wordpress would notify you of comments that were awaiting approval. The only workaround seems to be to give all users (including anonymous users) the right to publish comments without approval. That was definitely not what I wanted.

Luckily, I was able to make a minimal patch to notify that seems to do the trick. I'm not going to post a diff, since the frequent CVS drops of notify would make it obsolete, however here is the method to patch it yourself:

Drupal 4.x:

1. Open notify.inc, and find the function _notify_send()
2. Locate the comment about 20 lines down that says
      
   <span style="font-family: courier new,courier; 
           font-weight: bold">// Fetch new comments
3. In the SELECT statement under this, you should see that one of the WHERE conditions is c.status = 0 (or possibly c.status = %d, with a parameter of COMMENT_PUBLISHED). Modify the WHERE clause to remove the c.status check (I'm assuming you have a minimal amount of PHP know-how here).
   
   

Drupal 5.x:

1. Open notify.module, and find the function _notify_send() (around line 320)
2. Locate the code about 20 lines down:
   // Fetch new comments
   $cresult = db_query(db_rewrite_sql('SELECT c.nid,
          c.cid, c.subject, c.name FROM {comments} ...
3. In the SELECT statement, you should see that one of the WHERE conditions is c.status = %d, with a parameter of COMMENT_PUBLISHED. Modify the WHERE clause to remove the text c.status = %d AND, and delete the text COMMENT_PUBLISHED, from the list that follows. (I'm assuming you have a minimal amount of PHP know-how here).
   
   

You should now receive notification emails for both published and unpublished comments. For that matter, if you didn't care about published comments, you could modify the same statement to only select non-published comments.

One trick is that you need to be logged in to your site already as an administrator (or anyone who can administer comments) so that the email link will take you straight to the comment that needs approval. Otherwise, the unpublished comments won't show up.