Laravel query check date lesser than end date only if Not Null-CodePudding

I am working on a Laravel query and I need to get all the records which fall within a date range. So the table has both 'start_at' (Not null) and 'end_at' (Nullable) fields. This way all values have a start_At but few have a end_at value.

I want to get all values greater than start_at and (lesser than end_at if end_at value Exists). So if end_at not available, get as long as greater than start_at, or if end_at is available get date between them.

$effectiveDate = '2022-04-06 23:34:00'
GroupExtras::query()
                    ->select('group_extras.*')
                    ->join('groups', 'group_extras.group_id', 'groups.id')
                    ->where(function ($query) use ($effectiveDate) {
                        $query->where('group_extras.start_at', '<=', $effectiveDate);
                        $query->where('group_extras.end_at', '>=', $effectiveDate);
                    })
                    ->orderBy('group_extras.value')
                    ->first();

CodePudding user response：

I think what you're looking for is WhereBetween.

->whereBetween('created_at', [$start.' 00:00:00',$end.' 23:59:59'])

Set your start and end dates and that should work.

CodePudding user response：

How about adding a whereRaw with an orWhere statement? Also, I noticed that you said you wanted to get values that are greater than start_at and lesser than end_at, but you had start_at <= $effective_date and end_at >= $effective_date. I might have misunderstood you wrong though, but I changed the relations to match what your description said.

$effectiveDate = '2022-04-06 23:34:00'
GroupExtras::query()
    ->select('group_extras.*')
    ->join('groups', 'group_extras.group_id', 'groups.id')
    ->where(function ($query) use ($effectiveDate) {
        $query->whereRaw('group_extras.start_at > ' . $effectiveDate . ' AND (group_extras.end_at is not null and group_extras.end_at < ' . $effectiveDate . ')');
        $query->orWhere('group_extras.start_at', '>', $effectiveDate);
    })
    ->orderBy('group_extras.value')
    ->first();