Laravel 6 Eloquent Query Using WHERE with AND OR AND?

Question

I want to show restaurants which are currently open. How do I say in laravel:

WHERE (open = '00:00:00' and close = '00:00:00' ) OR (open < $currentTime and close > $currentTime)

I write it like this:

->where(function ($query) {
    $query->where('owh.open', '=', '00:00:00')
          ->whereAnd('owh.close', '=', '00:00:00');
})->orWhere(function ($query) use ($currentTime) {
    $query->where('owh.open', '<', $currentTime)
          ->whereAnd('owh.close', '>', $currentTime);
})

but it doesnt give me correct data.

score 0 · Answer 1 · answered Mar 16 '20 at 12:42

Use below

->where(function ($query) {
    $query->where('owh.open', '=', '00:00:00')
        ->where('owh.close', '=', '00:00:00');
})->orWhere(function ($query) use ($currentTime) {
    $query->where('owh.open', '<', $currentTime)
        ->where('owh.close', '>', $currentTime);
})

score 0 · Answer 2 · answered Mar 17 '20 at 07:59

I found answer to my own question

->whereIn('objects.id', [
            DB::raw("SELECT object_id FROM (SELECT object_id,
                   (CONCAT('$date', ' ', open)::timestamp) as open,
                   (
                        CASE WHEN open > close THEN
                            (CONCAT('$date', ' ', close)::timestamp + interval '1' day)
                        ELSE (CONCAT('$date', ' ', close)::timestamp) END
                    )
                    as close
            FROM object_work_hours WHERE type=$currentWorkHourType AND deleted_at IS NULL) as tb1 WHERE '$currentDate'::timestamp BETWEEN open AND close OR (open::time = '00:00:00' AND close::time = '00:00:00')")
        ])

There is

$currentWorkHourType = date('N');
$currentDate = Carbon::now()->format('Y-m-d H:i:s');
$date = date('Y-m-d');

Laravel 6 Eloquent Query Using WHERE with AND OR AND?

2 Answers2