#Laravel #Eloquent #PHP

How to use a WHERE on a relationship in Laravel Eloquent

Article author Jelle de Vries
Jelle de Vries
August 26, 2022

When building an application with Laravel and using Eloquent, sooner or later you will use a WHERE clause on a model's relationship. For example:

  • Get all products with product category 'shoes' or 'socks'
  • Get all customers with pending orders

2. Using 'whereHas' and 'withWhereHas'

Laravel offers two approaches when using WHERE clauses on relationships: Lazy Loading and Eager Loading. The difference between Lazy Loading and Eager Loading is that when using Eager Loading, Eloquent runs only 2 queries, one for the model ($product), and one for it's relationship ($product->categories.

When using Lazy Loading, Eloquent queries only the queried objects, and not it's relationships. The problem with Lazy Loading is that it results in more SQL queries (N+1), so it's better to use Eager Loading if you use the relationship objects in your code. Make sure to only Eager Load the relationships you actually reference.

2.1 Eager Loading

Eloquent provides the method withWhereHas. This method queries the objects, the products in this case, but also queries the specified relationship objects, in this case the categories, together with the products.

If reference and use the relationship objects after querying, use withWhereHas, instead of whereHas.

// Get all products with product category 'shoes' or 'socks' & load the relationship objects matching the conditions
$products = Product::withWhereHas('categories', function (Builder $query) {
	$query->where('name', 'shoes');
  	$query->orWhere('name', 'socks');
})->get();

2.2 Lazy Loading

As explained above, use whereHas for Lazy Loading:

// Get all products with product category 'shoes' or 'socks'
$products = Product::whereHas('categories', function (Builder $query) {
	$query->where('name', 'shoes');
  	$query->orWhere('name', 'socks');
})->get();

3. Using 'whereRelation'

Alternatively, you can use whereRelation. This method offers a solution for simple queries. Note that this method uses Lazy Loading. It offers a single WHERE clause for a model's relationship. This method can be chained.

// Get all products with product category 'shoes'
$products = Product::whereRelation('categories', 'name', 'shoes')->get();

// Get all customers with pending orders
$customers = Customer::whereRelation('orders', 'status', 'pending')->get();

Comments