CODEWITHSUNDEEP
phpsqllaraveleloquentlaravel-7
PSN
PSN

Reputation: 2516

How to fetch latest record of each user in a table using Laravel eloquent

I have n users in a table and for each user, I'm saving their log-in and log-out time in a separate table. I want to get the details of the users who haven't logged-in for 2 days using Laravel eloquent.

Users table structure

id | name | email

Log table structure

id |action | user_id | created_at | updated_at

So far I have done this much:

$users = LogsData::where('action','Login')->where('created_at','<',Carbon::now()->subDays(1))->get();

But the output has users who have logged-in within 2 days also.

EDIT: I got the query, I need to convert this into eloquent.

I solved it myself. Here is the solution:

SELECT t1.* FROM actions t1
  JOIN (SELECT user_id, MAX(id) as maxid  FROM actions where action = "LOGIN" GROUP BY user_id) t2
    ON t1.user_id = t2.user_id and t1.id = t2.maxid
     where created_at < NOW() - INTERVAL 2 DAY

Upvotes: 0

Views: 3874

Answers (3)

Jems
Jems

Reputation: 1706

If you only need to get the last data of each user, you can sort the id desc and then group by user_id to get the latest data

$users = LogsData::where('action','Login')
                 ->whereDate('created_at','<',Carbon::today()->subDays(1))
                 ->orderBy('id', 'DESC')
                 ->groupBy('user_id')
                 ->get();

to use groupBy, you have to change the strict from your config value into false. But if you don't want to change your config file, this query can help you. You just need to translate it into laravel query version

SELECT * FROM log_datas AS ld WHERE ld.action = 'Login' AND ld.id IN (SELECT MAX(id) FROM log_datas WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP_BY user_id)

Upvotes: 1

MHewison
MHewison

Reputation: 856

Maybe try using eloquent relations

Take note of your namespaces make sure that App\LogsData for example is correct here

// in your User Model
public function logsData()
{
    return $this->hasMany('App\LogsData');
}

// in your LogsData Model
public function user()
{
    return $this->belongsTo('App\User');
}

public function scopeLoginActions($query)
{
    return $query->where('action', 'Login');
}

Then you can access data with


User::whereHas('logsData', function ($query) {
    $query->loginActions()->where('created_at', '<', now()->subDays(2));
})->get();

// and if you require the login records
User::with('logsData')->whereHas('logsData', function ($query) {
    $query->loginActions()->where('created_at', '<', now()->subDays(2));
})->get();

// and if you require an individual login record
User::with(['logsData' => function($query) {
    $query->loginActions()->where('created_at', '<', now()->subDays(2))->first();
})->whereHas('logsData', function ($query) {
    $query->loginActions()->where('created_at', '<', now()->subDays(2));
})->get();

Upvotes: 0

Tum
Tum

Reputation: 7585

You need to make a join to the logs table first, and because MySQL always seeks the path of least resistance, you need to join it the other way around: there may not exists log entries YOUNGER than 1 day.

$users = DB::from('users')
    ->leftJoin('logs', function ($join) {
        $join->on('users.id', '=', 'logs.user_id')
            ->where('logs.action', '=', 'Login')
            ->where('logs.created_at', '>=', Carbon::now()->subDays(2));
    })
    ->whereNull('logs.id')
    ->get();

Upvotes: 0

Related Questions