php - Laravel 5.7 中具有多个值的 Where 子句

Question

我正在尝试为公交系统制作一份报告，该报告的表格有“司机”和“路线”字段。当我选择“Driver”获取报告或仅选择“Route”获取报告时它工作正常，但是当我选择“Driver and Route”时出现问题。任何帮助将不胜感激

View /表单

<form action="" method="get">
    <div class="row">
        <div class="col-md-3 form-group">
            <label>From Date:</label>
            <input type="date" name="start" class="form-control">
        </div>
        <div class="col-md-3 form-group">
            <label>To Date:</label>
            <input type="date" name="end" class="form-control">
        </div>
    </div>
    <div class="row">
        <div class="col-md-3" id="route_content">
            <label>Route</label>
            <select name="routeid" id="route_id" class="js-example-placeholder-singleuserid js-states form-control"
                    style="width: 100%; height:40px;">
                <option></option>
                <?php foreach ($routes as $key => $value): ?>
                <option value="{{$value->id}}">{{$value->from}} -> {{$value->to}}</option>
                <?php endforeach ?>
            </select>
        </div>
        <div class="col-md-3" id="driver_content" z>
            <label>Driver</label>
            <select name="driverid" id="driver_id" class="js-example-placeholder-single js-states form-control"
                    style="width: 100%; height:40px;">
                <option></option>
                <?php foreach ($driver as $key => $value): ?>
                <option value="{{$value->id}}"> {{$value->name}} </option>
                <?php endforeach ?>
            </select>
        </div>
    </div>
    <div class="row">
        <div class="col-md-3 form-group">
            <button class="btn btn-info btn-md" style="margin-top: 27px;">Search</button>
        </div>
    </div>
</form>

路线

 Route::get('post_genral_report/{start?}/{end?}' , 
'reportController@post_genral_report')->name('abd');

Controller

public function post_genral_report(Request $request, $start = null, $end = null)
{
    $data = DB::table('registration_tickets')
        ->join('trips', 'trips.id', 'registration_tickets.trip_id')
        ->join('buses', 'buses.id', 'trips.bus_id')
        ->join('drivers', 'drivers.id', 'trips.driver_id')
        ->join('routes', 'routes.id', 'trips.route_id')
        ->join('provinces as p1', 'p1.id', 'routes.from')
        ->join('provinces as p2', 'p2.id', 'routes.to')
        ->select('registration_tickets.*', 'p1.name as from', 'p2.name as to', 'buses.type', 'buses.plate',
            'drivers.name as d_name', 'drivers.lastname as lname');

    if ($start and $end) {
        $data->whereBetween('registration_tickets.date', [
            $start,
            $end
        ]);
    }

    if ($request->driverid) {
        $data->where('trips.driver_id', $request->driverid);
    }

    if ($request->routeid) {
        $data->where('trips.route_id', $request->routeid);

    }

    if ($request->driverid and $request->routeid) {
        $data->where([
            'trips.driver_id', '=', $request->driverid,
            'trips.route_id', '=', $request->routeid
        ]);
    }

    $data = $data->get();

    return Datatables::of($data)->make(true);
}

Answer 1

You need to modify the part where you are using both driver_id and route_id to below to achieve your purpose (because as per your code, it seems that you want the results when both of route_id or driver_id falls between the start and end date, however your code checks the driver_id and route_id individually for where condition. You need to wrap them in a where and use nested where in it):

if ($request->driverid and $request->routeid) {
    $data->where(function($q) use ($request)
    {
        $q->where('trips.driver_id', '=', $request->driverid)
        ->where('trips.route_id', '=', $request->routeid);
    });
}