Django Aggregation

hello my name is xander welcome to the django rm mastery series in this tutorial we take a first look at aggregation just very quickly if you are new to the django rm mastery series you will find a playlist in youtube for this and we've started from the very beginning here what is django rm and we've been working through different aspects of the orm and working with data in django you will eventually find a link from one of those tutorials to the github repository and here you'll find all the resources for this tutorial series as well as a structured view and you can see here the jump that we've made looking at some more advanced topics in advanced if you would like to follow this tutorial step by step utilizing the code that i'm using here there is a link in the video description to the repository where you'll find this project this project has one has a a project here called book and inside of the models you can see that we've got one table here called book and we've got some fields here and i've already pre-inserted uh the data you'll find the data here in the book csv so there's around about 199 200 rows so let's start off by trying to define what aggregation is here in the context of django rm so far in this tutorial series we've been looking at utilizing queries or generating queries to collect data from the database so we select specific data that we want to retrieve from the database so we can display or perform additional actions so aggregation is about not necessarily collecting values from a database but generating values from information that's stored within the database so here with aggregation we want to collect a range of objects a collection of objects and then we want to perform some sort of calculation possibly on that data so here we are in this tutorial we are going to focus on a single table or single table aggregation we will move into more complex aggregation once we've covered the next tutorial which is annotation so just take this example here so previously we've been looking at queries where we have been selecting data from a database so for example select um select book where author equals author one and that would then return all the data about uh connected to author one and there's two records here or two objects here that would get returned so here with annotation we want to take a collection of data for example this collection of data here and we want to perform some sort of calculation so for example maybe we wanted to calculate the total price of all books this is where we would utilize aggregation we would find the collection of books we want to calculate and then perform the calculation so maybe we wanted to find the average rating or the total average rating of all books or the maximum rating or the minimum rating of a book in our table so these are the types of calculations or actions that we want to perform utilizing aggregation so let's start by just counting i guess this is a form of aggregation we're going to just count the amount of rows that we have in our database so i will be utilizing the terminal as well as creating views to show how you can utilize these queries essentially we'll build here in our views and then i do have a template here that we can then output that data so let's just go ahead and we're going to need to import our table first so using our terminal let's just go into the the shell here right so from book and dot models so that's the the book app here book models and we want to import book so that gives us access to the book model so now we can go ahead and perform some sort of query so in this case we're just going to take book and then use the default object manager and then we're just going to count the amount of rows there we go so we have 189 rows in our table so if you do want to explore the database a little bit further here notice i've got sql explorer this is an extension called sqlite go ahead and install that and that will then give you access to basically utilizing or accessing the sqlite database here so this is my database open the database and then from there i can drill down to the different tables this is a table that we're working with i'll just move that across and you can see all the data that we have in this table if you take a look at the data in the database you can see that wonder fields is called ratings count so this denotes the amount of times users have rated individual books so this book here has been rated 633 times so let's go ahead and use annotate to get a collection of books and then we're going to use sum to calculate the total amount of ratings that have been placed by all users over all books so let's work in the terminal again to produce the data so let's go ahead and first of all go into the shell again so we're going to need to access the book information so book models this is all good practice import and then book okay so book being the project models the model file inside the model file there is a class called book or table called book or a model called book so we've now accessed that information right so we're going to need to summarize or some we're going to need to make a calculation so we're going to use some for that so let's grab that from django.db and models and then in there we can import some so just be careful this does need a capital okay so if you are copying this out letter by letter just make sure that you notice the um the capital there for some okay so we brought in the tools now so now we can go ahead and actually perform our aggregation so we need let's think of aggregation in two steps we're going to need some data to aggregate and then we're going to perform the aggregation i guess that's one way of looking at it so let's go for a book so that's the table that we want to work on we're using the defaultobjectsmanager.org so here we're just going to collect all the data from the database so we're saying we select all the objects in our table and now we're going to extend this with a dot and then we're going to aggregate so aggregate and then what we're going to do here is we're going to basically make a calculation so we're going to use sum to then select the field that we want to calculate so in this case it's going to be ratings count so in our sum here we're going to select the ratings count okay so that's the the field that we want to um calculate all the the ratings and then let's give that a go and there we go so we have a total here of it looks like 9 million seven hundred thousand for seven hundred forty thousand two hundred and eleven ratings in total so that's a a total of all ratings uh from all books taking a closer look at this two things that we can change here first of all we don't need to utilize the dot or in our query here so aggregation will work without that we've selected the table we've selected the column and now we're just going to perform the action so that will work exactly the same without using dot all for example in this case now secondly you'll notice that what is returned so here we're returning a dictionary key value here and notice the name that's being generated so this is automatically generated by django um you can see that we have the name of the column which is rating count and then the action that we performed which was sum so that's important obviously to understand because when we return this data you'll want to access this data so in order to access this data here in this dictionary potentially we're going to be utilizing the name here or the key so if you do need to change the name of our key here then all you need to do like i've performed two examples here is just place before the sum a name so in this case new name equals sum so let's just go ahead and rename this again so for example new equals sum and that's going to change the key name so we can just see the complete view here of utilizing this in our view because sometimes a lot of the examples of fine students find it then difficult to kind of comprehend how that's going to then be utilized in a view so apologies if this makes uh sense and you're already familiar to this i know that some students can find it difficult moving from the terminal into the view so let's just go ahead and perform the same action here in a view and we're just going to output this to our index page so we're just going to bring in some we're going to bring in our model like we've done here in the terminal now we just need to go ahead and create a view so i've already gone ahead here and inside of our urls probably in the core here we have a path to the the home page and our view is just going to be called example so let's just go ahead and create our view here so example we're going to pass in a request and then from here we're just going to now perform the the aggregation so let's just uh bring that in so let's just name this data equals so that's the data we're going to return so that's going to be the key in our dictionary so from here we just need to return so let's just return that so return render and then bring in our request and then we want to name our template so this template has already been set up here in the templates folder called index and then we want our data in our dictionary so data is going to be um in this case well let's go ahead and just give this a value data equals and then we pass that data over to our template right so let's go ahead and just import let's import the render so we can utilize that so we're just going to pass this data over if we can also if you like we can print so we can have a look in the terminal here so let's just exit this and we're going to then run the server so by running the server it just allows us then to potentially run this view which is then going to fire off our print so let's go ahead and access the home page so you can see there's nothing on the home page so i'll just refresh it a few times and when we go back into the terminal you can see that we're printing out the data and you can see the dictionary that we're printing out is data that's the key name that we've described and right here so let's go ahead now and just output that i'll put that in our index page so from here you can see that we're passing it over as data and that's the key name so data and then inside of the data we should have and we've set up our key to be called data so that's how we should be able to access the data so we can just change that if we like to something different the data um just to make sure it's different because we've used data so many times here just wanted to clarify this so it's called the data now so we can access um this variable so we stored the data in this variable we're passing it across here in our dictionary so that's passed across over to now our template so we can now access the data through data dot the data so that should display the information on our template keeping the code nice and explicit and easy to read you can see now how we've scaled this up so from django db models we brought in some more tools so we've had some or we've had an example of some so now we're bringing max so find the max number within a range or within an aggregated object set if you like find the minimum number and then the average so these tools are typically utilized here with aggregate so you can see now that um let's just get rid of the the all don't need that so you can see that i've set this up here we have four queries utilizing these different tools um on exactly the same field and you can see now i'm just going to output that uh the individual objects that are returned or the data sorry that's returned the calculations and then we're just going to output that to the template so what we end up with is this so i hope you get the general idea from looking at this example let's just go ahead and refactor this example because we don't need to create multiple queries here to perform all these different actions we can do this with one aggregate so let's just go ahead and we're going to remove this and we're just going to bring it into the one query if you like so we can bring all of this out here and to use aggregate to perform multiple calculations in this case on the same on the same field okay so we just take that out so you can see that it's separated by commas let's just go back to our template but actual fact if we did go to our template we would have an error because notice what we're doing here we've set the names so this is some so let's just go ahead and call this now data so that we can now access this um so we don't need to now have all of these here now we're going to just need one so data so we're going to pass some data and obviously inside of his data we're going to access the individual calculations or numbers through the names that we've defined here so some max min average okay so that should collect the data so let's just call that data okay so we're going to collect the data on our template through the context name data or the key name data so let's go back into our index uh so now we're going to need to change that to data and inside of our data we should have these key names right so let's go back in and refresh and there we go so just taking a look at the final example there you can see we've just moved all of those calculations into the one aggregate let's now finish this tutorial looking at how we might want to implement this utilizing a class based view so first of all let's just convert so i've commented out the previous code let's bring in for example list view so this should work with any of the other generic or some of the other generic views if you're using generic views so let's bring that in and then what we're going to do here is we're just going to set up a new class here called example bring in the list view and then we can go ahead and select the model that we want to use and then the template so our template is called index.html inside of a templates folder so if it is in a folder we need to define the folder in the templates folder if that was the case but here we're just using the root here of our templates folder okay so now let's go ahead we're going to pass the data over in context uh so that we can then capture it on our template so we're going to override the get context data and then first of all we're going to basically just select all of our context data so we're going to get all the context data and that's then going to allow us then to add some more data so here we're going to add a new piece of data so the key think of this uh the context as a dictionary so we're going to add some more data to the context when we pass it over to the template we're going to capture that information and display it so here we're going to give it a name so this is um the context key of the key value of our of our new entry in our dictionary so i'm just going to call this ratings count so like we've done before we're just going to use the aggregate here so books objects aggregate and so that's going to then we're then going to get the ratings count and we can just just uh basically count all the ratings in this column so like we've done before so once we've done that we then just need to return the context so that's just adding more information to the context now that's going to be passed over to our template so we're going to need to edit the template so what's important here is to remember the format so here we're going to give our we're creating a new key value so this is the name of the the key and if you remember using sum so what's going to happen here is an automatic name is going to be generated or semi-automatic name so it's going to be rating count double underscore sum and that's how we're going to then select this data so let's just have a look at that in the template here so we select the key rating count and then remember how django is automatically going to create some a new name which is going to be rating count and then double unscore sum so that's the actual data so by doing that we should be able to capture that new information and that should now appear on our template so let's take a look at that okay so i'm just saying template refresh and there we go so hopefully we have a better understanding now of aggregation i wanted to stop here before we move into deeper concepts of aggregation and talk in the next tutorial about annotate once we then have a clearer picture of what aggregation is and annotate is we can then move deeper into aggregation so thank you very much for listening hopefully it was useful and i hope to see you in the next tutorial

Channel: Very Academy

Views: 2,413

Rating: 5 out of 5

Keywords: django orm, django aggregation, django aggregate, django sum, django max, django min, django avg, django average

Id: oHJF9mfswzo

Length: 19min 13sec (1153 seconds)

Published: Tue Jun 22 2021