1
00:00:05,210 --> 00:00:09,830
Welcome back, everyone, to this lecture, we're going to discuss filtering with field lookups.

2
00:00:10,780 --> 00:00:15,910
Now, so far, we've had to use equality statements in our filtering that is something like a filter

3
00:00:15,910 --> 00:00:21,490
where age is exactly equal to 30 or last name is exactly equal to Smith.

4
00:00:22,000 --> 00:00:25,240
Now we were able to combine things with and then or statements.

5
00:00:25,510 --> 00:00:30,880
But what about a more general comparison operators like greater than or less than or even more complex

6
00:00:30,880 --> 00:00:34,180
things like starts with a certain set of characters.

7
00:00:35,280 --> 00:00:40,200
Well, for more complex filtering operations, we use field lookups with a filter call.

8
00:00:40,680 --> 00:00:46,380
So the way this works is, let's imagine we have a model that we call the objects and that filter.

9
00:00:46,470 --> 00:00:51,330
Now, previously we would pass in an attribute and then set it equal to something like first name is

10
00:00:51,330 --> 00:00:55,470
equal to John, but with field lookups, we have a lot more capabilities.

11
00:00:55,740 --> 00:01:01,020
The first step is again to choose whatever attribute you're interested in, like the name column.

12
00:01:01,770 --> 00:01:04,410
Then you use two sets of underscores.

13
00:01:04,410 --> 00:01:08,640
And I want to stress this because it's a really common mistake to only put one single set of scores,

14
00:01:08,970 --> 00:01:16,380
but you put two under scores and then the particular field look up, and there are a ton of field lookups

15
00:01:16,380 --> 00:01:17,910
that you can pass in here.

16
00:01:17,920 --> 00:01:20,100
For example, I'm using starts with.

17
00:01:20,640 --> 00:01:26,520
Then you put an equal sign and the actual results on the other side of the equal sign is going to kind

18
00:01:26,520 --> 00:01:30,090
of depend on whatever field lookup you are interacting with.

19
00:01:30,120 --> 00:01:32,250
There's a less than or greater than field lookup.

20
00:01:32,460 --> 00:01:35,850
There's an exact field lookup and there are many, many more.

21
00:01:36,210 --> 00:01:37,890
But this is the general syntax.

22
00:01:37,920 --> 00:01:39,780
Again, it's modeled the objects that filter.

23
00:01:40,080 --> 00:01:46,170
And then the actual name of the column or attribute you're interested in two sets of underscores the

24
00:01:46,170 --> 00:01:51,090
field lookup parameter and then an equal sign with whatever is relevant to the field parameter.

25
00:01:52,730 --> 00:01:57,140
Again, there are many, many different types of field lookup calls available, and you can check them

26
00:01:57,140 --> 00:01:58,190
out at this link.

27
00:01:59,680 --> 00:02:04,840
So we're going to do is just explore a few of these field look up methods and explore with documentation

28
00:02:05,020 --> 00:02:08,440
to see how exactly they translate into a sequel equivalent.

29
00:02:08,800 --> 00:02:10,270
Let's head to our code editor.

30
00:02:10,539 --> 00:02:14,260
OK, so here I am at our code editor.

31
00:02:14,380 --> 00:02:16,870
What I'm going to do is call patient.

32
00:02:18,000 --> 00:02:20,040
The objects that filter.

33
00:02:20,460 --> 00:02:25,980
And then let's imagine I want to explore all the last names that happened to start with us.

34
00:02:26,490 --> 00:02:28,110
So here I can say last name.

35
00:02:29,500 --> 00:02:36,430
And underscore underscore then the name of the field lookup that I'm interested in using this one happens

36
00:02:36,430 --> 00:02:37,750
to be called starts with.

37
00:02:38,380 --> 00:02:42,070
And again, I don't expect anybody to memorize all these field lookups.

38
00:02:42,370 --> 00:02:44,010
Some of them will feel kind of familiar.

39
00:02:44,020 --> 00:02:49,960
If you have familiarity with Python string methods or with just general SQL methods, they do take a

40
00:02:49,960 --> 00:02:51,520
lot of the terminology from that.

41
00:02:51,820 --> 00:02:55,900
But in general, you're going to be looking these up in the documentation because there are so many

42
00:02:55,900 --> 00:02:56,230
of them.

43
00:02:56,710 --> 00:03:00,280
So let's imagine again, I'm interested in the last names that happened to start with the letter S.

44
00:03:00,580 --> 00:03:03,070
So I say last name two sets of underscores.

45
00:03:03,160 --> 00:03:09,250
The field lookup starts with equals and then I'm going to say lowercase s, and then let's grab all

46
00:03:09,250 --> 00:03:09,640
of these.

47
00:03:10,540 --> 00:03:15,280
And now I get to see all the patients who have a last name that simply starts with S.

48
00:03:16,320 --> 00:03:17,370
So what else is available?

49
00:03:17,730 --> 00:03:20,730
Well, we already know there's an inn operator in Python.

50
00:03:21,090 --> 00:03:27,900
What I can do then is, say, patient the objects that filter and say, I'm looking for an age that

51
00:03:27,900 --> 00:03:33,330
is in a particular either range or values list.

52
00:03:33,510 --> 00:03:37,820
So for example, I can say age underscore, underscore in.

53
00:03:38,310 --> 00:03:41,730
And the way this one works is it's looking for an item that is in a list.

54
00:03:42,570 --> 00:03:48,390
So then I'm going to say something like in equals and then the list of maybe ages I'm interested in.

55
00:03:48,750 --> 00:03:54,480
So I'm interested in people that are, let's say, 20 years old, 30 years old or 40 years old.

56
00:03:55,470 --> 00:03:57,150
And then I'm going to grab all those.

57
00:03:58,430 --> 00:04:03,320
And I enter, so notice I get back some that's 30 Typekit and then three people that are 40.

58
00:04:03,470 --> 00:04:05,600
I didn't have any matches for 20 years old.

59
00:04:06,140 --> 00:04:10,190
So again, the way this works is usually attribute two sets.

60
00:04:10,190 --> 00:04:12,560
A double underscores the relevant field.

61
00:04:12,560 --> 00:04:13,700
Look up again.

62
00:04:13,700 --> 00:04:18,380
Don't expect you to memorize these and then equal sign and whatever is relevant to that field.

63
00:04:18,380 --> 00:04:22,370
Look up, you can see for some of them, it's going to be a string that's relevant for others.

64
00:04:22,400 --> 00:04:25,130
It's going to be something like a list that's relevant.

65
00:04:25,940 --> 00:04:28,610
It's always, however, going to be an equal sign here.

66
00:04:29,000 --> 00:04:33,320
If you're looking for something like less than or equal to, that's actually LTE.

67
00:04:33,560 --> 00:04:34,850
So I'm going to clear this again.

68
00:04:35,750 --> 00:04:36,740
I'll say patient.

69
00:04:38,230 --> 00:04:46,840
Objects filter and let's say eight underscore underscore, and I'm actually going to say greater than

70
00:04:46,840 --> 00:04:49,210
or equal to and let's say, thirty nine.

71
00:04:49,300 --> 00:04:54,820
So again, I'm going to say equal to thirty nine and grab everything there.

72
00:04:55,840 --> 00:05:01,540
So what's a little weird is that it's always going to be equal sign, even if the field look up implies

73
00:05:01,540 --> 00:05:03,550
that a different comparison is actually taking shape.

74
00:05:03,970 --> 00:05:09,520
So what this GTI stands for is greater than or equal to and then I can see everyone that's greater than

75
00:05:09,520 --> 00:05:10,790
equal to thirty nine years old.

76
00:05:10,810 --> 00:05:14,050
Essentially, everybody that's 40 in my particular database.

77
00:05:14,260 --> 00:05:18,580
So if you're wondering where to actually find all these field lookups, you go to the link that I just

78
00:05:18,580 --> 00:05:21,520
showed in the slides and that will look something like this.

79
00:05:21,850 --> 00:05:27,310
And essentially, this is the reference for field lookups inside query sets.

80
00:05:27,640 --> 00:05:34,630
So common ones are going to be using are in in order to check if something is in a list or in a character

81
00:05:34,630 --> 00:05:35,020
string.

82
00:05:35,050 --> 00:05:38,530
This works basically the same way in as Python's in operator.

83
00:05:39,130 --> 00:05:43,210
Then you can scroll down here and eventually you'll see things like greater than greater than or equal

84
00:05:43,210 --> 00:05:46,900
to less than L.T. less or equal to starts with.

85
00:05:47,260 --> 00:05:51,070
It starts with and there are a bunch of different examples and with and so on.

86
00:05:51,760 --> 00:05:54,640
You can also check for different ranges so you can do a range test.

87
00:05:54,940 --> 00:05:59,560
This one's a little different as it takes in a tuple so it can take in a start and an end.

88
00:05:59,830 --> 00:06:06,010
It's really useful for dates, and you also have a bunch of things that are explicitly for dates, things

89
00:06:06,010 --> 00:06:08,850
like checking for years, checking for the month.

90
00:06:08,860 --> 00:06:10,600
So it was at the 12th month of the year.

91
00:06:10,930 --> 00:06:13,940
Is it the third day of the year, et cetera?

92
00:06:13,960 --> 00:06:15,660
What week was it in the year weekday?

93
00:06:15,680 --> 00:06:17,320
Was it a Monday, Tuesday and so on?

94
00:06:17,560 --> 00:06:18,300
Was it a quarter?

95
00:06:18,310 --> 00:06:19,550
Was it an hour, etc.?

96
00:06:19,580 --> 00:06:21,550
They have tons of them that are available.

97
00:06:21,730 --> 00:06:25,540
You can also check if something is null, so whether or not there was an actual item there.

98
00:06:25,990 --> 00:06:29,260
And you can also use just really advance regular expressions.

99
00:06:29,560 --> 00:06:34,780
So you could if you were really good at regular expressions, say this case, insensitive regular expression

100
00:06:34,780 --> 00:06:37,240
and then pass it in as a regular expression call.

101
00:06:37,900 --> 00:06:38,200
OK.

102
00:06:38,530 --> 00:06:41,370
So there's other things that are available to hear.

103
00:06:41,380 --> 00:06:46,270
And again, I would recommend that you check them out if you just go to query API reference.

104
00:06:46,630 --> 00:06:51,790
You can see here there is a if we scroll down here, field lookup reference.

105
00:06:51,790 --> 00:06:56,590
So field lookups, there's exact contains in etc. and then a bunch to have to do of time.

106
00:06:57,070 --> 00:07:02,410
The other thing I want to point out here is previously, we've already talked about things like get

107
00:07:02,440 --> 00:07:06,250
or create as well as exclude or all.

108
00:07:06,670 --> 00:07:12,160
There's a bunch of methods that are relevant that are not just things like get or select.

109
00:07:12,430 --> 00:07:17,110
So these are called methods that return new query sets and filters one of them.

110
00:07:17,440 --> 00:07:20,980
But there's also things like exclude or order by or reverse.

111
00:07:21,850 --> 00:07:24,970
So really, what I want you to do is before you check out the next lecture.

112
00:07:25,270 --> 00:07:32,050
Just check out the query API reference as it's pretty much going to cover everything that we've already

113
00:07:32,050 --> 00:07:34,900
discussed but show you a plethora of options.

114
00:07:35,260 --> 00:07:40,390
We don't really have time to show examples of, you know, 100 plus different methods, but you can

115
00:07:40,390 --> 00:07:44,440
understand by the API documentation what these do.

116
00:07:44,770 --> 00:07:49,090
For example, we haven't really talked about how to sort your results, but you would come here and

117
00:07:49,090 --> 00:07:52,300
check out the methods, and you would realize that there's one called order by.

118
00:07:52,810 --> 00:07:56,290
So you click on order by and it says, OK, how did this actually work?

119
00:07:56,500 --> 00:08:01,900
Well, here we can see the first filter where they're filtering by the year 2005, but then they are

120
00:08:01,900 --> 00:08:08,380
ordering by publication date and you can see they can order by publication day and headline.

121
00:08:08,560 --> 00:08:10,150
So how would this actually work?

122
00:08:10,480 --> 00:08:16,450
Well, you notice that they're passing in some sort of string code that is relevant to the actual entry

123
00:08:16,450 --> 00:08:17,200
that they're looking at.

124
00:08:17,260 --> 00:08:19,780
And then they're ordering by that particular string code.

125
00:08:20,170 --> 00:08:23,530
So let's go ahead and check this out if we wanted to do it ourselves.

126
00:08:23,970 --> 00:08:26,050
I going to come back to Visual Code Editor.

127
00:08:26,350 --> 00:08:29,980
Let's say I wanted to order by last name or first name.

128
00:08:30,790 --> 00:08:37,880
I could say patient the objects that filter or actually this is not even a filter is necessary.

129
00:08:37,880 --> 00:08:41,740
You can just say order by order underscore by.

130
00:08:42,490 --> 00:08:50,170
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

131
00:08:50,170 --> 00:08:51,420
going to be ordered by H.

132
00:08:51,460 --> 00:08:57,730
Starting off with 30 years old, then 36, 40 and then 40 and 40, or I could order them by their last

133
00:08:57,730 --> 00:08:58,030
name.

134
00:08:58,060 --> 00:08:59,290
So again, string code here.

135
00:09:01,130 --> 00:09:04,970
Last name, and now they're ordered by their last name again in alphabetical order.

136
00:09:05,000 --> 00:09:07,880
So Bullis marks Smith, Smith, Smith.

137
00:09:08,300 --> 00:09:12,800
Hopefully that gives you an idea of how to interact with the documentation to figure out what you need

138
00:09:12,800 --> 00:09:13,190
to do.

139
00:09:13,610 --> 00:09:19,280
A big part of being able to use Django is being able to look at their API references.

140
00:09:19,580 --> 00:09:21,720
Understand the kind of small details here.

141
00:09:21,740 --> 00:09:26,660
You notice this one was actually using a string code instead of just a variable name that was assumed

142
00:09:26,660 --> 00:09:30,410
there and bringing it back to your actual code and then using it.

143
00:09:30,950 --> 00:09:36,710
So we're not going to focus too much more on the variety of methods that are available for us as far

144
00:09:36,710 --> 00:09:37,940
as filtering is concerned.

145
00:09:38,270 --> 00:09:42,650
Instead, we're going to introduce them as the situation arises where we need them.

146
00:09:42,980 --> 00:09:48,500
And keep in mind, if you ever see me use a particular query set API, call like a field lookup or a

147
00:09:48,500 --> 00:09:51,320
particular query method call and you're confused about it.

148
00:09:51,560 --> 00:09:56,300
You can always look it up in the documentation, and the documentation is really great and it has tons

149
00:09:56,300 --> 00:09:58,140
of examples and explanations.

150
00:09:58,160 --> 00:10:03,050
So a huge tool that if you're going to be good at Django, you have to be good at looking stuff up in

151
00:10:03,050 --> 00:10:04,280
the documentation as well.

152
00:10:04,910 --> 00:10:10,910
OK, so I'll see you at the next lecture, or we switch gears and discuss updating items that are already

153
00:10:10,910 --> 00:10:12,110
in our database table.

154
00:10:12,410 --> 00:10:12,920
I'll see you there.