Query that returns only Teams where no User has logged in within last 30 Days-CodePudding

I have Users, Teams, and Groups. Users are part of Teams and Teams can be part of Groups. I am trying to find all teams within a group where that team has not had a user that has logged in within the last 30 days. I only want the teams that have not had anyone log in during the last 30 days.

Here is the foundation of my query:

$stats->inactive_list = Team::groupFiltered()->whereHas('users',function($query){
$query->where('login_count','>', 1)  /*Add where statements*/ })->get();

Now as for Where statements:

If I use ->whereDate('last_login_at', '<', now()→subDays(30)) I can return Teams that have a User that has not logged in within the last 30 days. However, that does not mean that that same Team does not also have another User that did log in within the last 30 days. So this where statement does not return what I need for it will list any Team that has a User that hadn’t logged in within 30 even if that team has another User that has logged in within the last 30 days

If I use ->whereNotBetween('last_login_at', [now()->subDays(30), now()]) I can return Teams that have a Users that have not logged in within the last 30 days.

Or If I use ->whereBetween('last_login_at', [now()->subDays(30), now()]) I can return Teams that have a Users that has logged in within the last 30 days.

So my question is how can I construct this query so that it returns only those Teams where no User within the Team has logged in within the last 30 days.

CodePudding user response：

You can probably combine the first and the second query like this:

// this will Teams' users that has not logged in for 30 days, but the
// filtered Teams might have user(s) that logged in on that 30-day interval
$query->whereDate('last_login_at', '<', now()→subDays(30))

// this will probably filter out the Teams' which user(s) have logged in
// on that 30-day interval
$query->whereNotBetween('last_login_at', [now()->subDays(30), now()])

CodePudding user response：

That query has potential to be a server killer at scale. Think if you had 20k groups each with 20 members. You've got to check 400k users on last log in total. I'd find a way to narrow your query to be more specific. If you really need this statistic I'd add another column of last_login to the group model. Then when a user logs in just drop a current timestamp on the group column. Then your query gets much more manageable at scale and not as complex worrying about checking every single user that belongs to a group for their last log in.