Advanced Django ORM Features (Q-Objects, F-Expressions, Aggregations and Annotations)

[Music] hi everyone I hope you're doing well today are going to be exploring one of jango's most important features and that is the Jango omm if you use jangle then the omm is one of the ways that make using jungle easy the jungle is a way through which you can be able to interact with your database and do things like basic crow in this video I'm going to be walking you through some more advanced concepts while using the jungle web framework orm I hope you enjoy this video and without further Ado let us get right into it so to begin this video I'm going to be showing you the simple database model that we have right here which is a book database model and this model has the following Fields it has the title of a book Because The Entity or the table that we are creating is one to store information about books so each book has a title it has an author it has a description it has a language a page count created at date and the updated at date and we also have a string representation for whatever book object that we shall get one thing we have also done is to go ahead and register this on the admin so we have admin. register books but we can customize it as far as we want so I'm just choosing to just register it and have the default uh way it looks on the admin another thing we have done is is to go ahead and register this app in which the books model is into our project installed apps list so for now we can go ahead and try to see what is on the admin on the admin panel I've created a list of books and these books are information from a certain website of books so I think my server is not running but all I have to do is to go ahead and say python3 man py run server and this will go ahead and run the server so when the server is running we shall notice when you go back to the admin that we already populated our database with information about books so that we have some information to work with in this video so I decided to just go ahead and do this so that we can be able to skip this entire step so once we have that then let's start by looking at how we can do basic queries with jangle so the most important concept when doing queries with Jung is going to be the query set and what a query set is is just a group of objects that is returned whenever we query the database now to begin we're going to go where we have our server running and right here in my vs code I'll go ahead and open up a new terminal and in this terminal so I guess I have to put this to the left so in our terminal right here I'm going to begin by opening up the jungle shell so for me to do that I'll run Python 3 money py and then shell and once that is done we are now going to go ahead and create our first query set so right here in now shell I'm going to start by accessing the simple database model that we've created in books models.py so what I'm going to do is to say from the modeles that's inside our books so you going to say from books do models we are going to go ahead and import the book model that we've created so this book model so I'm going to go ahead and say import book and once we've imported book we're going to create our first query set so the first query set we're going to do is going to be called q1 and this is going to be our first query set this query set is going to be responsible for getting all books that are present within our database we're not going to carry out any sort of ordering or any sort of filtering all you're going to do is to go fetch all books and then return them in a query set of objects so to do this we're going to say q1 is going to be equal to our book model then we're going to use our object manager and then we shall call the all method and what this is going to do is to create a query set of all the books that we have stored within our database so to do that we're going to go ahead and say enter and when you call q1 you shall now notice that this is returning the list of all books that we have within our database so this is this is what a query said is it's basically a list of objects that are returned on a specific query now we can also go ahead and do something more more interesting with this query right here so I'm going to clear my with control and L and then I'll go ahead and create another query set based on the query set that we've done so to begin I'm going to say Q2 and Q2 is now going to be built off q1 so you can go ahead and also filter objects from a certain query set that we've initially had so in this case we're going to use q1 and then we shall call a filter method what this does is to help you filter through a query set and return objects that match a certain criteria or a certain rule so in this case what you want to do is to go ahead and search for all the items that have the language of English now to go ahead and do that I'll go ahead and say language and in this case shall say that want to specify the language to be English so this is going to return the query set of all our books and return all those books that have the language being English so if you go ahead and say enter and call rq2 in this case we're going to return a query set query set of items and these are going to be objects that have the language being English now let's make this more interesting what we have here is an unordered list and in case we did not set up the default ordering of our books which we can do via the uh The Meta class that we can use the ordering attribute on we can also go ahead and do that by using the order by Method on our query set so what you can do is to say Q2 do order by so sorry for this this is going to be order by and in this case we're going to order by the date at which we created these items so we're going to order by the created at date so this is one that's being stored every time we keep or each time we create an object of type book so that's the whole purpose of the auto now add argument to the DAT Time Field class it tells us to just go ahead and save the item at the specific date that we went ahead and populated it so in this case if we go back to our right here and say order by we want to specify the field that we want to use to order so in this case say order by created at and this time let's call this Q3 so in this case we shall call this oury set number three and when we say Q3 this is going to return our query set in the same way as we have it here and this is because we have just kept it to the default order which is the uh the one for the the ascending order but in case you wanted to order this by minus that created or by one that was created the latest then all we shall have to do is to go ahead and say that our Q3 is going to be q2. order by and in this case we shall specify that we want by the negative created at date and what this is going to do is to order them by the first item being the one that was created latest and the last item being the one that was created atest so if you go ahead and enter and then call our Q3 in this case we're going to see that our list is going to be ordered in another way what was first in the previous section is going to be the last and what was last is going to be the first so it's going to order them by the date at which they were created but it's going to order them in a in a more descending order jungle also allows for the use of the exclude method that helps us to filter out items that do not belong to the query set that we want and let's look at how that does so I'm going to create an next query set which is going to be query set number four and this query set number four is going to exclude items that are going to match a certain criteria or items that are going to be following a certain set of rules in their structure so in this case we are going to begin by getting our query one so in this case we can say query one I mean query 4 is going to be equal to the value of query one but let's try to exclude all items that have a certain rule so for example in this case what you can do is to go ahead and say that our query 4 is going to be equal to query 1 do exclude and in this case we have to provide uh a rule for us to exclude so in this case I let us try to use what is called a fill L up so jungle we can also go ahead and specify what we need to put inside a filter or exclude statement and this is basically where all the details about the where statement in the SQL will go so for example if you wanted to select all items where the price is greater than a certain price then this is where you will be able to do it but it has an interesting way of helping you to do that so for example what we're going to begin to do is by excluding an item that has a title that does not match a certain criteria or a certain rule so for our case what we're going to do is to specify the field so our field is going to be titled just like you can see right here our books have a title but when you go back to our admin we can notice that these books have different titles now let's say want to exclude a books that has the name jungle in need so for example when look through our examples of books right here now let me make this a little bit bigger we can only see one book that has a title of jungle so for us to go ahead and do exactly that we shall go back here and what we shall do is to use this syntax of that D and then we specify the field look up now in this case we want to say I contains and want to point to that specific phrase within the title which we want to filter out now in this case our filter is going to be based off the word jungle so we want to get all books in query set one that do not have the word jungle in their titles if we press enter and this case we call Q4 we shall return a list of all books that have every other title except for that one book that has the title jungle now to confirm whether the book has been returned let us first of all find out the number of items that are found within our query set one so jungle ships with it a special method that we can call on query set objects and that is the count method now for us to confirm we can say q1 do count and this is going to return the number of items that are present within our database which is the quy set one which is rning 10 items but then for our query set 4 where we went ahead and excluded that one book that doesn't have the title of that doesn't have jungle in it in this case if we say Q4 do count we shall notice that it's going to return n nine items instead of 10 and this is because in the query set Q4 we excluded that one book that has the title of jungle or the one book that has the title that has the word jungle in it now let us look at other ways we can use field lookups in this case we can go ahead and create our quer set 5 and quer set 5 is going to be one 2 basically filter so in this case we're going to filter and we shall filter by uh all books that have let's say the page count uh being greater than let's say the page count is going to be greater than the let's say the our page count is going to be greater than let's say 100 so to do that we can use double underscore and then in this case we can say greater than or equal two and then we say 100 and what this is going to do is to return all books that have their page count greater than 100 so if you went ahead and confirmed in this case we can say Q5 do count and in this case it's going to return eight books meaning that two books have their page count less than 100 so this is basically how jungle works it helps you to play around with a query set and also be able to perform an action based on that specific query set for example we can also chain on this query set another filter so we can say filter and this case we can also specify that want to filter by that item that has its language being English so if we go ahead and say language in this case we shall go ahead and say language is going to be English and when you say that it's simply going to go ahead and return all books because all these books have the language being English so that's how we can perform simple queries we can also go ahead and count item objects and we can also go ahead and basically do things such as field lookups and other interesting things all right so before we begin I would like to introduce one chain that I've done on our book model class so the book model class has a new field which is that is bestseller field and this is a Boolean field that has a default value of false and I've also gone ahead to make that migration and also make it reflect in our database in addition to that what I've done is to go ahead and also add other books on top of the books that we have within our database so we have now more books and some of those are bestseller books While others are not now I'm going to introduce the first Advanced concept and that is going to be Q objects so Q objects are ones that can allow us to filter items and do comparisons using logical operators so logical operators include and not and or and it's amazing how we can use the jungle omm to make filters based on logical operators such as and or and not so let us look at a very simple example so to begin we're going to go back to our shells so I'm going to say Python 3 money. py shell and within our shell we're going to create our q1 which is basically going to be want to get all items from our database so to do that shall say book doobs do all and this will return all books that we have within our database now I think I have a problem here because the book model is not yet imported so I'll go ahead and import that so I'll say from books do models we are going to go ahead and get the book model and I'll clear the terminal and once we've been able to do that then I'll call our q1 so q1 is going to Simply return all the items as we've created them from our database let us look at how we can formulate a simple statement that makes use of Q objects so to begin we have to first of all import our Q class so a C class is formed within uh the jungle DB modeles uh it's one within the jungle DB mod modes module and to access it you have to go ahead and say from Jungle this is going to be from jangle DB do models we are going to go ahead and import our Q class and once we've imported our Q class now let's create our C set let's call it Q2 and our Q2 is going to be one to get our query set of q1 and in this case we're going to make use of the filter so let's start by looking at the or so to formulate let's say a query set that has all books that have the language of English or the page number being greater than a certain amount then this is where the Q objects really sh so for example in case we wanted to get by that criteria we can just simply create a c object and in this case we have to pass in that specific item that should return true or false or that specific rule that should that should return true or false now in this case we want to search for one that has the language as English we're going to say language oh sorry for this so this is going to be language and our language in this case is going to be English and in this case we want to go ahead and use or so for us to use all we shall use this special pipe operator and that is going to symbolize our or and once we go ahead and try to provide the next condition so our next condition or rule is going to be one in which the page count let's say our page count do we call it the page count yes we called it the page count now in this case we are going to say the page count is let's say less than or equal to 100 in this case if we say less than or equal to 100 have to specify that and when you press enter so it seemed like we made an error we didn't close this quote so I'll go ahead and fix that so that is going to create for us Q2 which is going to be a query set of all books that have their languages English and also their page count less than or equal to 100 so if we go ahead and try to uh create another query set using Q objects let us try one that is actually the opposite so in this case we can do something let's say Q2 is going to be equal to one where we exclude let's say q1 dot let's say exclude and in this case we want to go ahead and exclude all items that have their language as English now note that an or statement will return true when one of the statements is true so in this case all these books are in English and therefore it will return true because of the truth of this one statement so in this case if we say language is equal to English now we are excluding all books that have their languages English or those books that have their page count being less than 100 so in this case we say page count D less than or equal to 100 now let's close this and when you press enter now let's call this our Q3 so that we keep this objects different so this sare set is going to be Q3 and when you call Q3 this time we expect Q3 to have less items so this is basically how we are dealing with all statements or this is how we are basically doing comparisons or using logical operations and in this case we're using the or operation with Q objects so the Q object is one that's going to formulate truth value out of what criteria you've provided and then by use of a logical operator such as the or we've used it will go ahead and use that operator on the other statement that you provide in this case what we've looked at is basically the use of the or statement but then let's go ahead and also formulate query query sets are based on our and or not so let's just actually do the and so for for to do the end what you shall do is to go ahead so I'm going to uh clear my terminal I create a query set called Q4 and Q4 in this case is going to be let's say q1 do filter and in this case shall create Q so the first q that we're going to have is going to be one two check if let's say a book is a besteller and if it has a certain page count now let us Begin by saying that we want to check his best seller sorry for this this is going to be is best seller and in this case we want to check if best seller is true so this is going to return true and in this case shall have to provide a logical and and in this case this is going to be Q so for our Q is going to be page count and our page count in this case is going to be we shall use a fi look up to State whether it's greater than let's say uh greater than or equal to let's say 400 so we shall close this so we have an invalid syntax it seem like we are supposed to use I don't know where I forgot and wrote two % so this is going to go ahead and return this query set where we are having all of these being truth values now one thing you need to understand about the logical and is that the logical and does not allow for one of the statements being false whenever you're using a logical and you should not have any statement being false that's if you want a truth result to be returned and in our case this Square set is going to go ahead and return only those objects or those book objects in which we have the besteller attribute being true and when where we have the page count being greater than 400 so I hope you've understood what I mean by that if you go ahead and say Q4 in this case it's going to return only one book that follows that specific criteria so in our case we have one book where the book is a best seller and the page count of that book is greater than or equal to 400 so this is basically how Q objects work they just allow you to be able to use logical statements like the and the or and the not to just be in position to query the specific objects you want to query and this is one of the ways you can formulate really complex queries by using conditions just like we've seen here now that we've looked at Q objects let us also look at the concept of aggregation so using query sets we can also go ahead and try to perform calculations Bas of the fields that we have within our databases so let's go ahead and look at how we can do that so to begin we're going to go ahead and access some of the classes that can we can use to perform calculations on some fields that we have within our database tables so to do that shall first of all go to jungle. nib do model and we shall import the following we shall Begin by importing average and then we shall go ahead and import let's say Max and we can also go ahead and import uh let's say sam so all these are classes that can perform actions on as on our database attributes and then return for us those values so let us Begin by first of all getting our query set of q1 and then maybe returning the total of all the pages that are contained in all the books that we have on each of the objects of our database or each book that is actually present within our database so what I'm going to do is to say uh q1 Dot and in this case shall use a special method called aggregate method Pome it's called aggregate not aggregate so it's going to be Aggregate and in this case shall call one of the methods that we've specified and that one method is going to be one for finding out the sum of all the let's say the page counts so if we say page count in this case it's going to go ahead and return the sum of all the pages that are contained within our books with this we can perform things like the average so if you want to find out the average then all we need to do is to go ahead and use the average class and that will go ahead and return the average of all the page counts that we have within our database table for the specific books we can also do things such as finding out the maximum amount of page numers so if you go ahead and say Max page count that will go ahead and return 1,000 meaning that the book with the highest number of pages is going to have 1,000 pages and that is just how aggregation works it helps us to perform multiple calculations onto our database fields and this is very important in cases where you're dealing with calculations and those calculations depending on the values that are stored within some database Fields now that we looked at Q objects and how they can allow us to use logical operators to make queries then let us look at F Expressions so there may be situations in which you may want to do calculations on certain fields of your database but you may want to access data from those specific fields and this is where F Expressions come in handy they allow you to refer to a value of a field so that you can be able to use it for an update or a computation or any other thing that you may want to do for your data that is stored within your data Bas let us look at a very simple example so I've opened up a new shell right here and within this shell I don't have most of the stuff I imported though if I scroll I can get to see all the things that I did in the previous parts of the video so I'm going to first of all Begin by reimporting our book model so I'll say from books model we are going to go ahead and access our book model and once we've been able to do that and I also import our F class that's going to help us to create these F Expressions so I'll say from jungle. DB do models we are still going to go ahead and import our F class so once you've been able to import our F class let us look at a very simple example that may be helpful when using our F class or our F Expressions so imagine a scenario where I want to update the count or be able to to increment a count or use a specific value that is a number to perform a calculation somewhere on a certain database field in this case let's say we want to do that for our page count so we need to begin by querying for a book object so I'm just simply going to go ahead and use our book model to get so I'll say objects get and in this case I'll get the book with the ID of two once I get the ID of two and that is going to return this book right here so what we want to access is the page count of this book and that is going to be our 200 Pages now if you wanted to go ahead and update this it's going to be as easy as saying book dot in this case page count and then we shall increment it by saying plus equals and then the amount of pages you want to go ahead and add for us to see this changes take effect or we have to do is to say book uh book. save and then this will go ahead and save the result to our database so in this case if we said uh something like book uh book. page count in this case we shall get 210 because we have incremented this count in our database but keep in mind that what is happening here is after getting our book object we still have to load the data of that specific page count into our memory so F Expressions allow you to be able to push that to the database end so that python doesn't have to handle that and that is very useful because in a scenario where are performing these calculations it may be very important for the database to handle that instead of your python s handling it so let us look at another example we we going to update the account of this specific book by choosing F Expressions so for us to update the account all we have to go ahead is to access our book object and then we shall say book Dot Page count so in this case we're going to access the page count or the current page count but if you want to access the current page count and then increment it this time we shall use an F expression now the way you do that is by providing F and then create an instance of the F class and then providing that specific type to or that specific field you want to go ahead and access in our case it's going to be our page count so once you've imported our page count now let's say wanted to go ahead and subtract the 10 pages that we added onto our page count in this case we can go ahead and say uh minus 10 and this will go ahead and update this to the page count that we had before if you want to go ahead and access our page count this time we shall do something like book do save and then when you try to access our book page count we shall say book do page count now one thing you'll notice here is instead of returning the book page count it's going to return a combined expression object so this means that we shall have to go ahead and requery the item for us to be able to see the updated values on our specific object so for us to be able to do that then we're going to call a specific method called book. refresh from DB on the object that we've updated using our F expression so book attribute has no refresh DB so it's going to be refresh from DB and in this case if we try to get the book page count we shall now see that our updated page count is going to be 200 now the interesting thing is we've been able to do that without having to use book. page count then minus the page count and that is one way in which you can easily access database fied values without having to qu the object and then accessing that value on the object let us look at another example now that we've looked at an example of how if Expressions work let me show you another example so I've introduced another field which is the price field and this price field is one to help you access the price of a certain book so do not worry about the values of this CU I've populated them in the database already so let's just go ahead and try to make complex queries for discounts that we may provide on this so the first concept I'm going to introduce the concept of annotations jungo provides the annotate methods on the query set that is useful as it allows you to create fields on the objects that are not part of your database Fields so let's say we have a price field right here we can also create a discounted price field and that will be dependent on this price field that we have right here let's look at how we can be able to achieve that so I'm going to go right here in my shell and the First Command I'm going to run is one to open up our shell and once I've imported that or once I've opened our shell the next thing is going to be for us to import our book model so from books do models let us go ahead and access our book model once we've been able to access the book model the next thing is going to be for us to import the F class because that's what we're going to use to carry out these F Expressions so I'm going to go ahead and say from jangle do DB do models we are going to access the F class and once we've imported the F class next thing is going to be for us to create a query that's going to return all our objects annotated with a discounted price depending on the price of a specific object so for us to do that we're going to go ahead and say books and our books is going to be a query set but this is going to be from books do objects and in this case not that we're going to call the unot method cuz that's what we need to go ahead and annotate that discounted price field so I'm need to go to a new line and in this case I'll say discounted price so our discounted price is going to be equal to what we shall get by using our annotations so in this case we are going to create an F expression to access the price of an individual book object and then from that we shall basically subtract 15% because that's the discount we want to give on these books so for us to achieve that we shall say 0.0 or actually going to be 0.15 multiplied by the price of that specific book so mind the fact that I'm using an F expression to access the price of a certain field without having to basically call the object and this is the B about F Expressions you are just having uh you're just having uh and if expression you provide the attribute on that on that object and then you're able to get that object so I'm going to wrap everything within brackets just like you see right here and then we shall close what we have here so the brackets are 1 2 3 4 so I have to add another bracket to close the entire thing so I think I've made a mistake I've called our query books so let me go ahead and repeat this and remove the S right here let me also go ahead and remove that so if I go back and fix this we're now going to have our books which is going to return all books within our query set now the interesting thing is each of these books is going to be returned with another field that does not belong to the model and that is the discounted price field so if you try to access the first book we shall basically access it with books zero because this is sort of like a list of books and to return the first book so if we try if we try to access book Zero's price that's going to be $11 if you try to access the price that's discounted in this case we are going to get the discounted price and that is going to be what we calculate using this annotation right here so basically this is how it works if you wanted to go ahead and do that for the second book then you shall have provide index number one and the discounted price is going to be like that same thing for book number two and that will be the discount so that's how we can use annotations to add extra fields that are going to hold information that we calculate based on other fields I hope you've understood this example before I end this video I like to talk about PCO Uganda P Uganda is uganda's developer that is focused towards python programmers so if you use python in your daytoday work whether data scientist web developer if you do some scientific competing that is using python then this event is for you if you want an opportunity for you to network make connections and also get to learn from people who use python in the real world then this is a very good chance for you to do that if you want to book yourself a ticket I'm going to leave the link to the official website of Pon and hope to see you there in this video we've looked at some of the advanced features when using the jungle omm we've looked at basic queries and how we can customize them to our needs using things like f Expressions Q objects and so on I hope you've enjoyed and learned from this video if you're new to this channel my request is you click the Subscribe button so that you can subscribe to my channel and be able to see the variety of the videos I have on the jungle web framework topic and if you like this video please leave a like it helps me it helps me by pushing these videos in the algori I hope to see you in another video thanks for watching bye

Channel: Ssali Jonathan

Views: 404

Keywords: django, django tutorial, django orm, f expression, django queries, django examples, django f expression, django database, f expression django, django tut, django framework, django f, f expression django query, learn django, django beginners, beginners django, f expressions, complicated queries in django with f expressions:, django aggregation, django how to, django annotate, python django

Id: DqGg5MVS71Q

Length: 39min 12sec (2352 seconds)

Published: Sat May 18 2024