We use cookies on this site to enhance your user experience
By clicking the Accept button, you agree to us doing so. More info on our cookie policy
We use cookies on this site to enhance your user experience
By clicking the Accept button, you agree to us doing so. More info on our cookie policy
Published: Nov 1, 2023 by C.S. Rhymes
Laravel Many To Many Relationships
This post is part of a series of posts about Laravel Eloquent many to many relationships.
Querying Laravel Eloquent's Many to Many relationships
A while ago I wrote about how to use a many to many relationship in Laravel, (all the way back in April 2019). Now I want to build on this example and show how you can query many to many relationships, but also how you can add additional constraints to the query to further filter your results.
In the previous article, we had breads and sandwich fillers, both with many to many relationships. A bread type belongs to many fillings and a sandwich filler belongs to many bread types. Hopefully it won’t make you too hungry whilst reading this.
What if we wanted to find the bread types that have fillings?
For this we can use has()
and pass in the relationship name that is defined in our Bread model. In this case it’s ‘sandwichFillers’:
$breads = Bread::has('sandwichFillers')->get();
This will return any breads that have at least one sandwich fillers.
Building on the previous example, if we want to retrieve breads with two sandwich fillers, then we can pass in additional parameters to the has()
. After the relationship name, we pass in the operator and then the count.
$breads = Bread::has('sandwichFillers', '=', 2)->get();
This will then return us the breads that have exactly two sandwich fillers.
If we wanted to return the breads with at least 2 sandwich fillers then we update the operator from equal to, to greater than or equal to.
$breads = Bread::has('sandwichFillers', '>=', 2)->get();
If we want to search for bread with a particular sandwich filler then we can use whereHas()
, instead of has()
, and pass in the relationship name and then a function for the query.
By the way, the query()
in the below examples isn’t essential, it just makes them easier to read on smaller screens.
use Illuminate\Database\Eloquent\Builder;
$breadsWithCheese = Bread::query()
->whereHas('sandwichFillers', function (Builder $query) {
$query->where('name', '=', 'cheese');
})
->get();
If we want to search for breads with multiple sandwich fillers then we can use the above whereHas, but change the query to use orWhere in the query filter.
use Illuminate\Database\Eloquent\Builder;
$breadsWithFillings = Bread::query()
->whereHas('sandwichFillers', function (Builder $query) {
$query->where('name', '=', 'cheese')
->orWhere('name', '=', 'ham');
})
->get();
We could also simplify this a bit further by using whereIn, passing in the field name and then an array of values, such as cheese and ham.
use Illuminate\Database\Eloquent\Builder;
$breadsWithFillings = Bread::query()
->whereHas('sandwichFillers', function (Builder $query) {
$query->whereIn('name', ['cheese', 'ham']);
})
->get();
When we get the results back, this may not be exactly what you expected. You may think the whereIn would return breads with both cheese AND ham, but it actually returns breads with cheese OR ham sandwich fillers.
If we look back to the first query with the orWhere then this gives us a clue as to what the whereIn is doing. According to w3schools for the MYSQL IN operator:
“The IN operator is a shorthand for multiple OR conditions.”
To explain a bit more, this will return breads with cheese, breads with ham, and breads with ham and cheese.
So how can we query for only breads with cheese AND ham?
The whereHas()
allows us to provide additional constraints after the function. This is very similar to how the has()
allows you to provide an operator and a count. After the relationship name and function we can pass in the operator and the count.
use Illuminate\Database\Eloquent\Builder;
$breadsWithOnlyCheeseAndHam = Bread::query()
->whereHas('sandwichFillers', function (Builder $query) {
$query->whereIn('name', ['cheese', 'ham']);
}, '>', 2)
->get();
This will now return breads with only cheese and ham sandwich fillers.
If we wanted to cast the net a bit wider and have sandwiches with cheese, ham and pickle then we can update the operator to be greater than or equal to so it includes breads with cheese AND ham but may also have other sandwich fillers too, such as pickle.
use Illuminate\Database\Eloquent\Builder;
$breadsWithCheeseAndHam = Bread::query()
->whereHas('sandwichFillers', function (Builder $query) {
$query->whereIn('name', ['cheese', 'ham']);
}, '>=', 2)
->get();
Hopefully this will give you a better understanding of what is possible when querying many to many relationships and how you can better filter them to fit your needs.
Share
Latest Posts
I normally develop forms in Laravel, using Livewire where possible. Laravel has some great form validation tools built in that I’m really used to working with and Livewire offers easy to use state management. For this project though, I had to build the form in a React project.
Here’s a little tip I discovered that I haven’t seen documented anywhere. You can use when() and unless() with the Laravel Http client.
Announcing the new book, The Little-Astwick Mysteries - Trouble at the church, by C.S. Rhymes. It is now available for pre-order on the Amazon Kindle store for £2.99, with the release date of the 1st February 2024.