I recently converted my web site to Drupal 4.7, and one of my last tasks was to get the newsletter up and running. Simple News accomplished that task nicely save for one problem: none of the imported email addresses show up on the "Show Subscriptions" admin page. The only email addresses that appear in the list are the handful who have accounts within my Drupal install.
Looking at the code, I think the problem is the query that assembles the subscribe list. This query (stripped of the filtering component) does a INNER JOIN on the subscription uid and the user uid:
SELECT DISTINCT ss.*, u.name FROM sn_subscriptions ss INNER JOIN users u ON ss.uid = u.uid INNER JOIN sn_snid_tid s ON ss.snid = s.snid ORDER BY ss.mail ASC
The problem is that it this only produces a list of users that have Drupal accounts. In order to get the full account list, you need to use a LEFT JOIN instead:
SELECT DISTINCT ss.*, u.name FROM sn_subscriptions ss LEFT JOIN users u ON ss.uid = u.uid INNER JOIN sn_snid_tid s ON ss.snid = s.snid ORDER BY ss.mail ASC
I made this change to my code, and it worked fine: all my missing, non-registered e-mail addresses appeared. Is there a reason why the INNER JOIN was used, or is this a legitimate bug?
Ken
Comments
Comment #1
greenmachine commentedThank you! I'm surprised this change hasn't made it into the default 4.7.x release of this module yet. Worked for me, too. For anyone else who might not know where this change should be made, it's on line 1455 of my copy of simplenews.module:
$query = 'SELECT DISTINCT ss.*, u.name FROM {sn_subscriptions} ss LEFT JOIN {users} u ON ss.uid = u.uid INNER JOIN {sn_snid_tid} s ON ss.snid = s.snid'.$queries[$_SESSION['simplenews_subscriptions_filter']].' ORDER BY ss.mail ASC';Comment #2
sutharsan commentedThe the 'list subscriptions' button/tab lists all subscriptions and only these. Pending subscriptions are not listed. The proposed code lists both subscribed and pending which is a change of functionality.
'list subscriptions' works by design.