Laravel Session Query Unique Users

Question

I noticed a bug in my application when a user logs into my site from multiple computers, they are displayed twice in the list of logged in. I managed to fix this by adding the groupBy('user_id') to my query, however I noticed something else wrong now: the user's last active field only updates for 1 of them.

return $query->whereNotNull('user_id')->with('user')->groupBy('user_id')->get();

To make this a bit clearer, prior to adding groupBy, lets say a user Bob logged in at 2 locations. My logged in users would look like this:

Bob Last Active 1 hour ago, Bob Last Active 6 minutes ago

However, with the groupBy, it might show this:

Bob Last Active 1 hour ago

Even though the latest activity is 6 mintues ago. I tried running a sortBy('last_activity'), however both ascending and descending have failed to fix this issue. Does anyone have any ideas?

Thanks.

bernie · Accepted Answer · 2015-10-05 17:25:10Z

1

It's hard to say with the question as-is because you don't explain what you're storing in that table and how you store it. I'm guessing you use a database session driver?

Anyhow, each user session is unique to a given location. This is why you get 2 entries when a user is logged in at multiple locations. A "location" here is considered different if it doesn't share cookies with another location (e.g. a private browsing window is enough). The session in Laravel is identified by the session cookie laravel_session.

What I suggest for your "last active" timestamp is to store it in your User model directly and update it when a user accesses your site. It makes much more sense to persist this uniquely with the user than trying to reconstitute the information from active sessions. Also, stale sessions are eventually removed and the information for a user would be lost.

update

group by works with aggregate functions. What you want is something like this:

SELECT user_id, MAX(last_activity) 
FROM SessionTable WHERE user_id IS NOT NULL
GROUP BY user_id

With query builder:

\DB::table('session_table')
    ->select('user_id', \DB::raw('max(last_activity) as last_activity'))
    ->whereNotNull('user_id')
    ->groupBy('user_id')
    ->lists('user_id', 'last_activity');

should return something like this:

[
 'user_id1' => 'last_activity1',
 'user_id2' => 'last_activity2',
 ...
]

edited Oct 5, 2015 at 17:25

answered Oct 5, 2015 at 15:48

bernie

10.5k7 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Logan Hasbrouck Over a year ago

I store the user_id in the sessions table and I also save the last_active to the user model so I am thinking of just using that approach. I was hoping to figure out how to achieve this through a query though simply for the sake of knowing.

bernie Over a year ago

explained groupBy a bit

Logan Hasbrouck Over a year ago

Sorry for the rather delayed response, my internet here is a little fubar right now. Anyways, I am presently attempting to figure out how to do just what you said already(hopefully not having to use raw sql). Presently, my toSql() looks like this "select * from sessions` where user_id is not null group by user_id order by last_activity desc"`

Logan Hasbrouck Over a year ago

That got it. Thanks mate.

Collectives™ on Stack Overflow

Laravel Session Query Unique Users

1 Answer 1

update

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

update

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related