BuddyDev

Search

[Resolved] Send email to user that has been inactive for 2 months (adding a question)

  • Participant
    Level: Enlightened
    Posts: 70
    Lefteris on #47573

    Hello Ravi,

    Following https://buddydev.com/support/forums/topic/send-email-to-user-that-has-been-inactive-for-2-months/ I would like to ask you about:

    
    global $wpdb;
    $bp = buddypress();
    
    $user_ids = $wpdb->get_col(
    		$wpdb->prepare(
    			"SELECT user_id FROM {$bp->members->table_name_last_activity} 
    			WHERE component = %s AND type = 'last_activity' AND 
    			date_recorded < current_date - interval 60 day",     
    			$bp->members->id,
    		)
    	);
    
    

    from the previous example, that is getting a list of BP users that have not been active for the last 60 days (works perfect).

    Is there any way that i could modify the sql query to get all authors IDs that have not published a new post for the last 60 days (Compare the date of the last ‘published’ post if it is more than 60 days ago).

    I have tried something like this

    
    global $wpdb;
    
    $user_ids = $wpdb->get_col(
    		$wpdb->prepare(
    			"SELECT u.ID, u.user_nicename, u.display_name, u.user_email, p.post_date FROM $wpdb->users u INNER JOIN (SELECT post_author, MAX(post_date) post_date FROM $wpdb->posts WHERE post_type = 'post' AND post_status='publish' GROUP BY post_author) p WHERE u.ID = p.post_author ORDER BY u.display_name",     
    		)
    	);
    
    

    that i have found on the web, but i believe it’s not correct and i am really not so good in sql .

    Thank you for your time 🙂

  • Participant
    Level: Enlightened
    Posts: 70
    Lefteris on #47584

    Hello Ravi

    After some research in sql and change in my original code, i now need to just retrieve with a SQL query the latest post from each author so that i can use authors ID and published post_date of the post.

    I’ve executed this

    $posts = $wpdb->get_results(
    		"SELECT post_author, post_title, post_date FROM (	SELECT * FROM $wpdb->posts ORDER BY post_date DESC ) 
    		X WHERE post_status='publish' AND post_type='post' GROUP BY post_author"
     	);

    but for some reason it just returns me all earliest posts of all authors and i cant figure out why?

    Could you help me please 🙂

  • Keymaster
    Level: Yogi
    (BuddyDev Team)
    Posts: 2541
    Ravi on #47587

    Hello Lefteris,

    Please try the following SQL query:

    
    SELECT ID, post_title, post_author, MAX(post_date) FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP By post_author;
    
    

    Please let me know if it helps or not.

    Regards
    Ravi

  • Participant
    Level: Enlightened
    Posts: 70
    Lefteris on #47589

    Hello Ravi

    Thank you very much for your help 🙂
    Unfortunately it didn’t work. Keeps giving me the oldest article for each author.

  • Keymaster
    Level: Yogi
    (BuddyDev Team)
    Posts: 2541
    Ravi on #47612

    Hello Lefteris,

    Thank you for the acknowledgment. I will look at it on the day’s end and will update you.

    Regards
    Ravi

  • Keymaster
    Level: Yogi
    (BuddyDev Team)
    Posts: 2541
    Ravi on #47659

    Hello Lefteris,

    Sorry for the delayed reply. I have checked the query again and it has given me the right result. I am sharing the screenshot of the query I have executed. Please take a look.

    Normal result for posts
    https://tinyurl.com/2qdd8jw7

    Lasted published posts
    https://tinyurl.com/2kd2gpcu

    Please share your query so that I can check.

    Regards
    Ravi

  • Participant
    Level: Enlightened
    Posts: 70
    Lefteris on #47660

    Hello Ravi

    Thank you so much for your help 🙂
    I was suspecting that your code is correct.
    I have tried many different examples of codes and i really can’t understand why it gives me wrong results.

    https://ibb.co/Z25Mw1x (keeps giving the oldest article)
    https://ibb.co/RND6Dm7

    It seems like the DESC doesn’t affect.

    What i am trying to do is similar to previous https://buddydev.com/support/forums/topic/send-email-to-user-that-has-been-inactive-for-2-months/ :

    With a cron job, send an email reminder to authors that have not published a post for the last 60 days.

    So i am trying to create my function, something like this :

    
    function reminder_email() {
    	
           $days = 60; // if these days have passed from the last published post of the author send email reminder
    
    	global $wpdb;
    	
    	// get the most recent post for each author
    	$posts = $wpdb->get_results();
    
    	// No post found.
    	if ( empty( $posts ) ) {
    		return;
    	}
    
    	
    	if ( ! empty( $posts ) ) { 
    	foreach( $posts as $post ) {
    	
            // get today's date in unix timestamp format
    		$today = strtotime( date( 'Y-m-d' ) );	
    		
    		// the date is X number of days from last post published date where X is set in the $days variable above
    		$expire = strtotime( '+'. $days .'days', strtotime( $post->post_date ) );
    		
    	$user_info = get_userdata( $post->post_author );
    	
    	if ( $expire < $today ) { wp_mail();  //send email reminder to $user_info->user_email  }
           }
    	}
    }
    add_action( 'reminder_email', 'reminder_email' );
    

    I don’t understand why results from db are wrong.

    • This reply was modified 1 month, 2 weeks ago by Lefteris.
  • Keymaster
    Level: Yogi
    (BuddyDev Team)
    Posts: 2541
    Ravi on #47672

    Hello Lefteris,

    Thank you for sharing the code. I will look at it and will update you.

    Regards
    Ravi

  • Participant
    Level: Enlightened
    Posts: 70
    Lefteris on #47738

    Hello Ravi.
    I believe i’ve got it working but i am not sure if there is any mistake in my code. Could you also check it and confirm:

    
    
    function reminder_email() {
    	
           $today = strtotime( date( 'Y-m-d' ) ); // Get today's date
    	$args = array(
       'orderby' => 'post_date',
       'order' => 'DESC',
       'author' => '',
       'post_type' => 'post',
       'post_status' => 'publish',
       'posts_per_page' => 1,
    );
    
    $authors = get_users( array(
        'role__in' => array( 'contributor', 'author', 'administrator' ), // get only specific roles
        'fields' => array( 'ID' )
    ) );
    
    foreach ( $authors as $author ) {
       $args['author'] = $author->ID;
       $author_posts = new WP_Query( $args );
       if ( $author_posts->have_posts() ) {
    	   
         
    	   
          while ( $author_posts->have_posts() ) {
             $author_posts->the_post();
             $expire = strtotime( '+60 days', strtotime( $post->post_date ) ); //Calculation to compare the post_date of the last post's with today date (when is more than 60 days that author haven't published a new post)
    		 
             if ( $expire < $today ) {  wp_mail();  //send email reminder to get_the_author_meta( 'user_email' ) }
         
    	 }
       
       }
       wp_reset_postdata();
    }
    
    }
    add_action( 'reminder_email', 'reminder_email' );
    
    

    Thank you very much for your help 🙂

    • This reply was modified 1 month, 1 week ago by Lefteris.
  • Keymaster
    Level: Yogi
    (BuddyDev Team)
    Posts: 2541
    Ravi on #47762

    Hello Lefteris,

    Sorry for the delayed reply. Code seems to be work fine but it is very inefficient with large user base. So, If you want we can provide you an efficient way of doing this just tell how many users you are dealing with.

    Regards
    Ravi

You must be logged in to reply to this topic.

This topic is: resolved