This episode is for members only
Sign up to access "Eloquent Relationships By Example" right now.
Get started
Already a member? Sign in to continue
Playing
27. Filtering by intermediate columns
Episodes
01. Introduction
02. One to one
03. One to many
04. Belongs to
05. Eager loading
06. Many to many
20. Defining many to many relationships
13m 46s
0%
21. Attaching relations
8m 58s
0%
22. Detaching relations
1m 52s
0%
23. Attaching and detaching multiple relations
3m 58s
0%
24. Eager loading
2m 15s
0%
25. Defining the inverse of the relationship
7m 40s
0%
26. Intermediate table columns
7m 54s
0%
27. Filtering by intermediate columns
8m 16s
0%
28. Syncing relations
7m 19s
0%
07. Has many through
08. Has one through
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.
33 episodes•4 hrs 18 mins•1 year ago
Overview
Eloquent is Laravel's ORM (Object Relational Mapper). In simple terms, it's how your models work with the database.
The good news? There's a bunch of powerful relationship types available. Our task is to learn when and where to use each one.
In this course, we'll cover each basic relationship type, how to access related models, and then insert, sync, update and delete related data. Oh, and we'll build a practical example for each relationship type, to really make it stick.
Alex Garrett-Smith
Hey, I'm the founder of Codecourse!
Comments
No comments, yet. Be the first to leave a comment.