In Laravel, many-to-many relationships are established using an intermediate table that holds foreign keys from the related tables. Sometimes, we need to filter queries based on conditions set in the intermediate table columns. This can be done by using the wherePivot method on the belongsToMany relationship.
Ifyou don’t know How to Define Custom Intermediate Table Models, please read our previous blog post on the topic: 🔗📚🌐 Many-to-Many Relationships in Laravel: How to Define Custom Intermediate Table Models 🤝💻
Let’s look at some examples to understand this well:
return $this->belongsToMany(Role::class)
->wherePivot('approved', 1);
In this example, we are filtering the query to only return records where the approved column in the intermediate table is set to 1.
return $this->belongsToMany(Role::class)
->wherePivotIn('priority', [1, 2]);
In this example, we are filtering the query to only return records where the priority column in the intermediate table is either 1 or 2.
return $this->belongsToMany(Role::class)
->wherePivotNotIn('priority', [1, 2]);
In this example, we are filtering the query to only return records where the priority column in the intermediate table is neither 1 nor 2.
return $this->belongsToMany(Podcast::class)
->as('subscriptions')
->wherePivotBetween('created_at', ['2020-01-01 00:00:00', '2020-12-31 00:00:00']);
In this example, we are using the as method to give a name to the relationship and filtering the query to only return records where the created_at column in the intermediate table falls between two dates.
return $this->belongsToMany(Podcast::class)
->as('subscriptions')
->wherePivotNotBetween('created_at', ['2020-01-01 00:00:00', '2020-12-31 00:00:00']);
In this example, we are using the as method to give a name to the relationship and filtering the query to only return records where the created_at column in the intermediate table does not fall between two dates.
return $this->belongsToMany(Podcast::class)
->as('subscriptions')
->wherePivotNull('expired_at');
In this example, we are using the as method to give a name to the relationship and filtering the query to only return records where the expired_at column in the intermediate table is null.
return $this->belongsToMany(Podcast::class)
->as('subscriptions')
->wherePivotNotNull('expired_at');
In this example, we are using the as method to give a name to the relationship and filtering the query to only return records where the expired_at column in the intermediate table is not null.
In conclusion, Laravel provides a powerful set of methods to filter queries based on conditions set in the intermediate table columns. We can use these methods to retrieve the data we need efficiently and effectively.
Table of Contents In Laravel, many-to-many relationships are established using an intermediate table that holds foreign keys from the related
Table of Contents Introduction: Laravel is a PHP framework that has become quite popular in recent years. One of the
Table of Contents In Laravel, models are the backbone of the application. They act as a bridge between the database
Table of Contents Laravel is a popular PHP framework that is widely used for web application development. One of the
An experienced Code Debugger with a strong track record of identifying and resolving complex software issues. I aim to contribute my analytical skills and expertise in debugging tools to ensure efficient and reliable software operations and deliver high-quality software products that meet customer requirements.
If you found this content valuable, please show your support by following me on Medium, LinkedIn, Twitter and GitHub. Your support will motivate me to create more informative content in the future. Don’t forget to give a clap or share this blog with others if you found it helpful!