Fetch users who have posted the last comment 3 days before eloquent

user_5188585 0
Viewed 170

Actually, I need to send the reminder emails to all my users in a time interval. So basically if a user has not posted any comment in last 3 days I need to send him the email.

I am using table structure:

Users

  • id
  • username
  • email
  • unsubscribed

Comments

  • id
  • user_id
  • comment
  • created_at

I have used the silly query for that 

Comment::with(['user' => function ($q) {
        $q->where('unsubscribed', 0)->select('id', 'username', 'email');
    }])
->groupBy('user_id')
->whereDate('created_at', $thereDaysAgoDate)
->get(['user_id']);

This give me all the users who have posted comment on that particular date.

My issue is, I only want the users who have posted a comment on that particular date but then not posted any comment after that date.

I can do this by fetching all users from comment table and then compare date via PHP if else condition but I do not want to approach in this manner.

So is there any way to achieve this via eloquent only.

eloquent laravel mysql php
2 Answers
  $main_query =  Comment::with(['user' => function ($q) {
                $q->where('unsubscribed', 0)->select('id', 'username', 'email');
            }])
        ->groupBy('user_id');     

  $all_users = $main_query->whereDate('created_at', $thereDaysAgoDate)
        ->get(['user_id']);

  $users_who_have_posts_after = $main_query->
        ->whereDate('created_at','>', $thereDaysAgoDate)
        ->get(['user_id']);

  $users_without_post_after = $all_users->whereNotIn('user_id',$users_who_has_posts_after);

Maybe it isn't the nicest way but it only do 2 queries and a loop. I'm fetching all users who posted in desired date, then fetching users who still posting after our date and then compare the original users pool with the users who still active.

user_63298020

You can combine whereHas and whereDoesntHave and also turn around the query to filter users by the condition met by their relationship

    $the_date = '2017-09-29';
    User::where('unsubscribed', 0)->whereHas('comments', function ($comments)  use ($the_date){
        return $comments->whereDate('created_at', '=', $the_date);
    })->whereDoesntHave('comments',  function($comments) use ($the_date){
        return $comments->whereDate('created_at', '>', $the_date);
    })->get();

PS: I assume that you have comments relationship with User. This is tested on L5.5.*

user_57044100
Related