Laravel event handler for last login

I recently needed to show on the admin side of an app the last login of all users and if they had logged in at all.  I also wanted to show the user the last time that they visited the site.  I used event handlers for both these tasks.

Update the Users Table

I have used the user model to store a timestamp for each event, so I added two new rows to my users table.  I will then be able to access these easily in my blade template.

$table->timestamp('latest_login')->nullable();
$table->timestamp('previous_visit')->nullable();

On the user model I also added both timestamps to the proteced $dates array. By doing this I will be able to use ‘Carbon’ helpers like ‘diffForHumans’.

protected $dates = [
        'created_at',
        'updated_at',
        'latest_login',
        'previous_visit'
    ];

Creating the Event Handlers:

On the admin side I used the login event and for the user I used the logout event to record the current time and date to the users table.

In the EventServiceProvider service provider (app/Providers/EventServiceProvider.php) I added the following to the $listen array:

'Illuminate\Auth\Events\Login' => [
    'App\Listeners\Users\LatestLogin',
],
'Illuminate\Auth\Events\Logout' => [
    'App\Listeners\Users\PreviousLogin',
],

I then ran php artisan event:generate. This command then creates two event listeners located at app\Listeners\Users\LatestLogin and app\Listeners\Users\PreviousLogin.

Each event listener’s handle()method I added the following.  

public function handle(Login $event)
    {
        $event->user->latest_login = Carbon::now();
        $event->user->save();
    }
 public function handle(Logout $event)
    {
        $event->user->previous_visit = Carbon::now();
        $event->user->save();
    }

I also added:

use Carbon\Carbon;

to each listener.

Now when a user logs in or logs out the event listener fires and updates the appropriate row of the users table for the user.

Blade

Finally for completeness in my blade file I can call:

{{ $user->latest_login->diffForHumans() }}

And it will output something like – 1 hour ago – or however long it was since the user signed in.

For the user in the view I add:

Last Visit: {{ Auth::user()->previous_visit->diffForHumans() }}

It outputs something like: Last Visit: 2 days ago

That is about it. I hope this helps someone else out.

Laravel Raw Statements for Database Queries

Laravel has a powerful query builder but there are limitations when database queries become more complex.  Enter Raw queries.

The docs have an example  of a raw query which will get you going but sometimes there is a need to completely ditch the builder and and go it alone.

The following example is what I used create a query which is completely raw.

DB::select(DB::raw('
        SELECT  Months.id AS `month` ,
            COUNT(story.id) AS `count`
            FROM 
            (
              SELECT 1 as ID UNION SELECT 2 as ID UNION  SELECT 3 as ID UNION SELECT 4 as ID 
              UNION  
              SELECT 5 as ID UNION SELECT 6 as ID UNION SELECT 7 as ID UNION SELECT 8 as ID 
              UNION  
              SELECT 9 as ID UNION SELECT 10 as ID UNION SELECT 11 as ID UNION SELECT 12 as ID
            ) as Months
            LEFT JOIN `story` on Months.id=month(story.created_at)
                                   AND 
                                   (company_id = :companyId) 
                                   AND (YEAR(created_at)= :thisYear)
            GROUP BY Months.id 
            ORDER BY Months.id ASC'), array('companyId'=>Auth::user()->company_id, 'thisYear'=>Carbon::now()->year));

The important thing to notice here is that when using the raw query you can pass variables straight into the query which could be a security issue especially if the information is being passed from a from by a user.

To ensure that any values that need to be passed to the query are sanitized they can be passed in an array after the query.

Take a look at the query and the ‘And’ clauses.

company_id = :companyId

The :companyId in the example is how you add your variables using the array.

...ORDER BY Months.id ASC'), array('companyId'=>Auth::user()->company_id, 'thisYear'=>Carbon::now()->year));

In the array give the key and then the value to be added.

That is it.  Hope it helps with your complex Laravel queries.