Aggregate and Annotate

We don't have to use Python to combine or 0:00 modify our database for every part of our app. 0:02 Lots of times we can use database functions to make things 0:05 more expedient for us. 0:07 Sometimes we need to add information to every item that's in a query set. 0:09 Like for example, what if we wanna do now how many quizzes and 0:14 text septs are in each course? 0:17 We could select the course and then count the text step and 0:19 the quiz step query sets. 0:22 But well, that's at least two queries for every single course. 0:24 I don't know about you, but I don't wanna spend that much time in the database and 0:27 then in Python itself. 0:30 Durango gives us a tool known as annotations. 0:33 Annotations let us run sequel operations on each item in a gray set and 0:35 then append the result of that as a new attribute. 0:39 So let's give it a try. 0:43 First of all though we need to import the SQL operations that we wanna use. 0:44 I'm only gona use one for right now so I'm gonna bring in count. 0:47 And let's update our course list vie. 0:53 We haven't done much for this today. 0:55 So let's mess with this one. 0:57 All right, so we already have the publsihed=True so that's good. 0:59 I'm gonna break this to the next line. 1:05 And so now I want to annotate these. 1:08 So annotate(total_steps=Count('text', 1:10 and I have to say, distinct=True. 1:16 And then I want to add that to the count of quiz. 1:21 And this one needs to be distinct as well. 1:26 All right? 1:29 Yeah that line is a little long but we'll mess with that later. 1:30 If we could do that. 1:33 That makes it a little better. 1:36 I actually had to put them under 80, sweet. 1:38 Okay, so we need to add the distinct equals true so that each text and quiz 1:41 is only counted once for each course since they're selected based on their course ID. 1:46 Since we assigned the two counts, the total steps, 1:51 that will be the new attribute on each of our query sets. 1:53 Lets display it in the template. 1:56 So we need to go to our templates and courses and course list. 1:58 All right, and so 2:06 I think I want to put it in right here under this course description. 2:08 I'm gonna add in if course.total_steps. 2:12 Because we may not always have this thing. 2:19 P and then a strong and I'm gonna say steps. 2:21 And then we'll do a space and we'll do course.total steps. 2:28 And that's end our if. 2:36 All right, so just in case it doesn't show up for 2:38 some reason like on a search page cuz we're not doing that annotation there. 2:40 It won't show up. 2:44 All right, so we're on our search page. 2:46 Let's get off of our search page. 2:50 There we go, and we can see this one has four steps and these have one step. 2:54 So that's cool. 3:01 All right, so those are the counts. 3:02 Let's look at the SQL tab. 3:03 So we come over here and we look at SQL. 3:06 We can see that there's this new huge, scary query over here. 3:09 But it's not taking a huge amount of time. 3:15 I mean, it's taking nine and a half milliseconds. 3:17 That's really not that bad. 3:19 And if we want to expand this and 3:22 look at it you can see there's a huge amount of stuff going on here. 3:23 We have this count distinct plus count distinct as total steps. 3:25 That's where we're attaching that total step thing. 3:29 So that's pretty neat to do. 3:32 All right, so let's hop back over here to our code. 3:34 Sometimes we don't want to do a thing on each item in the query set 3:39 we're gonna do something on the entire query set, all right? 3:44 So if we're doing it on each individual item in the query set that's called 3:48 an annotation. 3:52 If we're doing it on the entire query set then that is an aggregate. 3:53 And aggregates don't give us back a query set, they give us back a dictionary. 3:57 So let's use our annotations to create an aggregation. 4:01 We need to add one more import. 4:06 So we're gonna import some. 4:11 And then here inside the course list, let's add a new thing here, 4:14 we'll say total equals courses, cuz that's our query set, 4:18 .aggregate total equals sum, total steps. 4:21 All right, and then we need to send the total out in our dictionary. 4:30 So we'll come here and we'll do total is total. 4:39 All right, simple enough. 4:44 And I need to display it. 4:46 Not really sure where I want to put the I think I'll just put it right up here. 4:47 I'll do a P and I'll just say total number of quizzes and steps. 4:52 And we'll say total.total, because if we look here, 5:01 the total is this variable, which is gonna come out names total, and inside of that, 5:06 there's gonna be a key called total. 5:09 All right, so total.total. 5:12 And then let's go, refresh this. 5:14 And we can see that we have 15 steps. 5:18 So we have four there and one, two, three, 5:20 four, five, six, seven, eight, nine, ten, 11. 5:23 I may have counted wrong. 5:28 Anyway, we came up with 15 so, cool. 5:29 That's actually really handy thing to know. 5:33 And let's see how that affects our queries And 5:35 where is our not that one. 5:40 It's not that one, it's this one. 5:44 Yeah, so look at this giant query. 5:45 This is actually a little bit scary. 5:47 This query alone took twenty milliseconds. 5:48 That's really big query it takes a long time. 5:51 Aggregates are amazing. 5:53 They're super useful but 5:55 they are one of those things that you're gonna wanna keep your eye on. 5:56 Because you can see I did one aggregate and 5:59 I suddenly added 20 milliseconds to my query time. 6:02 If I take that one out, then I have a super lightweight thing. 6:05 Not super lightweight but pretty lightweight. 6:10 This took almost 55 milliseconds, that's a long time. 6:12 Just for being inside SQL. 6:16 Annotations and 6:18 aggregations are useful and can save you a lot of time in Python instead of SQL. 6:19 You want to be sure and 6:24 use Django debug toolbar to find places where your aggregations and 6:25 annotations can save you time and where they're abusing your database. 6:28