Welcome back, everyone, to this lecture, we're going to discuss filtering with field lookups.
Now, so far, we've had to use equality statements in our filtering that is something like a filter
where age is exactly equal to 30 or last name is exactly equal to Smith.
Now we were able to combine things with and then or statements.
But what about a more general comparison operators like greater than or less than or even more complex
things like starts with a certain set of characters.
Well, for more complex filtering operations, we use field lookups with a filter call.
So the way this works is, let's imagine we have a model that we call the objects and that filter.
Now, previously we would pass in an attribute and then set it equal to something like first name is
equal to John, but with field lookups, we have a lot more capabilities.
The first step is again to choose whatever attribute you're interested in, like the name column.
Then you use two sets of underscores.
And I want to stress this because it's a really common mistake to only put one single set of scores,
but you put two under scores and then the particular field look up, and there are a ton of field lookups
that you can pass in here.
For example, I'm using starts with.
Then you put an equal sign and the actual results on the other side of the equal sign is going to kind
of depend on whatever field lookup you are interacting with.
There's a less than or greater than field lookup.
There's an exact field lookup and there are many, many more.
But this is the general syntax.
Again, it's modeled the objects that filter.
And then the actual name of the column or attribute you're interested in two sets of underscores the
field lookup parameter and then an equal sign with whatever is relevant to the field parameter.
Again, there are many, many different types of field lookup calls available, and you can check them
out at this link.
So we're going to do is just explore a few of these field look up methods and explore with documentation
to see how exactly they translate into a sequel equivalent.
Let's head to our code editor.
OK, so here I am at our code editor.
What I'm going to do is call patient.
The objects that filter.
And then let's imagine I want to explore all the last names that happened to start with us.
So here I can say last name.
And underscore underscore then the name of the field lookup that I'm interested in using this one happens
to be called starts with.
And again, I don't expect anybody to memorize all these field lookups.
Some of them will feel kind of familiar.
If you have familiarity with Python string methods or with just general SQL methods, they do take a
lot of the terminology from that.
But in general, you're going to be looking these up in the documentation because there are so many
of them.
So let's imagine again, I'm interested in the last names that happened to start with the letter S.
So I say last name two sets of underscores.
The field lookup starts with equals and then I'm going to say lowercase s, and then let's grab all
of these.
And now I get to see all the patients who have a last name that simply starts with S.
So what else is available?
Well, we already know there's an inn operator in Python.
What I can do then is, say, patient the objects that filter and say, I'm looking for an age that
is in a particular either range or values list.
So for example, I can say age underscore, underscore in.
And the way this one works is it's looking for an item that is in a list.
So then I'm going to say something like in equals and then the list of maybe ages I'm interested in.
So I'm interested in people that are, let's say, 20 years old, 30 years old or 40 years old.
And then I'm going to grab all those.
And I enter, so notice I get back some that's 30 Typekit and then three people that are 40.
I didn't have any matches for 20 years old.
So again, the way this works is usually attribute two sets.
A double underscores the relevant field.
Look up again.
Don't expect you to memorize these and then equal sign and whatever is relevant to that field.
Look up, you can see for some of them, it's going to be a string that's relevant for others.
It's going to be something like a list that's relevant.
It's always, however, going to be an equal sign here.
If you're looking for something like less than or equal to, that's actually LTE.
So I'm going to clear this again.
I'll say patient.
Objects filter and let's say eight underscore underscore, and I'm actually going to say greater than
or equal to and let's say, thirty nine.
So again, I'm going to say equal to thirty nine and grab everything there.
So what's a little weird is that it's always going to be equal sign, even if the field look up implies
that a different comparison is actually taking shape.
So what this GTI stands for is greater than or equal to and then I can see everyone that's greater than
equal to thirty nine years old.
Essentially, everybody that's 40 in my particular database.
So if you're wondering where to actually find all these field lookups, you go to the link that I just
showed in the slides and that will look something like this.
And essentially, this is the reference for field lookups inside query sets.
So common ones are going to be using are in in order to check if something is in a list or in a character
string.
This works basically the same way in as Python's in operator.
Then you can scroll down here and eventually you'll see things like greater than greater than or equal
to less than L.T. less or equal to starts with.
It starts with and there are a bunch of different examples and with and so on.
You can also check for different ranges so you can do a range test.
This one's a little different as it takes in a tuple so it can take in a start and an end.
It's really useful for dates, and you also have a bunch of things that are explicitly for dates, things
like checking for years, checking for the month.
So it was at the 12th month of the year.
Is it the third day of the year, et cetera?
What week was it in the year weekday?
Was it a Monday, Tuesday and so on?
Was it a quarter?
Was it an hour, etc.?
They have tons of them that are available.
You can also check if something is null, so whether or not there was an actual item there.
And you can also use just really advance regular expressions.
So you could if you were really good at regular expressions, say this case, insensitive regular expression
and then pass it in as a regular expression call.
OK.
So there's other things that are available to hear.
And again, I would recommend that you check them out if you just go to query API reference.
You can see here there is a if we scroll down here, field lookup reference.
So field lookups, there's exact contains in etc. and then a bunch to have to do of time.
The other thing I want to point out here is previously, we've already talked about things like get
or create as well as exclude or all.
There's a bunch of methods that are relevant that are not just things like get or select.
So these are called methods that return new query sets and filters one of them.
But there's also things like exclude or order by or reverse.
So really, what I want you to do is before you check out the next lecture.
Just check out the query API reference as it's pretty much going to cover everything that we've already
discussed but show you a plethora of options.
We don't really have time to show examples of, you know, 100 plus different methods, but you can
understand by the API documentation what these do.
For example, we haven't really talked about how to sort your results, but you would come here and
check out the methods, and you would realize that there's one called order by.
So you click on order by and it says, OK, how did this actually work?
Well, here we can see the first filter where they're filtering by the year 2005, but then they are
ordering by publication date and you can see they can order by publication day and headline.
So how would this actually work?
Well, you notice that they're passing in some sort of string code that is relevant to the actual entry
that they're looking at.
And then they're ordering by that particular string code.
So let's go ahead and check this out if we wanted to do it ourselves.
I going to come back to Visual Code Editor.
Let's say I wanted to order by last name or first name.
I could say patient the objects that filter or actually this is not even a filter is necessary.
You can just say order by order underscore by.
And then as a string code, I can pass an h and let's grab all of those and you can see now they're
going to be ordered by H.
Starting off with 30 years old, then 36, 40 and then 40 and 40, or I could order them by their last
name.
So again, string code here.
Last name, and now they're ordered by their last name again in alphabetical order.
So Bullis marks Smith, Smith, Smith.
Hopefully that gives you an idea of how to interact with the documentation to figure out what you need
to do.
A big part of being able to use Django is being able to look at their API references.
Understand the kind of small details here.
You notice this one was actually using a string code instead of just a variable name that was assumed
there and bringing it back to your actual code and then using it.
So we're not going to focus too much more on the variety of methods that are available for us as far
as filtering is concerned.
Instead, we're going to introduce them as the situation arises where we need them.
And keep in mind, if you ever see me use a particular query set API, call like a field lookup or a
particular query method call and you're confused about it.
You can always look it up in the documentation, and the documentation is really great and it has tons
of examples and explanations.
So a huge tool that if you're going to be good at Django, you have to be good at looking stuff up in
the documentation as well.
OK, so I'll see you at the next lecture, or we switch gears and discuss updating items that are already
in our database table.
I'll see you there.