Eloquent Relationships By Example

Sign in Get started

This episode is for members only

Sign up to access "Eloquent Relationships By Example" right now.

Already a member? Sign in to continue

Playing

27. Filtering by intermediate columns

Episodes

0%

Your progress

Total: 4h 18m
Played: 0m
Remaining: 4h 18m

Join or sign in to track your progress

01. Introduction

01. Installation, database and migration refresher

02. One to one

02. Creating and accessing the relationship

03. Creating an address

04. Updating an address

05. Deleting an address

06. Scoping to get the most recent address

07. Building a working example

03. One to many

08. Defining one to many relationships

09. Two ways to insert

10. Accessing the relationship data

11. Deleting records

12. Updating records

13. Adding modifiers directly to the relationship

14. Creating a postable timeline app

04. Belongs to

15. Defining a belongs to relationship

16. Associating relations

17. Switching up keys

05. Eager loading

18. Debugging the issue

19. Eager loading

06. Many to many

20. Defining many to many relationships

21. Attaching relations

22. Detaching relations

23. Attaching and detaching multiple relations

24. Eager loading

25. Defining the inverse of the relationship

26. Intermediate table columns

27. Filtering by intermediate columns

28. Syncing relations

07. Has many through

29. Defining and using has many through relationships

08. Has one through

30. Using has one through relationships

09. Has one of many

31. Setting up a practical example

32. Defining and using a has one of many relationship

33. Using aggregate functions

Transcript

00:00

So we've learned already how to attach data to our pivot table, but what if we want to filter on this or order with this? In this episode, we're going to focus on filtering by this because we have a good use case. Over in our topics page that we built, where it shows all of the courses for a specific topic,

00:18

what we can do in here is add an additional filter, maybe in the query string, to specify the version that we want returned. So if, for example, under the Laravel topic, we only want Laravel content with a version of nine, we could go ahead and add this in now because we are storing this in our pivot table. OK, so to get started with this, let's go and just change around our topics listing over in the show page that we have just here.

00:49

So let's go ahead and replace these just to add this version in here. There's a bit of duplication here, but for this particular topic, we're showing this course and we know that this uses Laravel version nine. So what we want to do is say, well, I want to only see topics or courses under Laravel that have version 10. So by the time we get to the point of specifying this in the query string, this should not be included because it uses Laravel nine.

01:16

Let's take a look at how we do this. OK, so we're going to head over to our web routes because we need to update the way that this page here works and not that page. Let's go down to the page that we actually want is this one just here. OK, so we know at the moment what we're doing is we're just passing a list of the topics down to here and then we're iterating over these.

01:38

What we could really do is reconfigure this to fetch out the courses and pass them down separately. So let's go ahead and say topic. So from that topic, we're going to grab the courses like this and then we're going to separately pass the courses down to here just so we have a little bit more control over in our route here. So that makes a little bit more sense.

02:01

So we're just going to go ahead and switch over this to instead of iterate through topic courses, we're just going to iterate through courses. So if we go over and give this a refresh, you can see it works in exactly the same way. OK, so with this, now what we can do is add in that constraint. So we're going to say topic courses and then we're going to build start to build this up.

02:23

And to do this, we're going to say where pivot and then we're going to pass in the name of the pivot column, which is version. And then we're going to say we want this to match a specific thing. So we can either use the operator here and the value or just the value. It doesn't really matter too much, but we'll add an equals in there anyway.

02:41

So we want this to only be version 10, for example. Then what we're going to do, because we're still working with a builder here, is say get. So that's going to grab all of them courses for us. Let's head over and just check this out and see what we get.

02:54

OK, so sure enough, that has disappeared now because we have specified in here manually version 10. What we can now do is switch this out from our request, the version. And of course, you might want to validate this if you need to. And we'll go ahead and accept the request into here.

03:11

So now we have the freedom to pass the version into the query string and only get back courses under that topic which have that specific version. So let's choose version 9. We know that Laravel is, of course, version 9 here. And we're going to say version 8.

03:29

And of course, we don't see anything. Now, the interesting thing about this very specific example is we could actually use a greater than or equals operator if we were storing this as an integer. I don't think this is going to work if we have a string. But if, for example, you wanted this to be, for example, 9 or less or 9 or above, you could go ahead and do that as well.

03:50

So as long as the version is greater than or less than the one that we specify or less than or equal to the one we specified, it should work. It might actually work with a string. Let's just check this. So version 8, no, it doesn't quite work.

04:01

But if you did have an integer there, you could do any kind of operation to get this to work nicely. At the moment, it has to match the exact version that we give, but you can switch that up if you need to. So this is really interesting and it works, but we now have the problem that we have a few more queries sort of creeping back into this. The reason being is that when we were iterating over the courses directly within that topic before,

04:25

what we were doing is just diving into the relationship directly within the template. What we've now done is separately pulled out a query here, which also within this query contains them topics. So what we really want to do here is get rid of the eager load and think about eager loading at this point. So because we're building up this new collection, this now needs to have with and then topics because we are directly extracting the topics out with a specific condition

04:54

and then fetching them directly. So if we come over and give that a refresh now, we're back to three queries and they should work nicely. So even if we have multiple courses in here with different topics, eager loading now just needs to be at this point because we are manually building up this query here. There's a slightly more advanced use case of this, which I will show you.

05:13

So I'm going to go ahead and get rid of this line just here and I'm going to get rid of this line just here. And I'm going to show you how we might do this in a slightly different way. So let's go and bring back what we had before because we might want to do it this way. I find it a little bit cleaner.

05:28

So we're going to say topic and load and courses dot topic. And let's go back over to our template and switch that back over. So if you did want to do it like this and make sure we load that in properly, if you did want to do it like this, but still eager load like this and keep everything nice and tidy, you can do that. The way that we do that is we define out in here an array.

05:55

And then for this key, which we are eager loading, we can give in a closure like so. So let's just pull this down a little bit. Just so it looks a little bit neat so we can see what's going on. So doing this is going to have exactly the same effect.

06:09

We still get three queries. Everything is working nicely. But what we can now do is pull out the query builder inside of this specifically for topics and add the scope in here like we did just here. So we could add, for example, in here a query and say where pivot.

06:30

Version. Equals and then we could use. Our request in here and say request. And version. So let's head over, give this a refresh.

06:44

We've got Laravel version nine. Now, this isn't quite right, because what we're actually doing here is we are saying where pivot on version under the topic scope, not under the courses. We want courses that have the version under Laravel. So we're going to move this over.

06:59

We're going to eager load in courses and then inside of the closure, we're going to say query with topics. And then down here, we're going to say where pivot version. So I'll explain this in just a second, but let's just go ahead and check this out. Version nine that matches version 10 doesn't.

07:18

So we don't get that returned. Version eight doesn't. So we don't get that returned. So what's actually going on here is we're going ahead and eager loading in courses.

07:27

We know that we want to eager load topics within them courses, but for the courses themselves, we're also attaching a pivot like we did here. This just saves us having to reconstruct out a query inside of here to then fetch that data. So either one that you want to do is absolutely fine. I find doing things like this a little bit more convenient because we've already got the topic in here and we know that for a topic, courses are attached to that topic.

07:57

So it doesn't really make too much sense to pull them courses back out again because we already have them as part of topic. Instead, as part of the eager loading, we apply the constraint in here and that works in exactly the same way. So this is a slightly more advanced case. There's nothing wrong with doing what we've done up here, but you've got two options now should you need to use either of them.

Episode summary

In this episode, we dig into how to filter by intermediate (pivot) columns in a many-to-many relationship in Laravel, using a real-world use case: displaying courses for a specific topic and a specific version (like Laravel version 9 or 10).

We start by updating our topics page so that you can pass a version in the query string, and only get back courses matching that version for the chosen topic. You’ll learn how to modify the Eloquent relationship query and apply a wherePivot filter for precise control. We also talk through the practical difference between eager loading in the route/controller versus doing it inside your template, and cover how this affects query count and code clarity.

Finally, we end with two approaches to the problem—either by fetching the filtered courses and passing them separately, or by eager loading with a constraint using a closure. You’ll see a few common mistakes and how to resolve them, all while making sure your filtering is efficient and clear. By the end, you’ll understand when and why to use each method, and be able to filter your relationships with confidence!

Episode discussion

No comments, yet. Be the first!