Grouping abscence results by date

Do you want to have a list per day, since you compare the data between two dates?

It's a bit unclear to me.

Yeah that is what I want. is it possible to make this query? you mean I shouldnt use whereBetween() ???

The easiest way would be to flip it around, and group using php

Something like

$posts = Post::with('user')->whereBetween('date',[$first_date,$last_date])->groupBy('date')->groupBy('user_id')->get();

$data = $posts->groupBy('date');

Hey thank you for answering! I think your query is isnt really what I am looking for since it gets all the posts WITH users. And the groupBy methods arent working properly

If you simply want to find the list of users who did not post within the time period, a whereNotIn would be the easiest option...

$postUsers = Post::whereBetween('date', [$first_date, $last_date])->pluck('user_id')->toArray();
$users = User::whereNotIn('id', $postUsers);

However if you really need to know the users that didn't post on each specific date in a range, that's a bit more involved. I'm not sure offhand how you'd do that in SQL, though I suspect it's possible. Very likely it'd be easier (albeit not more performant) to simply run one query per date in the range.

In that case the above queries would be basically the same, you'd just loop for each date and find users who did post, then use that to drive a whereNotIn clause to find the missing users for that date.

So long as your ranges aren't huge, and user list isn't massive, that should be performant enough for most things... and results could be cached so that the queries only need to be run once for each new day, basically.. Otherwise you're likely looking at having to generate some stored procedure(s) to get it done in the DB I think.