Query methods: Conditions

We've seen how to perform the basic find operations, but most times on their own, they're too simple to search for records matching complex criteria. Instead we'll need to construct queries. To begin we'll look at query methods for setting conditions. Conditions that you specify the criteria that the data must meet to be part of the query results. And in rails, we do that with a query method called where. We pass in the conditions as an argument to the where method. So for example, subject.where and then as an argument, we pass in a hash, where the key is visible and the value is true.

So, as you would expect, this would be looking for all subjects where visible has been set to true. But unlike the finder methods that we're looking at in the last movie where they return an actual array. The query methods are going to return an active relation object. The main benefit of that is that then this active relation object can be chained together with other query methods. And that the end ActiveRelation will take all of them and put them together to build an SQL statement. So for example, we can have subject where visible is true.

And then follow that up with another query method to order the results by the subjects position. We'll talk more about order later on. But this illustrates the point of how they can be daisy chained together. And most importantly it does not execute a data base call immediately. That's what allows us to chain them together. A hash is not the only type of conditions that we can provide to the where method. Let's look at a few others most simple one is going to be a String. So we can just provide a simple string and that will be passed in to the SQL statement raw.

Its great because its very flexible, allows us to write any kinds of SQL we want, and will just dropped directly into the where clause. Of our SQL statement, but there's a big danger in that. We have to be very careful and beware of SQL injection. Now, I'm not going to go into great depth about what SQL injection is here but let me give you a very simple example. Let's imagine that, instead of having a perfectly static string like I have at the top there, that instead, I'm going to construct that string by using a variable called subject name. And let's say that that variable, subject name, comes either from the user, maybe from a forum for from the url, or it comes out of my database.

Either way it's possible that a user could find a way to insert some malicious code into that variable. And then when I take that string that they've provided and I drop it into my SQL statement it could do something very bad. I've done a very simple example here, but notice how after name equals, there's a single quote and then my variable subject name begins with a single quote. Which effectively closes the set up quotes so now my SQL statement has name equals. Quo, quote and then whatever code that they want to insert in my SQL, and then it reopens the quote again at the end, to continue with the rest of what I had intended.

The result of this is probably not the SQL that I was expecting. As a result you should try not to use strings very often, and when you do use them use them as hard-coded strings that never change, that have no user data coming from them in any way whatsoever. Even from the database which is data that may have originated with the user at some point in the past. Instead it's almost as easy for you to use the array type. We still get to write flexible raw SQL. The main difference here is that any time we want to drop a value in, instead of putting single quotes and putting our value directly into the string, instead, we're going to provide a question mark as a place holder for where that value should go.

So the first element in our array is the template for the SQL string that we want to construct with all of those placeholder there. And then all the subsequent elements in the array are the values that should be dropped in to those question marks. The result is almost identical except that it gives rails the opportunity to escape those values before it inserts them in. To render them harmless before it puts them into the string. And then as we already saw, the third type that you can provide as a condition is the hash. We provided just a single key value pair before but you can have multiple ones.

This is a very simple way to write it. It's very easy to read. It also provides escaped SQL that's safe from injection. Each of the key-value pairs in that list is going to be joined together with an AND to generate the where clause that the SQL going to need. Now, while working with hashes can be easier, it does have some limitations It only supports equality, range, and subset checking. And there's no OR, LIKE, less than, or greater than clauses. If you needed any of those features in your SQL statement, then you would need to use the Array type instead.

Let's try creating some queries using conditions. If you are already in your Rails Console there is no reason to exit and go back again, but if not go and make sure that you are in the root of your rails application then type rails console. So let's try the first one, let's just do subject where visible is true, so that's what we're going to assign to this variable called subjects, this can be plural because We're going to be expecting that there might be more than one, where visible is true. We'll hit return. Notice, this is the SQL that it would construct.

And it returned to me, and ActiveRelation Object. That's what's going to allow me to daisy-chain those together. It's not returning an array, it's returning an active record relation. In fact if we do subjects.class, you'll see that it comes back and tells us what it is. One nice thing is that on that ActiveRelation Object, you can take a peek at the SQL by doing subjects.to sql, and that'll tell you what SQL it would generate. So if you are building up a query from different parts, you can stop and check what the SQL would be along the way.

Alright, so we tried it with a hash. Let's try some of the other forms that we have instead of visible true, let's try it with just a simple string first. So that's going to be visible equals true. Right? You can see it's going to generate something very similar for the SQL here. But it's just taking my string and dropping indirectly, that's why it's visible equals true here instead of visible equals one. One and true are synonymous is SQL. Here it took my value, in the other one it took the one it preferred. Now that version is not the SQL injection safe version.

For that we would need the array, we would have a place holder for the value and then we provide the value outside of that. Now this recognized as an array just on it's own, but I like to go ahead and put the square brackets. Around it just to make it explicit. So that says alright we're going to pass in an array. The first value is going to be the string that's a template for the SQL, and then each of the values after that should be dropped in where the question marks sit. Once again we can see the SQL that it generates, and see that it's basically the same thing that we had up in the beginning. So let's take a look at how these are chainable.

Let's go back to my first one here, where I had visible true. Let's also chain it with another one where position is going to be one. So now I've got two where clauses daisy chained together. So there it goes, you can see that it takes it and it constructs SQL that looks like this, take a second look at it. Where subjects visible and subjects position equals one. ActiveRelation took each of those and made one query out of it. Now incedently, this is exactly the same easier for you just done with a single hash that had two key value paris in it.

Alright, and you can compare those and see that it comes up with the exact same result. Now even though there is only one item that's visible and at position one. It does still return an array to me. Not a single item. If we wanted to get the first item out of there, then we would need to use something like first. Alright, let me hit return. Let me show an example that'll make that a little bit clearer. Let's say that we have subject equals subject.wear id is one. And then we're going to find the first one in that result.

So we're going to get an array back, and then we're going to take the first one from that array. And assign it to subject. Now we actually get a subject back, not an ActiveRelation. Not an array, not an ActiveRelation, we're getting a single object. This is equivalent to using the dynamic finders in that find by ID. We're doing the same thing. We're finding by ID, and getting just one of those. That's constructing our SQL with limit one and then returning just that single object instead of an array. The where method is definitely the most powerful of the query methods.

However there are a few others that really help it out and help you to find the records that you are looking for. Let's look at those next.