Laravel 5.4 Eloquent filter from a single input

Question

I'm trying to make a query using laravel eloquent to join companies table with addresses and telephone numbers so a user can type into a single input field e.g address line or number and it will query across multiple tables/columns and only return ones which have a potential match.

I've looked into the wherehas but it seems to stop all results if both numbers and address tables have a match each. I can get it to work I believe with a standard left join on these tables but I want a clean solution if possible. here is what i'm working with below which doesn't actually return any filtered results so i'm scratching my head a little.

The goal is a single input for ease of use but searching across multiple columns/tables and returning rows only with a match. Is there a wherehas option but for multiple conditions maybe? hope someone can understand what i'm trying to do.

public function addresses(){
    return $this->hasMany(Company_addresses::class, 'company_id', 'id');
}

public function digits(){
    return $this->hasMany(Company_digit::class, 'company_id', 'id');
}

public static function search($request)
{
    $filters = $request->all();
    $sortby = isset($filters['sortby']) ? $filters['sortby'] : 'created_at';
    $orderby = isset($filters['orderby']) ? $filters['orderby'] : 'desc';

    return static::with([
        'addresses' => function ($query) use ($filters) {
        $query->where('address_line_1', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('address_line_2', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('city', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('county', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('post_code', "LIKE", '%'.$filters['name'].'%');
        },
        'digits' => function ($query) use ($filters) {
        $query->where('number', "LIKE", '%'.$filters['name'].'%')
            ->orWhere('extension', "LIKE", '%'.$filters['name'].'%');
        }])
        ->orderBy('companies.'.$sortby, $orderby)
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type'])
        ->paginate(20);
}

How are you accessing this method in your controller? Do you just need the parent (company) records which satisfy the condition or even the child records along with it? — Sandeesh, Jun 04 '17 at 17:17
public function search(Request $request) { if ($request->ajax()) { return Company::search($request); } } it's just an ajax pagination i'm doing but it only pulls the company table in the overview. I just need the parent which satisfies the conditions for now. the answer below from devk seems to be right for me — JonasJ, Jun 04 '17 at 17:46

DevK · Accepted Answer · 2017-06-05T14:32:46.543

This is how you would do it with relationship methods.

You indeed need to use ->whereHas(), but you need to wrap both ->whereHas('addresses', ...) and ->orWhereHas('digits', ...) in a ->where(function ($q) ...) method.

Like this:

public static function search($request)
{
    $filters = $request->all();
    $sortby = isset($filters['sortby']) ? $filters['sortby'] : 'created_at';
    $orderby = isset($filters['orderby']) ? $filters['orderby'] : 'desc';

    return self::where(function ($q) use ($filters) {
            $q->whereHas('addresses', function ($q) use ($filters) {
                $q->where('address_line_1', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('address_line_2', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('city', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('county', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('post_code', "LIKE", '%'.$filters['name'].'%');
            })
            ->orWhereHas('digits', function ($q) use ($filters) {
                $q->where('number', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('extension', "LIKE", '%'.$filters['name'].'%');
            });
        })
        ->with(['addresses', 'digits'])
        ->orderBy('companies.'.$sortby, $orderby)
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type'])
        ->paginate(20);
}

While the upper code should work, I would recommend rewriting the search method from static method to a scope. I would definitely rewrite it in a way to keep $request out of it as request has nothing to do with the model.

Like this:

// Use it like: \App\Company::search($request->all())->paginate(20);
public function scopeSearch($q, $filters)
{
    $sortBy = isset($filters['sortby']) ? $filters['sortby'] : 'created_at';
    $orderBy = isset($filters['orderby']) ? $filters['orderby'] : 'desc';

    return $q->where(function ($q) use ($filters) {
            $q->whereHas('addresses', function ($q) use ($filters) {
                $q->where('address_line_1', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('address_line_2', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('city', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('county', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('post_code', "LIKE", '%'.$filters['name'].'%');
            })
            ->orWhereHas('digits', function ($q) use ($filters) {
                $q->where('number', "LIKE", '%'.$filters['name'].'%')
                    ->orWhere('extension', "LIKE", '%'.$filters['name'].'%');
            });
        })
        ->with(['addresses', 'digits'])
        ->orderBy('companies.'.$sortby, $orderby)
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type']);
}

Seems like its working how i want. Thanks! I put the wherehas query inside an if isset filters['name'] so it only filters if the input has anything else shows all. — JonasJ, Jun 04 '17 at 17:44
Glad to help. Btw see the edit (a small rewrite to a scope). Also flag it as accepted if it's what you needed :) — DevK, Jun 04 '17 at 17:45
have done thanks alot! Ah i will try the scope. I had post values from the controller i wanted to be done in the query so wasn't 100% if I was doing it the right way :) — JonasJ, Jun 04 '17 at 17:48
Trying your scope code but getting Undefined variable: filters. Is it because it's lacking a second condition on // \App\Company::search($request->all())->paginate(20); ? — JonasJ, Jun 04 '17 at 18:07
Hmm, no that shouldn't be it. The first parameter (`$q`) gets auto inserted, you only need to provide 1 parameter. Can you show how you've used it? — DevK, Jun 04 '17 at 18:09
yeah my mistake but still got the error. so the scope is in the model, i'm then using your code for the ajax response in the controller. public function search(Request $request) { if ($request->ajax()) { return \App\Company::search($request->all())->paginate(20); } }I tried putting a condition for if the input is empty but still the same — JonasJ, Jun 04 '17 at 18:17
Ah found the issue. use ($filters) was needed on the first where has. was wondering why it wouldn't work! :D — JonasJ, Jun 04 '17 at 18:35

score 0 · Answer 2 · answered Jun 04 '17 at 17:57

Since you got the answer before i could even understand. Here's a simple refactor for the code which i was gonna post after clarification on the question.

public static function search()
{
    $name = request('name');

    return static::whereHas('addresses', function ($query) use ($name) {
            $query->where('address_line_1', "like", "%{$name}%")
                ->orWhere('address_line_2', "like", "%{$name}%")
                ->orWhere('city', "like", "%{$name}%")
                ->orWhere('county', "like", "%{$name}%")
                ->orWhere('post_code', "like", "%{$name}%");
        })->orWhereHas('digits', function ($query) use ($name) {
            $query->where('number', "like", "%{$name}%")
                ->orWhere('extension', "like", "%{$name}%");
        })
        ->orderBy('companies.'.request('sortby', 'created_at'), request('orderby', 'desc'))
        ->select(['id', 'registered_name', 'trading_name', 'created_at', 'type'])
        ->paginate(20);
}

While that should work I would strongly recommend [decoupling](https://softwareengineering.stackexchange.com/questions/244476/what-is-decoupling-and-what-development-areas-can-it-apply-to) `request` and model. As well as try to avoid [this type](https://stackoverflow.com/questions/752758/is-using-a-lot-of-static-methods-a-bad-thing) of static methods wherever possible. — DevK, Jun 04 '17 at 18:12

Laravel 5.4 Eloquent filter from a single input

2 Answers2