CODEWITHSUNDEEP
phpsqllaravel-4
user2002495
user2002495

Reputation: 2146

Laravel select records older than 5 minutes?

I have Laravel app where i check user loggedin regularly with a heartbeat for every 3 seconds (demo purpose, actually 5 minutes). For each beat I check if user's last activity with current time is also 5 minutes. If it is, log them out.

Here's my code:

$result = DB::table('db_user')->where('id_user','=',Session::get('id_user'))->where('last_activity','>=',"now() - interval 5 minute")->get();

    if(!empty($result)) 
        return Redirect::to('/logout');
    else
        return Response::json('still-alive');

My problem is, this doesn't work. If I change the operand to <=, it will log me out immediately before 5 minutes, if the operand is >=, even after 5 minutes it won't log me out, can anyone explain why?

-EDIT-

Thanks for all the answers, I solved my problems by modifying my code to:

$result = DB::table('db_user')->where('id_user','=',Session::get('id_user'))->first();

    if(!empty($result)) 
        if(strtotime($result->last_activity) < strtotime("-5 minutes"))
            return Redirect::to('/logout');
        else 
            return Response::json('still-alive');
    else
        return Response::json('no-record');

Upvotes: 19

Views: 51128

Answers (3)

user570605
user570605

Reputation: 768

You can use whereRaw():

$result = DB::table('db_user')->where('id_user','=',Session::get('id_user'))->whereRaw('last_activity >= now() - interval 5 minute')->get();

or

$result = DB::table('db_user')->where('id_user','=',Session::get('id_user'))->whereRaw('last_activity >= now() - interval ? minute', [5])->get();

Upvotes: 4

Ben Wilkins
Ben Wilkins

Reputation: 866

The accepted answer is correct, but you could make it even cleaner by using a query scope on the User model (assuming that you have a User model)...

$result = User::currentUser()->activityOlderThan(self::HEARTBEAT_INTERVAL)->get();

Your User model would then have these functions:

public function scopeCurrentUser($query)
{
    return $query->where('id_user', '=', Session::get('id_user'));
}

And

public function scopeActivityOlderThan($query, $interval)
{
    return $query->where('last_activity', '>=', Carbon::now()->subMinutes($interval)->toDateTimeString());
}

Now your code is clean and easy to read :-)

Upvotes: 52

Glad To Help
Glad To Help

Reputation: 5387

Assuming your business logic is correct, try using PHP instead of an SQL string in the where clause:

$date = new DateTime;
$date->modify('-5 minutes');
$formatted_date = $date->format('Y-m-d H:i:s');

$result = DB::table('db_user')->where('id_user','=',Session::get('id_user'))->where('last_activity','>=',$formatted_date)->get();

Also, it is a good practice to always output the executed SQL queries just to make sure Laravel behaves as expected:

$queries = DB::getQueryLog();

Upvotes: 40

Related Questions