Email users from database but only once

19 Posts

modx125 Reply #1, 13 years ago

Hi everyone,

I have a database of 'user comments' that holds the comment, the userId, true/false for if they want to be emailed.

My issue is that if a user comments on a post 5 times, they are in that database 5 times and thus, they get 5 of the same emails. Here is my setup:

Find users in the database that want to be emailed about this comment

$ab = $modx->newQuery('Comments');
$ab->leftJoin('modUserProfile','Profile',array('Profile.internalKey = Comments.author'));
$ab->leftJoin('Bulletin','Bulletin',array('Bulletin.id = Comments.bulletin_id'));
$ab->where(array('Comments.bulletin_id' => $bullId));
$ab->where(array('Comments.email' => 1));
$ab->select(array('Comments.*','Profile.fullname as fullname' , 'Profile.email as email' , 'Bulletin.title as title'));
$emailList = $modx->getCollection('Comments',$ab);

Now email them

foreach ($emailList as $e){
$modx->getService('mail', 'mail.modPHPMailer');
$modx->mail->address('to',$e->email);
$modx->mail->set(modMail::MAIL_BODY,$message);
$modx->mail->set(modMail::MAIL_SUBJECT,'A new comment has been posted!');}

The issue I'm sure has to do with me looping the results however I can't figure a way to find the user's email address if I don't loop. Thanks in advance!

This question has been answered by modx125. See the first response.

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 13 years ago

You should be able to use a "distinct" clause in that getCollection query.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

19 Posts

modx125 Reply #3, 13 years ago

Quote from: sottwell at Oct 03, 2013, 11:56 AM

You should be able to use a "distinct" clause in that getCollection query.

Thanks for this path of thought - it seems to be what I need. I reviewed this DISTINCT as well as GROUPBY for the query however, the emails are still sending multiple times with my foreach loop.

I used the DISTINCT in my code like this:

$emailList = $modx->getCollection('Comments',array(
    $ab,
    'email:DISTINCT'
));

discuss.answer

19 Posts

modx125 Reply #4, 13 years ago

I located the solution which is a byproduct of the DISTINCT clause. I ended up using the groupby which resolved my issue by returning only unique results...I believe it's very similar to the solution raised above. For anyone in the future that reads this, here is the final code:

$ab = $modx->newQuery('Comments');
$ab->leftJoin('modUserProfile','Profile',array('Profile.internalKey = Comments.author'));
$ab->leftJoin('Bulletin','Bulletin',array('Bulletin.id = Comments.bulletin_id'));
$ab->where(array('Comments.bulletin_id' => $bullId));
$ab->where(array('Comments.email' => 1));
$ab->select(array('Comments.*','Profile.fullname as fullname' , 'Profile.email as userEmail' , 'Bulletin.title as title'));
$ab->groupby('author');
$ab->sortby('Comments.create_date','DESC');
$emailList = $modx->getCollection('Comments',$ab);

Thanks again to sotwell for the path to get me here

Answered Email users from database but only once