Django ORM - QuerySet Filtering and Lookups / Ordering and Slicing QuerySets

so far we've just been working with a script and creating and deleting records as we've been working through that script in this video we're going to create a repeatable data set using our Django management command that we can use in future videos and this is going to allow us to delve deeper into some of the concepts in Django's orm and as well as creating that command what we're going to do in this video is dive further into the filter and exclude statements in Django these can be used to control and filter the results that you're getting back from the database based on conditions we're also going to look at the order by statement to control the ordering of a query set of models and we're going to look at the exists function as well which is a Boolean function that returns whether or not any models meet the conditions that you specify and finally we're going to look at the earliest and latest functions that can be used with date time fields in your Django models and we're also going to dive further into lookups in this video for example the greater than and less than lookups the range lookup that can be used to see if a values between the two boundaries and we're going to see the N lookup as well which can be used to check if a particular field has a value that's in a predefined set of values and we'll look at some other things in this video it's going to be quite a comprehensive video on some of these topics so let's get started now I've created a GitHub repository for the orm series I'm going to commit some of the code that we're going to work on to this repository in the future but I've put up the starter code that now contains in the core project and management slash commands folder and within this folder we have a python script called create data.pi now all the other codes in this repository is just the same code that we've been looking at throughout this tutorial series but I have added this create data.pi script so if we go to vs code what I'm going to do is go to that create data script this is a Django management command so it extends the base command class and that has a handle function so let's very quickly go over what's going on in this function and remember you can clone this directly from the repository and follow along with the video so we're looking here for a user with a username of admin in the database we're going to check whether or not that user exists and if they don't success we are creating that super user with the username of admin and a password that we're just setting to test here so we're getting the user within this if and else statement here and by the way if you're not interested in looking at the management command then you can skip to the next section of the video below that we have a list of restaurants and each element in that list is a python dictionary that contains values for the fields that are defined on a restaurant model if we scroll down you can see that what we're doing in this script is deleting all the existing restaurants from the database and then We're looping over that list of dictionaries and we're using the restaurant.objects.createfunction and unpacking the dictionary of key value pairs into that create function so that will enter our value into the database for each element in this list of dictionaries and then below that what we're doing is we're creating some ratings so I've got a hard-coded value of 30 here and feel free to change any of these if you want so we're creating 30 ratings and we are using the random.choice function in Python to choose a particular rest restaurant from the query set of restaurants so this is a rating that will be applied randomly to one of the restaurants and finally below that we are also creating 100 sales in the database and again we're using random.choice here to select the restaurant and for the income generated from that sale we are generating uniformly a number between 5 and 100 and I should say as well above that the rating that we have here we are applying a random integer between one and five for that rating so this is the management script and we can run this with the python manage.pi and we can give it the name of the script which is create data that's going to then run this and add these objects to the database for restaurants for ratings and for sales now what we're going to do is verify that this has worked by going to our scripts directory and the orm script.pi file this is a script that we're running with the Django extensions run script command what we're going to do is we're going to print to the terminal here the result of executing restaurant.objects.count and as well as a number of restaurants I'm going to paste in these two commands here and we're going to also get the number of ratings and the number of sales in the database table so let's execute this with python manage.pi and we use the Run script command and the name of this is orm script and below here you can see the raw numbers coming from these commands we have 14 restaurants 30 ratings and 100 sales in the database so these match the values that we had hard coded here and for example this for Loop for the ratings 30 and 100 so these have been successfully entered into the database when we run that management command so now we have data in the database let's learn a bit more about querying that database using Django now we've already seen methods such as the dot count method that gets the number of rows in the database table and we've seen other methods such as dot all to get all of the rows and the first and last methods to get the first record and the last record in the table we're now going to expand on our knowledge of the filter and exclude functions in Django and while we're working with these functions we're going to also dive further into lookups in Jan angle that allow you to customize the where Clause of an SQL query so let's clear these print statements from the terminal I'm going to remove this comment and replace it with another one what we're going to do is filter down to only the Chinese restaurants and the data so let's write that statement here below it's going to be restaurant.objects.filter and we're going to filter on the restaurant type field and we're going to set that equal to the restaurant.type choices dot Chinese value remember type choices is our text choices class in Django that defines some values that we can use in the restaurant type field so if we scroll up here to the restaurant model we have our type choices class and we can see some of these values this is very similar to an enum it contains some predefined values such as Chinese here in this case and we are going to search for that when we use the filter command and we'll be past the restaurant type in here as a Chinese restaurant so let's print this to the terminal and then we can execute the script and we're running that script below here and you can see the query set coming back with three restaurants in that database stable that are Chinese restaurants and if we look at the query that's being executed here and you can see that there is a where Clause here and we're filtering down to where the restaurant type is equal to Chinese in this case that's defined by this CH value that we're storing in the database so what the filter statement does that we're running here is it takes the keyword argument that represents one of the columns in the database table and the value we pass in after that is what is searched for in the database and that's searched by doing an equality here so we're checking an exact value that's passed in whether that particular column has records that match that exact value and then returning them to the color now let's see another way to do this for example we might want to check for a restaurant with a given name so the field that we're going to use here is now name not restaurant type and the value we can remove the enum value and let's say we're searching for a restaurant called Pizzeria one let's now execute this query again and we can see the result and you can see the where Clause again at the bottom we're checking if the name is equal to what we've passed in as the value of this keyword argument so Django is taking these keyword arguments that we're passing to the filter statement and it's converting them into where Clause statements that are used in the SQL to get the right results back to the caller so what I'm going to do is store this in a value called restaurant this is just a variable and what we're going to do is we're going to print out the restaurant.count function and of course we expect this to equal one because there's only one restaurant in the table that has that exact name called Pizzeria one and the dot count function is defined on a query set in Django and a query set represents a list of models that you have coming back from the database now if you only have one model you might want to just get the value of that directly as a Django model class rather than having it as a query set so if we remove the call to dot count we are just going to print out a query set in djangle but what we can do is also call a function called dot get and that is going to attempt to retrieve the value from the query set as a Django model now will only work if you have one model in your query set only so let's comment out the connection.quaries we're not interested in the SQL here and we're going to rerun the script and you can see the two outputs on the top we have a query set containing a single restaurant with the name that we've passed in and when we call the dot get function we are converting that query set to a single Django model now we can actually do this on one line of code if we change the dot filter function to dot get then when we print the restaurant to the terminal we're going to see that that's a Django model instance it's not a query set but you have to be careful when you're using don't get this is a function that should return only a single Row from the database that is then converted into a Django model on the application layer so if you pass in parameters to the dot get function that will return more than one row from the database that's actually going to cause an error in your application that you're then going to need to handle so let's go to database browser for SQL Lite here and you can see for the restaurant type we have multiple for restaurants for all of these and we have I think it's five or six Italian restaurants here so if we then pass a parameter to don't get here that's going to return the Italian restaurants we would need to remove this and change the field that we're looking at to restaurant type and we can set that equal to Italian but we're passing this to the dot gate function which should return only a single row and this particular query is going to return more than one row so when we try to execute the script you can see the error at the bottom we have an error saying that multiple objects are being returned and that error is telling us that the dot get function returned more than one restaurant it returned five restaurants from the database therefore you need to be careful when you are using dot get this should return exactly one model to your application so if it returns more than one or if it returns no models then this is going to fail so let's go back to the dot filter function here and we're going to get back these Italian restaurants and we'll be print this to the terminal we can see the query set coming back and this query set contains all of the rows that have Italian restaurants converted into jungle models now you can check in a query set whether or not any rows have been returned by chaining the door exists function onto that query set that will return true or false it will turn true if any rows are matched by the query otherwise if we have no rules that match it's going to return false so if we execute this script you can see we are getting back true here because there are Italian restaurants in the database but if we change the filter here and put in some random nonsense for the name when we run the script again we're going to get back false because we don't have a restaurant with a name equal to that value so that's the exist functions very simple to use in Django what we're going to see now is how to perform queries that have multiple filters in the where Clause so let's remove this code here and again we're going to filter this down to only the Chinese restaurant so I'm going to pull out the value from that enum and let's now rewrite the filter statement restaurant.objects.filter and we're filtering down to restaurants with the restaurant type equal to Chinese now let's store the value of the squidy and variable code restaurants and we can print it out to the terminal and then what we're going to do is we're going to uncomment the line below here and that's going to allow us to print out the SQL queries that are executed in this script so let's run the script and we're going to see the output now you can see in the query set that we're getting back that we have three restaurants the first one is called Golden Dragon the second is Chinese two and we also have Chinese three so this is the same as we had before we're getting back a query set with three restaurants and if you look at the where Clause we have a single filter operation where we're checking the restaurant type being equal to Chinese so what if we want to add another filter statement here that can allow us to execute more advanced logic by using the and or or not Clauses in SQL so let's make the terminal smaller here and all we need to do if we want to chain an and statement to the where Clause is just pass another keyword argument to filter so as well as the restaurant type being Chinese you can see below that we have two restaurants that have a name it begins with the letter c so if we just want to get back the restaurants that are Chinese and have a name that starts with C we can use the name field on the model and we can use the start with lookup and as I said in the previous video lookups allow you to customize what's passed to a where clause in SQL in this case we're going to check names that begin with the letter c and this is going to create an and close in the SQL where Clause it's going to check for restaurant types that are equal to Chinese and whose names begin with the letter c so let's now clear the terminal and I'm going to run this script and we're going to see the output here you can see the query set that we're getting back no longer has three records it's down to two and those two records are the ones that both begin with the letter c and if we look at the SQL statement you can see the Clause that we had before with the restaurant type is equal to Chinese but we also have another Clause here where we are looking at the name field and we're finding records whose name is like this particular pattern here where we have the letter c and then the Wild Card percentage operator after that and that pattern allows us to check for restaurants that begin with the letter c but the important thing to note in this where Clause is the and operator here so the resulting query set is going to have restaurants that begin with C and whose restaurant type is equal to Chinese so in order to chain these and clauses together all we need to do is pass different keyword arguments to the dot filter or exclude functions so this is the starch width filter we're going to look at another filter now the N filter so I'm going to clear this code and Define three variables here let me tab these over we have Chinese Indian and Mexican and we're taking the values from that enum and we're storing it in a variable let's now create a variable called check types and we're going to set that equal to these three values we're just going to pack them into a list that's the Chinese the Indian and the Mexican values now what we're going to try and Achieve here is we're going to try and find all restaurants that are one of these values so let's create a variable called restaurants and we're going to use the restaurant.objects.filter statement again and again we're going to check the restaurant type which is a field on the restaurant model but instead of just using an equality here we're going to chain the end lookup so two underscores and then in and we can set that equal to our sequence in Python for example a list that we have above so what this is going to do is it's going to find all restaurants whose type is one of these three values so let's print these restaurants to the terminal and we're going to run the script again and you can see at the top here that we're getting back the query set that contains Chinese restaurants it contains Indian restaurants and it also contains the Mexican restaurants as well but it doesn't contain the Italian and other restaurants that we have in the data and if you look at the SQL query you can see a new operator that's being used here and that's the N operator and that's used to check if a particular column's values are in this set that we Define after the N operator so we're checking if the restaurant type is one of Chinese Indian or Mexican that's the query and it's returning that query set to Django consisting of the correct models so we've seen a bit more about how to use filter and how we can use multiple keyword arguments to chain them together using an and close and we've also seen how to use the N lookup here in order to translate that to an in SQL operator to check if a particular column's values are in a predefined set we're now going to look at the exclude method in Django and this method is the inverse of the filter method and it results in exactly the same SQL query that's executed by the filter method but it simply adds a not operator before the where Clause so let's see that in action we're going to remove all this code but I'm going to keep the reference to the Chinese restaurants around and let's say that we want to get all the restaurants in the data except for the Chinese restaurants so we don't want to get Chinese restaurants we just want to get any other type of restaurant so what we're going to do is write a query here and store it in a variable called restaurants so it's going to be restaurant.objects.exclude and instead of filter we're now using this exclude method and we pass the condition to that so restaurant type equal to Chinese as the condition and unlike filter when it's going to return values where the restaurant type is equal to Chinese this is going to add a not to the where clause and it's going to then result in returning any restaurant whose type is not Chinese so let's print these restaurants to the terminal and we can execute the script and I'm going to make the terminal bigger here so we can see the query you can see at the top the query set we're getting back contains the Italian and Indian restaurants and so on but it doesn't contain any Chinese restaurants and if we look at the SQL statement here we're getting all the values from the restaurant table but we have this we're not statement and we're getting values back where the restaurant type is not equal to Chinese so what happens with the exclude function in Django is that it adds a not close to the SQL statement and specifically it adds it to that where Clause when it's doing the filtering so when you use the filter function you're seeing give me back the records that meet the conditions that we're passing to the function as arguments but when you use the X squid function we're seeing get me back all the records that do not meet the conditions that we're passing and we can use lookups with the exclude function just as we did with the filter function so let's grab Indian values from that enum and we're now going to get all restaurants where the value of the restaurant type is not Chinese or Indian so in order to do that we can remove this equal sign and we can use the N operator and we can pass a list here equal to Chinese and Indian values in that list so this is going to exclude any restaurants where the restaurant type is one of these two values so let's execute the script and we can analyze the output at the top here and again we have in the where Clause here and not operator and we're checking if the restaurant type is in Chinese or Indian and we're excluding that with the note operator so if you understand the filter function you should understand exclude as well it's just the inverse of filter so let's now move on we're going to show some additional lookups in Django now so let's say we wanted to get all restaurants that started with letters a b c C or D we can use the less than lookup for lists so let's write the query now it's going to be restaurant.objects.filter and if we want restaurants whose name begins with a B C or D we're going to use the name field and we can use the less than lookup now the value that we pass here is the letter e and what that is going to do is it's going to give us back all restaurants in the data whose name begins with letters a to d so let's store that in a variable and then we can print it to the terminal let's now run the script and we can analyze the output of that query so you can see at the top we have three restaurants printed to the terminal and these begin with the letters b and c so we're getting back all restaurants whose letter for the first name starts with something that is less than the letter e and if we look at the where clause in the SQL you can see that we're looking at the name column and we're getting back any restaurant whose name is less than the letter e so when you use the less than lookup in Django it's going to translate that to this less than operator in SQL and it's going to use that within the where Clause to filter the results you're getting back from the table now it's probably more common to use the less than lookup with numerical data rather than with a car field or textual data so what we're going to do now is we're going to look up a different field and I'm going to look up the longitude field and let's get all longitudes that are greater than the number 0 here so this is another lookup very similar to the LT less than lookup this one is greater than now basically when you have a longitude that's greater than zero you have a longitude that is east of the city of London in the UK and that's because London is basically on What's called the prime meridian now longitude is a float field in the database so if we're using the greater than lookup here it's going to check for values numerical values that are greater than what we pass in which in this case is zero so let's execute the script and this time we can see two values in the query set and it's for a pizzeria and an Italian and I've hard coded some longitudes into the data set so these are presumably in Italy which is to the east of the UK and again let's look at the SQL where Clause we're looking at the longitude here and we're checking for longitudes that are greater than 0.0 and we're using the greater than lookup which converts to this greater than operator in the SQL workload so this query is giving us restaurants that are east of London basically what if we wanted to get restaurants that are west of London well all we have to do is either change the filter to an exclude function you can see this time we're getting back all restaurants that are west of London and in the SQL statement here is the lookup where we're checking if the longitude is greater than zero but this time we have a we're not close used instead and another way to check if restaurants are west of London is you can continue using the dot filter function but change the lookup so we can look up values that are less than zero in this case and that should give us the same output but it's going to use a slightly different query we don't have a wear note close we just have a where Clause but we've switched the operator from greaterland to less than so it's different ways of achieving very similar or the same results in Django using these operators so let's see a few more examples here of these lookups I'm going to use the sale object now and I'm going to store these in a variable called sales and the sale object has a field called income and we're going to get incomes that are greater than 90 in the data so let's execute this and if we look at the result here we get a bunch of sale objects in this query set and the where clause for this is looking at the income field and getting values greater than 90. so I think it's easy to understand the greater than and less than lookups and by the way there is also greater than and equal to you just put an e at the end of that and also less than or equal to as well so these exist if you want to check if something is greater than or equal to or less than or equal to a value what we're going to see is another lookup here and then we're going to move on to ordering and this is the range lookup so to the range lookup you can pass a tuple of two values the lower boundary for the lookup and the upper boundary for the lookup so let's see we wanted to get all sales that have an income that's between 50 and 60. let's execute this script with the range lookup and we get back some objects but let's look at the SQL here at the where Clause we're looking at the income field and we see a new SQL operator called between here and we're getting incomes that are between 50 and 60. what's happening is it's translating the values that we pass in to an SQL between statement and it's getting back the records that are between those two values and just to prove that we're going to use a list comprehension here and for each sale in the sales that we're getting back we're going to print out the sales income and we're going to make sure these are all between the values 50 and 60. so you can see these decimals coming back 51 58 and so on all of them are within or rather between those boundaries and that's what you can do with the range lookup it's quite powerful and you can also use this with date times in order to get all the rows from a database that fall between two dates and that can be very useful for performing date based analysis and we're going to see more of that later in the series let's now move on to ordering records in Django query sets so when you query a database you often want to get back records in some sort of order and SQL has a statement called ordered by which can be used to achieve this Now the default ordering and a Django model is by the primary key of the particular model and that's usually an ID field that's numerical so when you call restaurant.objects.org you get back a query set that contains all of the rows in the database but they are ordered they're ordered by the primary key of the table the ID but you can override this if you want and we're going to see how to do that now so I'm going to remove these two lines of code I'm going to fetch the restaurants by using the restaurant.objects.order by function and the order by function allows you to specify an ordering to get back a query set of Records in a particular order and you can order by a particular Fields values in our case we're going to get back the restaurants ordered by the name value so let's print these restaurants to the terminal and we can execute the script and you can see the output at the top here we have the restaurants in a query set and these are ordered by the name so you can see the name beginning with b then C then G and so on and if we look at the SQL statement you can see the order by statement appearing in that SQL and we're ordering by this particular field called name and we're doing that in ascending order by default and ascending order for textual data means we're starting at the letter a and we're getting back all records in order up to the letter Z so this allows us to customize The Ordering of a query set and we can use djangles order by function to do that but what if you wanted to order it the other way around for example if we wanted the restaurants that begin with a later letter first what we could do is because we have these in order of the name we could use the dot reverse function which is another function on Django query sets if we chain that to the order by and then re-execute the script you can see we get back the restaurants in the reverse order and looking at the SQL you can see the order by statement this time we're looking at the name field but we have a descending argument after that so it's going to get the records from top to bottom essentially so that's one way of doing it we call order by we pass the field and then we can reverse the query set what we can also do is we can pass a minus to the field name like this and this is a Django specific syntax where we're telling Django just by using this minus operator that we want the values back in reverse order so let's save that and execute the script again and this gives us back the same results as we go when we called order by and then the reverse function and you can see that descending keyword in this order by close and this is going to return all the restaurants where the name field is ordered from top to bottom and the query set and this works with any field it doesn't just work with textual data you can use this with numerical data and you can use it with date time Fields as well for example if you wanted to get back all sales we have in the sale table our date time field that captures when the sale occurred so if you wanted to get back the most recent sales we can go to our orm script here and we can use the sale.objects.order by and this time we're going to pass the minus date time to that this is going to return the most recent date times to us earliest in the query section so let's execute this and we can go to the terminal here and you can see we are getting back a bunch of sale objects but if we look at the order by statement it's ordering by the date time and again it's in descending order now I'm going to bring back the code for getting the restaurants ordered by the name now there's one problem with this statement for textual data and that's that it doesn't take into account the case of the restaurant names that are in the database so what happens if some restaurants begin with lowercase characters in the database and others with uppercase characters let's demonstrate this problem by pulling a restaurant out of the database so we can do that with restaurant.objects.first and what I'm going to do is reset the name of the restaurant so we're going to use the existing name but we're going to convert it to lowercase once we've done that we can save the new value to the database and then we're going to see an example of what's going wrong here when we run this script again so let's run it so if we look at the query set that's coming back here you can see we have everything ordered by the name as before but we have at the end here a restaurant called Pizzeria one and this begins with the letter P but it's a lowercase p and this comes after other restaurants such as Taco Bell and that's a problem because we're ordering by the name and we don't particularly care about the case here we want to make sure that the returned values are case and sensitive so how can we do that in Django when we have a text field and we're ordering by that field but that particular text field might have different cases as the first letter now it turns out that Django allows you to execute database functions when you're running queries so what we're going to do is we're going to bring an import in at the top here from django.db.models.functions we're going to import a function called Lore and we can use that in the order by Clause to lowercase the values in a particular column when we're doing the ordering and that will result in a case insensitive ordering at the end of that process so what I'm going to do is remove these three lines of code and we're going to use the lower function directly in the order by statement here and we pass the name of the field that we want to apply the lower casing too so that's the name field and that is going to be used as the values to the order by function so let's now execute this script and we can look at the output at the top here you can see this time we have the pizzeria 1 appearing in the correct place within the resulting query set so in order to get a case insensitive ordering we are using the lower function on the name field and passing that to order by and if we look at the SQL that's executed here we can see that we're actually executing a database function called Lore to lowercase the restaurant name as we're doing the ordering so Django's function modules allows you to execute functions at the database layer and this can be useful to do your processing on the database server rather than pulling values into Python and then having to amend all of those values in your code base so let's demonstrate a few other things with ordering here we're going to show that we can actually use this with date time Fields now we've already shown this but what I'm going to do is go to models.pi if we look at the restaurant model up here we have a date open field so I'm going to quickly show an ordering by that so we can remove the call to lower here and we're going to paste in the field date opened so let's save that and execute the script and when we order by the date open field what it's going to return first of all here is the restaurant that was opened at the earliest date up until the restaurant was opened at the latest date now we've already seen that with the seal model but I want to show something else here and that's how to index into a query set now we can index in at element 0 here and if we execute the script and look at what we get back we get back a model so when we index in at one particular index Django will see that indexing in a particular element and it will convert that to a limit clause in SQL now limit is an SQL keyword and it's used to control the number of rows that are being returned to the caller and when we limit to one here it's going to return a single row and that's because we have index then and our indexing doesn't contain any slices we're a hard recording an actual index an actual number here so it's going to return a single model to us not a query set now if we use a slice for example we could get the first five records in the restaurant table in order of the ones that were opened earliest if we execute this and look at the results this time we're getting back a query set containing five restaurants and you can see in the query that's being executed here we're ordering by the date open field and we're limiting to only five results in the output and that's again because this time we're using a slice operator and we're slicing into the query set and we're getting the first five records in the database table now what if we wanted to get all records between two and five here we can use this notation and this is just coming from python list indexing notation this will give us back all records between two and five so let's now see the resulting SQL query for this one if we expand that you can see we get back three restaurants and this time we can see a new keyword in the SQL statement and that's the offset set so the query that's being executed gets back all restaurants from the database in order of date opened but at the end of the statement we're limiting to only three rows being returned but we're also offsetting by two and that's because if we go back to the code here we are indexing in with this particular slice in Python we're starting at element two and up to element five so that's going to return a query set of three models and we're offsetting the results by two because of that indexing now I'm going to finish this section on ordering by looking at a default way of ordering your model so what I'm going to do is go to models.pi and in fact I'm going to copy this import of the lore function out of the script and into the top of the models.pi file and you can set a default ordering on a Django model for example we're going to do that on the restaurant model here and you can do that by defining our meta class in your Django model so let's define an inner class here called meta and this is a common practice in Django the metaclass can provide option are applied to your Django models now the metaclasses are field called ordering that you can set to a tuple or a list that will specify the default ordering for that particular model when it's retrieved with a query set so what we're going to set this to is the lower name field and by setting the ordering to this it means that when we call any operations on this model with the orm unless we specify a different ordering it's going to try and order it by this default ordering in our case by lowercasing the name field so let's go back to the orm script.pi and we can remove the dot order by call and what we're going to use here is restaurant.objects.org it's going to return all the restaurants to us when we execute this script below now we're not specifying any ordering here but you can see that there is an order by clause in the SQL that's being executed and again we're passing the lower function of this name field so if you have a default ordering that you want to use unless you specify otherwise then you can go to your model and Define a meta class if you don't already have it and as an ordering field into that meta class and you can pass a particular column in there and you can also use functions as you see here in order to control that ordering and if two rows have the same name then you can also pass additional values to that for example you may want to order by date opened if two restaurants have the same name we're just going to keep it to the name field and we're going to save this file and we're going to move on to the earliest and latest functions in Django now earliest and latest are kind of similar to the first and last functions and they are convenient when you're working with date time fields in Django let's go back to the orm script.pi and what we're going to fetch here is a single restaurant and we're going to get the restaurant that was opened at the earliest date so what we can use is restaurant.objects.airlist and we pass a field or a set of fields to that now the field we're going to use is the date open field that's a date time field so the concept of having an earliest record makes sense with a date so the dot earliest function is just a convey minions function it allows us to take a particular database table and get back our record and that record represents the earliest date opened from this particular set of restaurants if we execute the script and look at the output you can see that we get back a single model so dot earliest doesn't return a query set it returns a single model to the application and you can see the query has it ordered by the date opened and it's limiting to a single row of output so earliest for a date time is kind of similar to the DOT first method and there's an adjacent method called latest as well and if we execute that we can see the resulting query it's going to return another given restaurant again it's a model not a query set and again the order by statement is ordered by the date open that we're passing as a field to the dot latest function and this time it's in descending order and again limited to just a single row so earliest and latest are just convenience functions in Django when you have a date time field on a model and you want to get the earliest and latest rows from at that particular table based on that date time field and you can see we're passing the field to the model here but what we can actually do is we can go back to models.pi and we can pass another field to The Meta class and that's get latest buy and this can be set to any date time or date field in your model so we have the date opened date field here we're going to get the latest by this particular field and what that means is when we go back to the orm script.pi we can just call the dot latest function and it's going to by default apply it to that field in our model so let's re-execute this and we should see the same output of Pizzeria 3 here so if you have a date time field in your model that you're typically ordering by you can either use it as the ordering field itself or you can use the get latest buy and set it to that value and that can help you when you're wanting to get back the latest the most recent record from the database or the most distant the earliest record from the same table so this has been quite a long video let's finish by looking at how to filter by by Foreign key and Django so let's say we want to find all ratings associated with restaurants beginning with the letter c so let's remove this code here and we're going to set up a query here rating.objects.filter so this time we're using the rating model in our application racing has a foreign key to restaurant and that's called restaurant.field and we can use the two underscores to specify a lookup to that related object so the restaurant is the related object and it has a field called name what we want to do is get all restaurants whose name begins with the letter c so what we can do is chain I'll look up to that foreign key field and that's the starch with lookup and we can set that equal to the letter c let's store the value of this in a variable called ratings and let's just quickly go over the syntax one more time we're filtering the ratings and we're looking at only ratings whose restaurant's name begins with the letter c so we can use the start with lookup on this foreign key field called name so when we're filtering by a foreign key field we use the name of the film that refers to the following key we have two underscores to refer to any fields on that related object and finally we can also optionally do lookups as well to that particular field so let's print out these ratings to the terminal and execute the script and you can see a query set of ratings here and if we look at the SQL statement you can see we have a where Clause where we're looking at the restaurant name being like the letter c here but something to notice when we do this query is we're actually doing an inner join on the restaurant table and that join is being done by looking at the restaurant ID from the rating table that's the foreign key and it's joining that based on the equality where we're looking at the restaurant tables ID field so it does the join and then it does the filter where it only gets back the restaurants whose name begins with the letter c so when you do a filter like this in Django where you're doing the filter on a folding Keys field Django is going to translate that into a join statement in the SQL and let's see one final example in this video assume that we want to get get back all seals that belong to a Chinese restaurant let's change these variables we're going to refer to sales and it's going to be sale.objects.filter and again we are filtering by the restaurant but we can remove the rest of this this time we're going to look at the restaurant type field and we're going to look for restaurants that are Chinese restaurants in this query and we can print those to the terminal so let's execute the script and you can see again in the SQL we get back a query set of sales but we also have this inner join going on in the SQL statement and the sale Tables Restaurant ID foreign key column is being joined to the restaurant's ID and in the where Clause we're looking at the restaurant type and we're checking for those restaurants that are Chinese restaurants so I hope that demonstrates how to do foreign key filters in Django and that's all for this video we've covered quite a lot of Topics in this video we've looked more into the filter and exclude functions including how to use lookups with those functions we've looked at how to order records in Django using the order by function and also by using the ordering field of a meta class and we've looked at the earliest and latest functions that are defined on models that have a date time field and finally at the end here we've seen how to filter query sets based on the value of a foreign key field in the next video we're going to look at the N plus one problem in Django and we're going to look at how we can solve that using some of the utilities offered by the orm we're also going to look at a tool called Django debug toolbar and how that can help you when you're developing Django applications so thanks again for watching this video if you've enjoyed it please like And subscribe to the channel and we'll see you in the next video

Channel: BugBytes

Views: 7,843

Keywords:

Id: 84BBAGEu064

Length: 39min 38sec (2378 seconds)

Published: Mon Jul 03 2023