FREELessons: 12Length: 2.1 hours

Next lesson playing in 5 seconds

  • Overview
  • Transcript

4.2 Querying With Relationships

Naturally, we need to be able to perform queries that include our related data. In this lesson, I'll show you the common ways of doing just that. We'll also discuss eager and lazy loading data.

4.2 Querying With Relationships

In the previous lesson, we set up the relationship between our manufacturers and guitars tables. So now we just need to look at some queries for using that relationship. And we're going to do essentially what we've done in the past few lessons, where we have looked solely at queries. And that is we're going to create a new file, and we will write our queries there, and then we will run our queries in Tinker. So let's call this relationship-queries, and then we will just get started. So, probably the very first thing that we should cover is how to retrieve not just our manufacturers, but also the guitars related to that manufacturers. Because a lot of times, that's exactly what we want, we want both sets of information at the same time. So to do that, we're gonna start with our manufacturer because that's essentially our starting point, at least as far as this relationship is concerned. Now we will be able to go in reverse, start with the guitar and get the manufacturer but we haven't gotten there yet, so lets just do it this way for now. We will use this method called with and then we are going to pass in guitars. Now we've already set up this relationship, so it knows how to get the guitars with a given manufacturer, so in this way we are essentially saying we want to get our manufacturers with the guitars. And we want all of that information, so that whenever we view this in the console we're going to see everything. All we have to do is just scroll up, and we see that we have this collection the first element in this collection is a manufacturer. We see the ID, the name, all that stuff, and then we see the guitars for that manufacturer. That is another collection where the guitars are listed. So we could loop over this collection and that loop would iterate over our manufacturers. And then we could have an inner loop that iterates over our guitars. So it could look something like this, foreach and let's do it like this. We'll say mans for short so that I don't have to type manufacturers, so we'll say mans as man, and then each one of these man objects is going to have guitars. So we will have another for each, so for each man guitars as guitar, and then that will get our guitars. So this is I'm not going to say the most common usage, but this is something that we would use pretty regularly within our applications. At least as far as getting all of the data at once, but there are other times when we want just the manufacturers. But we only want the manufacturers that have guitars. So in that case, we use a different method, but we, of course, still start with manufacturer, and in this case, we use a method called has, and we are essentially saying that we want the manufacturers that has any guitars. And so in this case, we are going to see all of our manufacturers, because all of our manufacturers have guitars however we will modify our database just so that we will set up one of our manufacturers not to have a guitar. So we see our three manufacturers but let's go to our guitars table and let's change the strat, since there's just one there, it would be easier to change. Let's just change the manufacturer id to something other than 2 so that Fender then is not going to show up in this list. So if we run this same query, well now we have just two results, we have PRS, and then we have Gibson. The Fender manufacturer Is completely left off, but now we could also say that okay, we have the manufacturer information but we also want to include at least some information about the guitars related to these manufacturers. Like for example how many guitars a manufacturer has, so we could do this, we could say manufacturer and then there's a method called withCount and we say guitars, and then we execute that query. And of course this is the query builder, so if we wanted to add where clauses here in order to filter things down some more. We could do that if we wanted to, but we are just going to keep it simple here. So, lets pace that in, lets run it, and now we see our manufacturers has the same information, but now we have another column here, but it's not really a column because this column doesn't exist so this is an attribute then. So we can see that the PRS manufacture has three guitars, our Finder manufacture has none because we made that change and Gibson has two. So let's go back to our database and change it so that the Strat is once again related to the Fender manufacturer. So if we run this again we are of course going to see different results. PRS has two guitars, Fender has one, and then Gibson has two, when I would said that we want to retrieved the guitars and only the guitars of a given manufacturer. We don't want the manufacturer information, we just want the guitars associated with it. So, we'll use our manufacturer and we'll use to find the method which is passing the idea of 1, and then to get our guitars, we'll call the guitars method. We wrote this method whenever we set up the relationship with our manufacturer model, there's the guitars there. So we are able to use the guitars method, and then we can just simply get them. And that is going to give us the guitar's for that manufacturer, but we can even do this a little easier. We can use guitars as a property, as opposed to method, and we'll get the same exact results. Now, this is what's called lazily loaded, this means that the guitars are not loaded until we actually retrieve or use the guitar's property, and in many cases that's great and in other cases, it's not. So let's look at an example, let's create mans, we're going to retrieve all of our manufacturers. So manufacturer man I wish that was a different word, [LAUGH] and we'll call the all method, so that gets all of our manufacturers. And then let's loop over this, so we'll have mans as we'll just say m, that's even easier. And then we're going to access the guitars on each manufacturer object, well this is lazily loaded so the guitars for the given manufacturer are not going to be loaded until we actually use this property. So we have three manufacturers, that means that for each iteration of this loop theres going to be a query made to retrieve the guitars for that given manufacturer. So this means that there's going to be three extra queries and if we had ten manufacturers then there would be ten extra queries to get all of our guitars and in that particular case having our guitars lazily loaded. Really isn't a good thing because, that's causing our application to perform more work. So in this particular case we want to do what's called eager loading. We want to go ahead and retrieve the guitars whenever we retrieve the manufacturers as well, that way all of the data that we need is retrieved with one query. So let me add a comment here, this is eager, and this is lazy, now neither approach is correct or incorrect. How you use them determines whether or not you're doing something correctly or incorrectly. So if you plan on using all of the data at once, eager load, if you don't, like for example, if you are only going to display the guitar information if a certain condition is met, then you want to lazily load. All right so now let's talk about the guitar to manufacturer relationship, and this is very easy to do. Let's go to our guitar class and we're going to add another method here. We're going to say function manuFacturer and it is simply going to return this, but instead of saying has many or has one or anything like that, we're going to say this belongs to, and then our model class, so manufacturer. And that's all that we have to do it's very similar to what we did with our manufacturer. It's just that now we are saying that the guitar belongs to the manufacturer. So if we look at these queries, it's going to be very similar, in fact, it's going to follow the same ideas and concepts. So, if we wanted to achieve the manufacturer of a guitar, we could say this, we'll start with guitar. Let's find the first guitar, that is our villa and then we will get to the manufacturer. So this same idea except in reverse so, this would be manufacturer so let's hop on over and run this and there we go. Now if you yours comes up and says no remember that you need to restart Tinker, I did that off screen and if you wanted to eager load the manufacturers. We would say Guitar, and then with('manufacturer'), and then get(), and that would get all of the guitars and the manufacturers associated with those guitars, so there we go. Now, notice here for the manufacturers that have two guitars, we have our guitar here, there's Vela and then there's the manufacturer, we can see that that is the PRS. Now, you can see here, that there's this number, number 766. If we look at the next guitar and its manufacturer, these were made by the same manufacturer. You can see that the manufacturer information isn't here except the number is the same, 766. So it's essentially pointing to this other manufacturer. There's no sense in having two separate manufacturer objects for these two guitars because they have the same manufacturer. And if we scrolled on down, we would see the same thing for the Gibson guitars. The Les Paul has the manufacturer information, the number is 768 if we look at the Explorer we see that same number there. So when it comes to querying with relationships it's very straightforward. You start with whichever model you want to start with, and then you simply include the related models. But remember about eager loading and lazy loading If you know you need all of the information because you're going to work with all of the information, eager load it, that's better for performance. However, if you know that you're going to work with that extra information but only in certain circumstances, then lazy load, but ultimately, it's up to you to make that decision.

Back to the top