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

Answer 1

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

Answer 2

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