3.3 Performing Queries
Of course, the whole reason we're using Eloquent is to get our data. So in this lesson, you'll learn about some of the queries you'll use over and over in your apps.
1.Introduction1 lesson, 01:29
2.The Basics3 lessons, 40:56
3.Using Eloquent5 lessons, 53:38
4.Relationships2 lessons, 26:36
5.Conclusion1 lesson, 00:59
3.3 Performing Queries
In the previous two lessons, we've done several things. But among that, we have been performing queries. They've been very simple queries, but queries nonetheless. And simple queries are used all the time in just about every application. However, in most applications, we need more complex queries. Queries that are based upon multiple conditions or other sets of criteria. We need something more than just retrieving a row by the primary key or by a single column's value. So Eloquent really has everything that we need to perform the queries that we need to perform. So we are going to do this. We're going to create a file inside of the app folder. And we're going to call it queries.php. And we're going to write our queries here so that we have them in code and so that they are a little bit easier to read. But then we're going to paste them into the console so that we can run them with Tinker. That way we just have immediate results there. So the first thing we're going to talk about is a method called select. Now so far all of our queries have returned all of the columns. So we've had the ID, the name, the year, as well as the date fields. The created at and the modified or edited at. Well, most of the time we don't need everything coming from the table. And I imagine for the majority of our application, we would need just the id, the name and the year. You wouldn't really need anything else. Eloquent gives us the ability to select just the columns that we need, with a method called select. We're selecting columns. It's just like the select statement in SQL. We just specify the columns that we want, so id, name, and year, and that is going to give us just those columns. So lets copy this. Let's hop on over to the console, and let's paste it in. And we will see all of our Guitar objects. They're given to us still as our model class, but we just have the columns that we requested, id, name and year. So let me also put in some comments, the SQL equivalent. So we have SELECT id, name, year FROM Guitars. And there we go. Okay, so if you're not familiar with the Guitar industry, there are several years that are highly sought after. They might be because they're vintage guitars or they were made a little bit better than other years. Now that's not always the case for every manufacturer. But there are some manufactures and some years that you just want to avoid, because quality was absolutely horrible. So let's write a query that would then select just the Guitars that we would consider desirable, based upon the year. So we're going to start that with a SQL that would look like this. Let's SELECT id, name, year FROM Guitars WHERE, and we will say, year BETWEEN. We'll start with Vintage Guitars, so we'll say 1950 and 1962. Really we could probably go beyond 1962, but as far as I'm concerned about 1959, at least as far as Gibson guitars are concerned, are what I would be interested in. So our query would look something like this. App\Guitar, we would use the select method for the id, and the name and the year. And then we would use our trustee where method. But we want a BETWEEN condition, so we have a method, simply called whereBetween. And then we specify two things, the first is the column, the second is an array, where the elements in that array are the endpoints of our range. So our range begins at 1950 and ends with 1962. And that is the equivalent there, let's use the get method. And let's also just kind of organize this, so it's a little bit easier to read. So we'll have our whereBetween there, and then our get. And so let's copy that, let's paste it into our command line, and that gives us a problem. So let's do this, let's see if we can get away with that. No we can't, it now needs to be on one line, hooray! So I don't know if your operating system if you're not using Windows lets you do that, but that is rather annoying. So what you will paste that in and run it, and there we go. We have one guitar with the year of 1956 and that is a Strat. So that's the first part. Let's also say that we also want to include Guitars that were made from 2011 to 2017. So we're going to end up with a SQL statement that looks like this. Let's put this on another line and we're going to add to this. First thing we need to do is surround year BETWEEN and that, OR, and then year BETWEEN, what did I say? 2011 and 2017, no, let's do 2010, to be nice and even there. So this is our query, and our equivalent and eloquent is going to look kind of the same here. What we're going to have is this select method will have whereBetween. And then we have another method called orwherebetween. And it's pretty much exactly like what we just wrote except that it begins with or, and our date range is gonna be 2010 and 2017. So things are beginning to look, well not ideal. I mean this is still somewhat neat, but it could be a little better. Let's see if this is going to work. So let's hop on over, let's paste that in, and it does. I like that, okay, so now we have multiple results. We have our Guitar from 1956, we also have the Guitars from 2010 to 2017. So this is one way to write this. We can also do something like this. The WHERE method will allow us to pass in a function that we could then use to build our query. So that it looks a little neater In my opinion. And it also gives us the ability to have multiple conditions and it looks nicer. So we pass in a function and that function is going to accept the query builder. And we would use this query builder inside of the function. But here's what we do we don't return anything, we simply use the query builder object, then we use our methods. So the first condition is going to be the whereBetween, the year is between 1950 and 1962. And then we'll have orWhereBetween and then our second condition there. So it's a little bit cleaner, in my opinion. And then of course we need to call get after this. So, if we take this and we paste it in, we're going to see a fatal error, Call to undefined function get(), and that is a problem. You need the error there. So, the error we have, the results that we had before. But let's say that when it comes to the latter years we are just concerned with the guitars that have a name of Vela. So we would essentially need an and. So before we write that, let's take this SQL so that we have that there. And then let's copy this and use it as a basis. So that our SQL is getting a little bit messy but SQL is messy and the oral portion of this condition is going to look like this. Where the years between 2010 and 2017 AND name is LIKE. Let's use a LIKE because there are several models of Vela. And all of them are really good. So we're going to use a LIKE. And we want where the name is LIKE Vela. So our OR condition really has two parts to it. Well, all we really have to do here is add just a normal where. So if we break this up by line, it gets a little bit clearer here. So our first condition is whereBetween the year is between 1950 and1962 or where are the years 2010 and 2017 and where the name is LIKE Vela. And we're going to use the percent signs on both sides of vela. So that the string '%vela%', or rather the substring '%vela%', can be matched throughout the entire value of that column. Now, I used uppercase here for 'LIKE' and for any other SQL keyword. And you don't have to do that, because SQL Is not case sensitive. But that's just a habit of mine so that whenever I have SQL in my code, heaven forbid, then there is some distinction between what is SQL keywords and then what is part of the table. But we don't have out select method here, so let's select id. Let's also get the name and the year, and then we will call the where method. And we didn't do that above either. So, let's just copy and paste that up there too. All right,so let's take this query and let's go to the command line, let's paste it in. And there we have two guitars, Vela, first of all because the year is between 2017 and the name is Vela, and then we also have those Strat. Now, if we change our condition here, let's say that we wanted to select all of the guitars that have just A in there, or LA, let's do that. If we do that, then we're going to of course see different results here. And that's definitely not gonna work, so let's paste that back in or rather, we need to copy and paste that back in. So that we have the Vela, we also have the Starla because that also meets those two conditions. And then of course we have the Strat there as well. The final method that we're going to look at in this lesson is called when. And we use it like this. Let's say that we have a query builder, we have allowed the user to perform a search based upon the year. And if the selected year is greater than 1970, then our query is going to look like this, where('year, '>', 1970), so very, very basic. And in fact, it's highly questionable if this is actually something that [LAUGH] would be needed. But anyway, if the selected year is greater than 1970 then that is the query that we build. But if the selected year is less than 1970, then we do the opposite. Our query is going to select where the year is less than 1970. It's a very, very common thing to do. Especially if you have an application that allows the user to perform their own queries and you have to take their input and build your query based upon their input. So Eloquence actually gives us a better way of doing this. Well, I say better, it looks neater. And it is with a method called when. So once again, we're going to select the id, name and year. And then we're going to use a method called when. Now, the when method accepts three arguments. The first is the condition that we want to test. So this is where our selected year is greater than 1970 is going to go. The second argument is a function that is going to execute if our condition is true. This function accepts a query builder object. So this is essentially where we do this. And then the third argument is another function that once again accepts the query object. And this function is going to execute when the condition is false. So you can think of the where method kinda like the ternary operator, but for Eloquent. And so let's call the get method, and let's copy this, and let's go to Tinker. Now let's create that selected year. And let's set that equal to, I don't know, 2010, for the lack of a better year. And then we will paste in our query and we will execute that. And you can see that we have retrieved all of the guitars that are greater than 1970 and there are quite a bit. So we could do some ordering here. So, before we call the get method, let's call orderBy and we will order by the year. So, then whenever we went the query, we can actually see if this worked and it did. The oldest guitar that we have is 1977, and then it just goes on up from there. So Eloquent has just about everything that we need to perform just about any query that we need. There are many where methods. WhereBetween is just one, there's another one called whereIn, if we needed to perform an in operation. But then we also have methods like when that allows us to build a query based upon a condition. Well sometimes we want to do something with the data coming from the database before we actually use it within our application. And we will look at how to do that with accessors and mutators in the next lesson.