Query for calendar events for a specific month

I'm trying to get the correct calendar events with the month and year values. This is what I tried but I know it's wrong as it brings the events for the month they belong but also a bunch of adjacent ones too.

return Event::whereHas('users', function ($query) use ($request) {
            $query->where('user_id', $request->user()->id);
        })
            ->whereMonth('init_date', $request->input('month'))
            ->orWhereMonth('end_date', $request->input('month'))
            ->whereYear('init_date', $request->input('year'))
            ->orWhereYear('end_date', $request->input('year'))
            ->orderBy('init_date', 'asc')
            ->get();

I was trying this approach but I'm not sure how to apply it since my dates are a range, and I'm unsure how to handle events that go from one month to another.

$start = Carbon::createFromFormat('d/m/Y', '1/' . $request->input('month') . '/' . $request->input('year'));
$end = Carbon::parse($start)->endOfMonth();

How can I get the correct events?

LaryAI

3 years ago

Level 58

To get the correct events for a specific month, you can modify the query to check if the event starts or ends within the month. You can use the whereBetween method to check if the event starts or ends within the month. Here's an example:

$month = $request->input('month');
$year = $request->input('year');

$startOfMonth = Carbon::create($year, $month, 1)->startOfDay();
$endOfMonth = Carbon::create($year, $month, 1)->endOfMonth()->endOfDay();

return Event::whereHas('users', function ($query) use ($request) {
        $query->where('user_id', $request->user()->id);
    })
    ->where(function ($query) use ($startOfMonth, $endOfMonth) {
        $query->whereBetween('init_date', [$startOfMonth, $endOfMonth])
            ->orWhereBetween('end_date', [$startOfMonth, $endOfMonth])
            ->orWhere(function ($query) use ($startOfMonth, $endOfMonth) {
                $query->where('init_date', '<', $startOfMonth)
                    ->where('end_date', '>', $endOfMonth);
            });
    })
    ->orderBy('init_date', 'asc')
    ->get();

In this example, we first create Carbon instances for the start and end of the month. We then modify the query to check if the event starts or ends within the month using the whereBetween method. We also check if the event spans across the month by using an orWhere clause with two where clauses to check if the event starts before the month and ends after the month.

Note that this assumes that the init_date and end_date columns are of type datetime or timestamp. If they are of type date, you will need to modify the Carbon instances accordingly.

Beptum

3 years ago

Level 4

Hello I would suggest this approach:


	$month = $request->input('month');
    $year = $request->input('year');
    $startOfMonth = Carbon::create($year, $month, 1)->startOfDay();
    $endOfMonth = Carbon::create($year, $month, 1)->endOfMonth()->endOfDay();
return Event::whereHas('users', function ($query) use ($request) {
    $query->where('user_id', $request->user()->id);
})
->where(function ($query) use ($startOfMonth, $endOfMonth) {
    $query->whereBetween('init_date', [$startOfMonth, $endOfMonth])
        ->orWhereBetween('end_date', [$startOfMonth, $endOfMonth])
    })            
->orderBy('init_date', 'asc')
->get();

Please or to participate in this conversation.