0

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 4 items after whereIn and the orWhere selection. $eventIds is a flat array:

["P000018","P000054","P000021","P000030"]
Rome
  • 432
  • 6
  • 27
  • you don't need to pass a function for your `whereIn`; if the result you are getting is too large then it's definitely a matter of grouping since you're using orWhere you need to get your `where`s grouped correctly to get the expected result – ahmad Jun 28 '20 at 16:29
  • could you please give a short example? i would like to do the `whereIn` first then the `orWheres`. how to provide the first result to the `orWhere`query? – Rome Jun 28 '20 at 17:09
  • 1
    before you call `->get()`, you can call `->toSql()` and print the output, it's the generated query, you can verify if it contains the `where in` clause or not. your query is not much complex , it should work. – Majed DH Jun 30 '20 at 09:49
  • @MajedDH Thanks, this is helpful but I have a question: When I replace `->get()` for `->toSql()` in the above query, `$start`, `$end` and `$eventIds` are replaced with `?` in the Sql printout, e.g. it prints `eventId in (?, ?, ?, ?)` for the `whereIn` (while result of 4 items is correct). How can I make these variables visible in the Sql printout? – Rome Jun 30 '20 at 10:08
  • 1
    Take a look here: https://stackoverflow.com/questions/27314506/laravel-how-to-get-query-with-bindings – Majed DH Jun 30 '20 at 10:17

0 Answers0