Heads up! To view this whole video, sign in with your Courses Plus account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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