Laravel Optional WHERE clause

Question

Is there a way to achieve an optional WHERE in Laravel, this is my query

$people = \App\people::query()
        -> select ('people.name', 'people.username', 'price', 'service','people.city', 'people.streetaddress', 'people.postalcode', DB::raw("GROUP_CONCAT(DATE_FORMAT(datetime, '%H:%i')) as times"))
        -> groupBy('people') 
        -> leftjoin ('services' , 'people.id', '=', 'services.people_id')
        -> leftjoin ('imagesforpeople' , 'people.id', '=', 'imagesforpeople.people_id')
        -> whereDate ('datetime', '=', $request->get('date'))
        -> whereTime ('datetime', '=', $request->get('time'))
        -> get();

Here, I want this line to be optional

-> whereTime ('datetime', '=', $request->get('time'))

So, if the search does not contain a time, it will completely ignore this line of query.

whereDate is required, but whereTime is not required. If the time request is obtained, it will query for time, but if the time request is not obtained it will ignore the whereTime query, and will show result for whereDate only.

How can I achieve this in Laravel?

Answer 1

As per https://laravel.com/docs/5.3/queries#conditional-clauses

->when($request->get('time'), function($query) use ($request) {
     $query->whereTime('datetime', '=', $request->get('time')); 
})

Answer 2

The simple thing to do is:

$peopleQuery = \App\people::query()
    ->select('people.name', 'people.username', 'price', 'service','people.city', 'people.streetaddress', 'people.postalcode', DB::raw("GROUP_CONCAT(DATE_FORMAT(datetime, '%H:%i')) as times"))
    ->groupBy('people') 
    ->leftjoin('services', 'people.id', '=', 'services.people_id')
    ->leftjoin('imagesforpeople', 'people.id', '=', 'imagesforpeople.people_id')
    ->whereDate('datetime', '=', $request->get('date'));

if ($request->has("time")) {
    $peopleQuery->whereTime('datetime', '=', $request->get('time'));
}

$people = $peopleQuery->get();

Answer 3

You can use the 'when' method. Read up on it here : https://laravel.com/docs/5.5/queries#conditional-clauses

$people = \App\people::query()
            -> select ('people.name', 'people.username', 'price', 'service','people.city', 'people.streetaddress', 'people.postalcode', DB::raw("GROUP_CONCAT(DATE_FORMAT(datetime, '%H:%i')) as times"))
            -> groupBy('people') 
            -> leftjoin ('services' , 'people.id', '=', 'services.people_id')
            -> leftjoin ('imagesforpeople' , 'people.id', '=', 'imagesforpeople.people_id')
            -> whereDate ('datetime', '=', $request->get('date'))
            -> when($request->get('time'), function($query) use($request) { 
                 $query->where('datetime', '=', $request->get('time')
             })-> get();