Laravel getting ALL Users with Posts between two dates-CodePudding

I've got two tables related to each other:

Users:

id | name | email | phone

Posts:

id | user_id | body | start:date | end:date

They're related like: User can have many Posts:

public function posts()
{
   return $this->hasMany(Post::class)->with('comments');
}

and in Posts model:

public function users()
{
   return $this->belongsTo(User::class, 'user_id');
}

I need to get All Users with Posts where start between [Carbon::now()->firstOfMonth(), Carbon::now()->lastOfMonth()] and same condition for end column:

$users = User::whereHas('posts', function($q) use($currentMonth){
            return $q->whereBetween('start', [Carbon::now()->firstOfMonth(), Carbon::now()->lastOfMonth()])->orWhereBetween('end', [Carbon::now()->firstOfMonth(), Carbon::now()->lastOfMonth()]);
        })->get();

The main problem which I got is that I didn't get any User if condition is not OK. I need All Users, even if he doesn't have any posts, and the condition isn't valid, but the array of their Posts(or object array related) should be null.

So: I get all users with posts, if he/she matches the condition of date, get's only posts which match this, if he/she doesn't have posts or it didn't match condition in query his/her relation should be null or empty, but user should be added to collection.

Is it possible to achieve?

EDIT:

I got another problem. I've got form with the specific month page view(up to 12 future months). After selecting specific one I'm submitting form and passing it to Controller which look like that:

public function monthPage(Request $request)
    {
        $start = Carbon::parse($request['month'])->firstOfMonth();
        $end = Carbon::parse($request['month'])->lastOfMonth();

        $users = User::with(['posts' => function($q) use($start, $end){
            return $q->whereBetween('start', [$start->firstOfMonth(), $end->lastOfMonth()])
                ->orWhereBetween('end', [$start->firstOfMonth(), $end->lastOfMonth()]);
        }])->get();

        return view('posts.specific-month')
            ->with('users',$users);
    }

If Users has post with start on 20th of March and end on 20th May, the query isn't returning Posts for selection(in dropdown) of April.

I got for each month the page, so it works correct if I select March - it will display it until end of month and If I select May it will be displayed until 20th. If I select April, there's nothing to return, but this date is between 20th of March and 20th of May.

CodePudding user response：

Here you go:

$users = User::with(['posts' => function($q){
    return $q->whereBetween('start', [Carbon::now()->firstOfMonth(), Carbon::now()->lastOfMonth()])
        ->orWhereBetween('end', [Carbon::now()->firstOfMonth(), Carbon::now()->lastOfMonth()]);
}])
    ->get();

CodePudding user response：

You should create a scope in the Post model:

public function applyDateRange(Carbon $start, Carbon $end)
{
   return $this->whereBetween('start', [$start, $end])
          ->orWhereBetween('end', [$start, $end]);
}

Then call the scope in the relation like this:

$users = User::with(['posts' => function ($q) {
   $q->applyDateRange(Carbon::now()->firstOfMonth(), Carbon::now()->lastOfMonth());
}])->get();