Laravel Eloquent检查多个orWhere语句，然后在两个日期之间进行排序

Question

I am trying to run a query in which I find out what positions a person held between two dates. Here is what I have so far:

$startDate = Carbon::parse($start)->toDateString();
      $endDate = Carbon::parse($end)->toDateString();

$user = Auth::id();

    $Positions = Position::whereBetween('shootDate', array($startDate, $endDate))->where('photographer_id', $user)->orWhere('photoEditor_id', $user)->orWhere('videographer_id', $user)
            ->orWhere('videoEditor_id', $user)->orWhere('scheduler_id', $user)->orWhere('drone_id', $user)->orWhere('webpage_id', $user)
            ->orWhere('flyer_id', $user)->orWhere('zillow_id', $user)->get();

I know this isn't going to work because of the orWhere statements basically override the whereBetween command but I don't know how to get around this.

I need to either get the list between the two dates, then see if the user held ANY of those positions, or get All of the positions that the user has, AND THEN only include those between the two dates. Does anyone know how I can accomplish this? Currently, this query returns all of the positions the user held even those outside of the dates.

Answer 1

I understand from your question that you need a query like this

SELECT * 
FROM position
WHERE (
    photographer_id = $user OR
    photoEditor_id = $user OR
    videographer_id = $user OR
    videoEditor_id = $user OR
    scheduler_id = $user OR
    drone_id = $user OR
    webpage_id = $user OR
    flyer_id = $user OR
    zillow_id = $user
    ) AND
    shootDate BETWEEN $startDate AND $endDate

I made some test with other tables and you can try something like this

$Positions = Position::where(function($query){
            $query->where('photographer_id', $user);
            $query->orWhere('photoEditor_id', $user);
            $query->orWhere('videographer_id', $user);
            $query->orWhere('videoEditor_id', $user);
            $query->orWhere('scheduler_id', $user);
            $query->orWhere('drone_id', $user);
            $query->orWhere('webpage_id', $user);
            $query->orWhere('flyer_id', $user);
            $query->orWhere('zillow_id', $user);
        })
        ->whereBetween('shootDate', array($startDate, $endDate))
        ->get();

Hope this can help!

I'll wait your feedback