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 have seen a lot of threads from indie authors asking for advice with marketing their ebook. If I’m honest, I have never really had a strategy for marketing my books, I just assumed that I could put it out there and people would find it. In the real world, that does not seem to be the case.
This post follows on from my last post about using your website to promote your ebooks. The first step of the article explains that you need to make a website, but didn’t go into too much detail. This post aims at explaining how you can build a landing page for your book with Bulma Clean Theme.
I’m a web developer by trade and a part-time author, so here are a few things that I have done to help promote my books and ebooks using my website and my tech know how from my day job.