Aggregate and Annotate6:32 with Kenneth Love
Sometimes we need to get more information on the QuerySet or on each item in the QuerySet. And if the database can do the work for us, why not let it?
Here are the Django docs for aggregations and annotation.
You can use
F() objects in your aggregations and annotations if you need things to be more up-to-date or directly from the database. There are several aggregates available so be sure to look through the documentation to find the ones you need or want to use.
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
You need to sign up for Treehouse in order to download course files.Sign up