CODEWITHSUNDEEP
phpmysqllaravellaravel-5.3
Cowgirl
Cowgirl

Reputation: 1494

Laravel Optional WHERE clause

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?

Upvotes: 7

Views: 10317

Answers (3)

apokryfos
apokryfos

Reputation: 40653

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();

Upvotes: 7

pseudoanime
pseudoanime

Reputation: 1593

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();

Upvotes: 2

Kutagh
Kutagh

Reputation: 226

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')); 
})

Upvotes: 21

Related Questions