Adding whereIn to orWhere functions in database query

I am struggling to add a whereIn to a more complex query:

      $events = Toolplan::query()
        ->select('id', 'eventId', 'resourceId', 'title', 'start', 'end')
        ->where(function ($query) use ($start, $end) {
            $query->whereDate('start', '>=', $start)->whereDate('end', '<=', $end);
        })
        ->orWhere(function ($query) use ($start, $end) {
            $query->whereDate('start', '<=', $start)->whereDate('end', '>=', $end);
        })
        ->orWhere(function ($query) use ($start, $end) {
            $query->whereDate('end', '>=', $start)->whereDate('end', '<=', $end);
        })
        ->orWhere(function ($query) use ($start, $end) {
            $query->whereDate('start', '>=', $start)->whereDate('start', '<=', $end);
        }) 
// gives "General error: 1096 No tables used":
        ->whereIn('eventId', function ($query) use ($eventIds){
           $query->whereIn('eventId', $eventIds);	
        })
// no effect: 
//      ->whereIn('eventId', $eventIds)
        ->get();

I tried to add it as a function and in simple form but the function leads to General error: 1096 No tables used and the other shows no effect at all, because the collection is far too large and should be only 2 items (for a certain timespan) after whereIn and the orWhere selection. $eventIds is a flat array:

["P000018","P000054","P000021","P000030"]

guybrush_threepwood

5 years ago

Best Answer

Level 33

Hi @loomix

You should wrap your orWhere stataments in a callback:

      $events = Toolplan::query()
        ->select('id', 'eventId', 'resourceId', 'title', 'start', 'end')
        ->where(function ($query) use ($start, $end) {
            $query
                ->where(function ($q) use ($start, $end) {
                    $q->whereDate('start', '>=', $start)->whereDate('end', '<=', $end);
                })
                ->orWhere(function ($q) use ($start, $end) {
                    $q->whereDate('start', '<=', $start)->whereDate('end', '>=', $end)
                });
                ->orWhere(function ($q) use ($start, $end) {
                    $q->whereDate('end', '>=', $start)->whereDate('end', '<=', $end);
                });
                ->orWhere(function ($q) use ($start, $end) {
                    $q->whereDate('start', '>=', $start)->whereDate('start', '<=', $end);
                });
        })
        ->whereIn('eventId', $eventIds)
        ->get();

This should return rows if the query matches at least one of the date conditions AND the Event ID condition (both are required).

1 like

Loomix

5 years ago

Level 1

I am pretty sure the orWhere conditions are not repetitive. It's for getting overlapping events in a FullCalendar timespan view, so it's about 1) overlapping to the left, 2) to the right, 3) overlapping left and right and 4) being fully in timespan. Will try the callback, thanks.

guybrush_threepwood

5 years ago

Level 33

Yes, sorry, I misread. Please revise my edited answer.

Loomix

5 years ago

Level 1

Yeah, it works, thank you!

1 like

guybrush_threepwood

5 years ago

Level 33

Great, you're welcome! :)

Please or to participate in this conversation.