how to make query with different options laravel

Question

i want get query by selected different parameters

but right now it just filtering seperatly. This is my controller:

    $cityId = $request->input('city_id');
    $category = $request->input('categories');
    $search = $request->input('search');

    if(isset( $cityId) || isset($category) || isset($search )){
        $companies = \App\Company::orWhere('city_id', '=', $cityId)
        ->orWhereHas('categories',
        function ($query) use ($category) {
            $query->where('id', $category);
        })->orWhere('name', '=', $search)
        ->get();
     }else{
        $companies = \App\Company::all();
     }`

Right now if I select city and category works fine, but if I also search for name, it should bring me just that company name from city/category list or empty.

My view looks like this:

    `{!! Form::open([ 'action' => 'HomePageController@index', 'method' => 'get']) !!}

<div class="container">
    <div class="col-xs-2 form-inline">
        {!! Form::label('city_id', trans('quickadmin.companies.fields.city').'', ['class' => 'control-label']) !!}
        {!! Form::select('city_id', $cities, old('city_id'), ['class' => 'form-control select2']) !!}
    </div>

    <div class="col-xs-3 form-inline">
        {!! Form::label('categories', trans('quickadmin.companies.fields.categories').'', ['class' => 'control-label']) !!}
        {!! Form::select('categories', $categories, old('categories'), ['class' => 'form-control select2']) !!}
    </div>
    <div class="col-xs-3 form-inline">
        {!! Form::label('search', trans('quickadmin.companies.fields.name').'', ['class' => 'control-label']) !!}
        {!! Form::text('search', old('search'), ['class' => 'form-control', 'placeholder' => 'Search']) !!}
        <p class="help-block"></p>
    </div>
    <div class="form-inline">
        <div class="col-xs-2">
            <button type="submit"
                    class="btn btn-primary">
                    Search
            </button>
        </div>
    </div>
</div>

{!! Form::close() !!}

</div>
<br>
@if (count($companies) > 0)
    @foreach ($companies as $company)
    <div class="container">
        <div class="row">
            <div class="col-xs-2">@if($company->logo)<a href="{{ asset(env('UPLOAD_PATH').'/' . $company->logo) }}" target="_blank"><img src="{{ asset(env('UPLOAD_PATH').'/thumb/' . $company->logo) }}"/></a>@endif </div>
            <div class="col-xs-10">
                {{$company->name}}
                <br>
                {{$company->address}}, {{ $company->city->name or '' }}
                <br>
                @foreach ($company->categories as $singleCategories)
                    <span class="label label-info label-many">{{ $singleCategories->name }}</span>
                @endforeach
                <br>
                {{$company->description}} 
                <hr>    
            </div>
        </div>
    </div>`

score 0 · Answer 1 · answered Nov 28 '17 at 10:09

Since you are using orWhere, also names that are not included in the search query are returned. Since you're telling that you need just the company with the name that is entered in the search field this doesn't seem to be the desired behaviour.

Change

orWhere('name', '=', $search)

to

where('name', '=', $search)

kunal · Answer 2 · 2017-11-28T10:20:59.093

0

If i understand your problem. You have to try below code.. Always try to partition the query when you do filtering of data.

$cityId = $request->input('city_id');
$category = $request->input('categories');
$search = $request->input('search');

$companies = DB::table('companies');
if(isset($cityId) && !empty($cityid)){
    $companies = $companies->where('city_id',$cityid);
 }
if(isset($category) && !empty($category)){
    $companies = $companies->orWhereHas('categories',
    function ($query) use ($category) {
        $query->where('id', $category);
    });
 }

 if(isset($search) && !empty($search)){
    $companies = $companies->where('name', $search)
}
$companies = $companies->get();
dd($companies);

Hope it helps!

edited Nov 28 '17 at 10:20

answered Nov 28 '17 at 10:15

kunal

4,122
12
40
75

i think you are right, but firstly i will check. Right now i have 3 columns(city_id, categories and name). So i have to make different statement for each pair? for example: if i have just cityId, thant if i have cityId and categories, than if i have cityId, categories and name? there is no simplier way? – Darius Nov 28 '17 at 10:24
this is very simple way to do.. filters work like in this way only to more clear view and execution is also fast @Darius – kunal Nov 28 '17 at 10:26
this is the way i used to follow in filters @Darius – kunal Nov 28 '17 at 10:28
Can you explane why you use this: && !empty($cityid) – Darius Nov 28 '17 at 10:32
it is checking that varibale having some value or not @Darius – kunal Nov 28 '17 at 10:32
@Darius don't forget to accept and upvote the right answer – kunal Nov 28 '17 at 10:43
i will, but firstly i will try it. why is not enought just use isset($category)? – Darius Nov 28 '17 at 10:45
yes you can use isset and !empty having some difference isset is checking that $category is exists or not and !empty($category) checks the category have some value or not like $category= 'some value'; @Darius hope you understand the difference for more info :- https://stackoverflow.com/questions/7191626/isset-and-empty-what-to-use – kunal Nov 28 '17 at 10:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/159981/discussion-between-kunal-and-darius). – kunal Nov 28 '17 at 10:50

score 0 · Accepted Answer · answered Nov 28 '17 at 13:13

There I found my answer: https://laracasts.com/discuss/channels/eloquent/multiple-where-clauses-from-get-variables

Hope it will help for somebody.

I just updated my model with function:

public function scopeFilterByRequest($query, Request $request)
{
    if ($request->input('city_id')) {
        $query->where('city_id', '=', $request->input('city_id'));
    }

    if ($request->input('categories')) {
        $query->whereHas('categories',
        function ($query) use ($request) {
            $query->where('id', $request->input('categories'));
        });
    }

    if ($request->input('search')) {
        $query->where('name', '=', $request->input('search'));
    }

    return $query;
}

And simply added to my controller:

$companies = \App\Company::filterByRequest($request)->paginate();

how to make query with different options laravel

3 Answers3