CODEWITHSUNDEEP
laraveleloquentlaravel-5.1
codiaf
codiaf

Reputation: 629

Use orWhere with 'whereHas' and 'with' in eloquent

I am trying to check a search value in a renationalized query to search if it matches with 3 different columns 

    Table |  Column
-------------------
    Order     code
    Customer  name
    Customer  dni

I tried several ways but it just woks with the value name. This is what I have now:

$search = "%{$searchQuery}%";

$constraint = function ($query) use ($search){
    $query->where('name', 'like', $search)
          ->orWhere('dni', 'like', $search);
};
return Order::with(['customer' => $constraint])
                ->whereHas('customer', $constraint)
                ->orWhere('code', 'like', $search)
                ->paginate(15);

If I search by name it works, but if I put part of the dni it says 

Trying to get property of non-object

I am accessing the value in the view like this:

{{ $order->customer->dni }}

or for example

{{ $order->code }}

Upvotes: 3

Views: 1440

Answers (4)

codiaf
codiaf

Reputation: 629

I ended up solving it like this:

public function search($searchQuery)
{
    $search = "%{$searchQuery}%";

    return Order::with('customer')
                    ->orWhereHas('customer', function($query) use ($search){
                        $query->where('dni', 'like', $search);
                    })
                    ->orWhereHas('customer', function($query) use ($search){
                        $query->where(DB::raw('CONCAT_WS(" ", name, lastname)'), 'like', $search);
                    })
                    ->orWhere('code', 'like', $search)
                    ->paginate(15);
}

Don't know if it has performance issues...

Upvotes: 0

Trip
Trip

Reputation: 2016

This should work if you remove the $constraint from the with() statement.

$search = "%{$searchQuery}%";

$constraint = function ($query) use ($search){
    $query->where('name', 'like', $search)
          ->orWhere('dni', 'like', $search);
};
return Order::with('customer')
                ->whereHas('customer', $constraint)
                ->orWhere('code', 'like', $search)
                ->paginate(15);

I have not tested this solution but everything else in your code samples looks to be correct.

EDIT: If this does not fix the problem, it would be helpful to see what else is going on in your template. You might need dd() your $order and/or $order->customer to see what values are making it into your template.

Upvotes: 0

smartrahat
smartrahat

Reputation: 5609

I assume you are getting search data from a input text box named search.

Your code in controller should be like this:

$search = Input::get('search');

$constraint = function ($query) use ($search){
    $query->where('name', 'like', '%'.$search.'%')
        ->orWhere('dni', 'like', '%'.$search.'%');
};
return Order::with(['customer' => $constraint])
    ->whereHas('customer', $constraint)
    ->orWhere('code', 'like', '%'.$search.'%')
    ->paginate(15);

This code is not tested.

Upvotes: 0

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81167

You're messing the whereHas subquery by using orWhere. It's something like this now: where fk = pk and name like $search or dni like $search, while you need to enclose (name like $search or dni like $search):

$constraint = function ($query) use ($search){
  $query->where(function ($wheres) use ($search) {
    $wheres->where('name', 'like', $search)
          ->orWhere('dni', 'like', $search);
  });
};

Upvotes: 5

Related Questions