Laravel - get last row using whereHas

Question

I am trying to get the time "created_at" for the last user activity, I have the model User, and UserActivity.
I want to get the last user activity and check if the last activity of this user is 3 days to send notification,

User.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
class User extends Model
{
    public function activites()
    {
        return $this->hasMany(Activty::class);
    }

}

Activity.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;

class Activity extends Model
{
    function function user(){
        return $this->belongsTo(User::class);
    }
}

Controller

$latest_activites = User::whereHas("activites",function($query){
            $query->where("created_at",">=",Carbon::now()->subDays(3));
        });
$latest_activites = $latest_activites->get();

You you want to check this only for one user (current user)? — Alexey Mezenin, Jan 16 '18 at 08:02
@AlexeyMezenin no for all, I want to send reminder message for all users with the last activity is 3 days — Peril, Jan 16 '18 at 08:03
If you want to get all users with last activity 3 days ago or more, please check my answer. — Alexey Mezenin, Jan 16 '18 at 08:06
but I want to get the last activity only, I want to get the activity information, not just the users, anyway to do it ? — Peril, Jan 16 '18 at 08:08
But to send a notification you still need a user instance. So, you need to get a user with just one latest notification? Also, the relationship is `hasMany()`? — Alexey Mezenin, Jan 16 '18 at 08:09
yes, the user have many activities, i want to check the last activity of the user only, not all of them, if I have old one and new one it will show the old one aslo — Peril, Jan 16 '18 at 08:11
So, you need to get the latest activity for one user. Please check updated answer. — Alexey Mezenin, Jan 16 '18 at 08:14

score 4 · Answer 1 · answered Jan 16 '18 at 07:52

4

You should eagerload user activities at the same time then with subquery filter based on created_at. Call the latest to get last record and you should be good to go.

$latest_activites = User::with('activites')
                         ->where("activites",function($query){

             $query->where("created_at",">=",Carbon::now()->subDays(3));

        })->latest()->get();

answered Jan 16 '18 at 07:52

Leo

7,274
5
26
48

On your first where clause if there is not column named `activities` on your database, then you will getting an error. Instead, you need to use `whereHas()` instead of using the `where`. – S Ahmed Naim Jan 03 '22 at 09:27

score 2 · Accepted Answer · answered Jan 16 '18 at 08:29

2

First, create another relationship in User model, which has to be hasOne to get the latest activity:

public function latestActivity()
{
    return $this->hasOne(Activity::class)->latest();
}

Then load just users who have latest activities older than 3 days

$users = User::whereHas('activites', function($q) {
        $q->where('created_at', '<=', now()->subDays(3));
    })
    ->whereDoesntHave('activites', function($q) {
        $q->where('created_at', '>', now()->subDays(3));
    })
    ->with('latestActivity')
    ->get();

answered Jan 16 '18 at 08:29

Alexey Mezenin

158,981
26
290
279

I'm sorry, I've incidentally added another answer instead of editing the old one. This query will get only users who have activities older than 3 days and doesn't have new ones. It will also load the latest activity for each user (just one per user). – Alexey Mezenin Jan 16 '18 at 08:30
Thank you, I guess this will work, I will try it now, is it better to loop though the activities instead of users or not ? – Peril Jan 16 '18 at 08:47
@Peril you can iterate over users and get the latest activity with `$user->latestActivity` without any additional DB queries. – Alexey Mezenin Jan 16 '18 at 08:49
Thank you alexey, I still have problem, I get the users twice, I tried to make groupBy but it didnt work – Peril Jan 16 '18 at 09:04
@Peril you can't get users twice with my code. Just copy paste it and do not use `groupBy`. Does it work if you do not modify it? – Alexey Mezenin Jan 16 '18 at 09:04

score 1 · Answer 3 · answered Jan 16 '18 at 08:06

main structure: model::latest()->get()

  $latest_activites = User::with('activites')
                    ->where("activites",function($query){
                     .
                     .
                     .
                     .
            })->latest()->get();

Laravel - get last row using whereHas

3 Answers3

Linked