Laravel: where statement on with relationship

Question

I want to do a query in laravel with multiple relationships and multiple where statements over the relationships but it doesn't go well.

Here's my code.

$orders = Order::with("customer")
            ->with("user")
            ->with("measuring_user")
            ->where("description", "like", "%" . $search . "%")
            ->orWhere("customers.name", "like", "%" . $search . "%")
            ->get();

I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'customers.name' in 'where clause' (SQL: select * from `orders` where (`description` like %% or `customers`.`name` like %%) and `orders`.`deleted_at` is null)

The query should filter orders where one of both conditions is true.

When i do the customer where statement in the with function it doesn't work either:

    $orders = Order::with(["customer" => function($query) use ($search) {
        $query->where("name", "like", "%" . $search . "%");
    }])
    ->with("user")
    ->with("measuring_user")
    ->where("description", "like", "%" . $search . "%")
    ->get();

In this case it founds some orders without a customer. It does the where statement on the relationship instead of the whole order query.

Hope one of you can help me out.

Use `orWhereHas` see [Querying Relationship Existence](https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence) — M Khalid Junaid, Jun 02 '21 at 13:17

score 2 · Accepted Answer · answered Jun 02 '21 at 13:22

2

Try this

$orders = Order::with('customer', 'user', 'measuring_user')->whereHas('customer', 
  function($q) use ($search) {
    return $q->where('name', 'like', '%' . $search . '%');
  })->where('description', 'like', '%' . $search . '%')->get();

Instead of using multiple with function, you can pass multiple relations to it using comma-separated values (like above).

To query on the relation use whereHas function.

answered Jun 02 '21 at 13:22

Jibin Bose

542
2
8

Thanks as well for your answer. Okay; i didn't know to query on relationships you should use whereHas. Good to know. Thanks again! – Henk-Jan Leusink Jun 02 '21 at 13:29
@Henk-JanLeusink Glad to help. – Jibin Bose Jun 02 '21 at 16:59

shaedrich · Answer 2 · 2021-06-02T13:29:38.197

That's because the relationship (separate query) is filtered but not the query itself.

$filterCustomer = function($query) use ($search) {
    $query->where('name', 'like', '%' . $search . '%');
};
$orders = Order::with([
        'customer' => $filterCustomer,
        'user',
        'measuring_user'
    ])
    ->where('description', 'like', '%' . $search . '%')
    ->whereHas('customer', $filterCustomer)
    ->get();

Laravel: where statement on with relationship

2 Answers2