Django - COALESCE Function and Handling NULL Values in the Database

in this video we're going to look at how we can add nullable columns to a database table using djangles model classes and we're also going to see how we can handle filtering null values when we're performing queries to the database we'll also see how null values affect ordering when we're getting back records and finally we'll look at the coalesce function in databases to see how we can use that to provide a default value when there is a null value so let's get started I have Visual Studio code open here and what we're going to do to start with is we're going to look at the restaurant model that we've been working with throughout this series and I'm going to add a new field to this model we already have these fields here but I'm going to add a new one and we're going to make that a nullable field in the database so let's say that for a restaurant we want to store the capacity of that restaurant that's the number of people that can be in the restaurant the maximum number at a given time so we want to store that information in the database but a restaurant might not necessarily know what its capacity is so we want to make this a nullable value what I'm going to do underneath the existing Fields is create a new field here which we'll call capacity and this is going to store the number of people that can be in the restaurant at a given time so we're going to make this a positive small integer field and what we can do and we instantiate that is we can pass some keyword arguments that are going to modify how the underlying column is created so what I'm going to do in order to make this nullable in the database is past the null equals true keyword argument in here and I'm also going to pass a blank equals true argument so that we can accept blank values when we submit a form in Django that's not going to be applicable to this video but that's how you would do that the important thing to make the database column nullable is to pass the null equals true keyboard argument and again from a data modeling perspective the reason we make this nullable is because a restaurant might not necessarily know what their capacity is so we want to give them the option of not passing a value in there and making that null so now that we've changed that column or added a new column rather what I'm going to do is run the pi python manage.pi make migrations command and that's going to create the migration file for that change and then once we've done that we can run the migrate command now I have the database browser for SQL Lite open here so what we're going to do is go to the restaurant table where we've added that new column and if we click modify table we're taken to this window here now you can see all the columns that are in this table if we go down we can see the capacity column and that is the only column that has the not null constraint unchecked so this is the only column that we're allowing null values to be entered into so we've added that nullable column let's now have a look at the existing records in the restaurant table if we look at those records you can see that for the capacity column every single one of those records is null and that's because this is existing data we've just added a new column and we've said that the value can be null in that column and that is going to make all existing rows have a null value in the capacity column so let's go back to visual studio code and we're going to now query this column using Django we're going to go to the ormscript.pi file and we're going to start writing some queries in here so what I'm going to do is print the results of our query and the query is going to be restaurant.objects.filter and what we want to do is get back all the restaurants whose capacity value is null so in order to do that we can use our lookup and Django we specify the capacity field and then two underscores and the lookup is called is null so if we want to get back all the rows where the capacity is null we can set that equal to true and we can execute the query and get back the results of that so let's clear the terminal and we're going to run the Run script command here so python manage.pi run script and the name of the script is ormscript.pi once we run that we get back all of the rows in the table where the capacity column has a null value and as we just saw in SQL Lite browser here every single row that we have has a null value at the moment so that's giving us back every single Row in the table if we change the true to 4 balls here and we re-execute this query we expect to see an empty query set and that's what we get below and just as a quick point I'm going to uncomment connection dot queries and rerun this script so that we can see the SQL that is being executed and you can see at the bottom here in fact let me make this a little bit bigger the query has a where Clause where we're looking at the capacity column and we're checking if that is not null using this statement here so we're checking if the capacity is not null and that is important we're not checking if the capacity is equal to null and that's because no two nodes are the same in an SQL database so we use the is not operators here in order to make that check and of course if we change the false back to True here for the is no lookup the S note will change to Simply checking if it is null so let's now go back to our script and we're going to pull out two restaurants from the database so I'm pulling out restaurants here using the first and last functions and what we're going to do is we're going to set the capacity field for both of these so let's set the first one to a capacity of 10 and let's set restaurant 2's capacity by setting that equal to in this case let's say 20. once we've set the capacity we need to call the save function for both of these restaurants so that's going to set that in the database and this time when we query for the restaurants and look at the restaurants whose capacity is not now we are going to get back the first and last restaurants that we've set here to 10 and 20 respectively so let's clear the terminal and rerun the orm script and in fact I'm going to comment out connection.queries and we can rerun this and this time our query set contains the two restaurants whose capacity we have set here and therefore that is how the isnal lookup is working it's looking for any restaurant whose capacity is not null and that is now going to be these two here so let me just delete this code here and what we can also do of course is chain another function here for example the dot count function so this will give us back the number of restaurants whose capacity is a not null value and that gives us back two at the bottom here so that's the basics of how to query a database table using a Django model and get back all of the rows where a particular column is null or a particular column is not null what we're going to do now is go back to the Django documentation and we have this page here on the model field reference I'll link this below the video what we're going to look at is the field options here and we have the field.null option that we saw when we added that capacity field to the restaurant model and as it says if you set null equals true Django will store empty values as null in the database but I want to raise this caveat below here where we are told to avoid using null on string based Fields such as a car field and a text field and that's because if you set null equals true on a string field that means that there are two possible values for no data you have the null value and you also have the empty string and in most cases that is redundant so you want to avoid setting null equals true on these text based fields in Django that's just a caveat Dimension when you're creating nullable fields on your models and if we go back to that restaurant model we have this website field here that we've not really used in this series but you can see that that's a URL field which is a subclass of the car field in Django it's a text based field and rather than storing null values by setting null equals true like we did down here in the capacity field what we've done with the website is we've set a default equal to an empty string so that means if we're trying to create an instance of a restaurant and we don't pass in a website it's going to default to that empty string and that is what represents a missing website from this model let's now move on to see how Django handles null values when it's doing ordering now in order to demonstrate this let's go back to the script and I'm going to remove the filter function and we're going to replace that with the order by function in the Django orm and we're going to order the restaurants by that new capacity field and once we've got that ordering we're going to use the dot val obvious function and we're only going to get back the values of the capacity field from the resulting query set so at the bottom I'm going to execute the script and we're going to see the output of that script and you can see the ordering here when we ordered by the capacity field we have the null values at the start these are equal to the non-value in Python but basically what's happening is that it's pulling out the non-values or the null values at the start of the query set and you can see at the end of the query set we have the capacitive fields that actually have a value so the rows with the null values come at the start when we use the order by function with a nullable field in Django as we're doing here and on the other hand the non-null values they come at the end of the query set when we are performing this ordering so Django treats the null values essentially as less than the non-null values that might not be desirable so there are different ways of handling this for example you might want the null values at the end of the resulting query set so let's go to a section of the Django documentation and and that's using the F object to sort out null values what we can do is to the order by function we can pass an F expression with the field in question and then we have the dot descending and Dot ascending function so that we can change to that and there is our keyword argument that we can pass in there called nulls last and we can set that to true or false in order to put the null values at the start of the query set or at the end of the query set so let's see an example of this with our model here what we're going to do is we're going to import the F expression at the top from django.db.models and then rather than referring to the field what we're going to do is we're going to create an F object here and we're going to refer to that capacity field and that if object we can use the dot ascending function here and we can pass that nulls last parameter and set that equal to true in order to move the null values to the end of the query set so let's execute this function and we're going to see the output of this script this time we have the two objects that have a capacitor that's not null at the start of the query set and those are ordered in ascending order as per this function here so we have 10 before 20 and then after those two values we have all of the capacities that are null and that's because now we have the nulls last keyword argument set to true in this dot ascending function and if we want to get this in reverse order what we can do for example is use the dot descending function and re-execute the script and now we're going to see 20 at the start and the capacity of 10 is now the second object and as before we have the null values at the end of the query set now if you're ordering and you don't want to deal with null values there is of course another way to deal with this so what I'm going to do is change the code back to what we had before and that was this statement here where we are ordering by capacity but we don't want to consider null values what we can do of course is use the dot filter function and we can again use that capacity as null lookup and we can set this look up to false in order to only return the rest restaurants whose capacity is not null and then when we have those we can safely order by the capacity and we don't need to worry about where null values will fall into the resulting query set so let's re-execute the script at the bottom and we can see the output this time we have the capacity of 10 and 20 and all of the other values have been filtered out of the query set and again if we want this in reverse order we can use this syntax here instead of the dot descending and Dot ascending functions because we don't need to worry about where the nulls will fall in that query set so if we re-execute this this time we get back the higher value first because this is now in descending order let's now move on to using the coalesce function in Django so what I'm going to do is go back to Django's documentation and we're going to go to the page on database functions which I'll link below the video now we've seen a few functions in this series so far this time we're going to look at the coalesce function in Django and this function accepts a list of at least two field names or expressions and that will return the first non-null value from that list so what we're going to do is copy this import statement here and we're going to bring that into vs code at the top of our script so we've imported the coalesce function and I'm going to remove this statement here and what we're going to do just to demonstrate this example before we run the coalesce function I'm going to run the restaurant.objects.update function and we're going to update every single restaurant in the database and set the capacity back to null for every single row so let's save that file and we're going to execute that query and that's going to set the capacity column to null for every restaurant in our database now let's imagine that we wanted to take the sum of the capacities in our database for all restaurants the problem with taking that sum over the capacity column is that every single row has a null value in that column so how do you take the sum of a column that contains null values let's see an example of that now if we go back to our script here I'm going to run the restaurant.objects. thought aggregate function and what we're going to try and aggregate here is the sum of the capacity column so let's create a key here called total capacity and we're going to set that equal to the sum function over the capacity column and we need to import the sum function from django.db.models and we're going to print the output of this aggregate call and we're going to see what happens when we try and sum up the values in this column so let's execute the script and you can see that we get a total capacity equal to none now what happens if we need to return a number here we don't want to return none under any circumstances even though there might be no values in this column we want to guarantee that when we call the sum aggregation function that we get back a number so what we can do or rather one way of solving this is to use the coalesce function so when we use the aggregate function in Django we're passing the sum in here but what we can actually do is pass the coalesce function and I'm going to instantiate that and we pass the sum of the capacity column as the first value and if that evaluates to know we can pass in a second expression here so after the sum of the capacity we're going to set that equal to zero and we can close off the coalesce function so what's going to happen here is going to take each of these values it's going to look firstly at the capacity column try and sum that up using Django's sum function and if that returns a value of null it's then going to move on to the second argument here which is just the row value of 0. so let's save this file and we can execute the script and see what output we get now and instead of null what we're getting back here is the number zero so the coalesce function can be used here with the aggregate function to make sure that an aggregation does not return none and coalesce can also help with our filter functions as well so what I'm going to do is remove this code and we're going to actually use the rating model here it's going to be rating.objects.filter and we know that all ratings have a rating value that's between 1 and 5. so I'm going to add a rating here that will return an empty query set we're going to look for all ratings that are less than zero and if we call Dot aggregate after that and this time we're gonna try and get the average rating so we're gonna use the average function from Django and we can pass that rating field in there and again we need to import average at the top here so what we're doing in this statement here is we're filtering down to all ratings that are less than zero and that will return an empty query set which we're then calling dot Aggregate and we're passing in the average aggregation here over the rating field now when you try and aggregate over an empty query set what's going to happen is it's going to return none for that particular aggregation as you can see below now again in the case where you need to have a non-null value coming back from this function we can use the coalesce function here and pass in a second argument if the first one evaluates the null and in this case I'm going to pass in 0.0 and let's execute this and see what we get back and this time we get back 0.0 here and we're not getting back none and that can be important if you want to guarantee again the you're not going to have a null value in your code coalesce gives you this fallback that you can use to provide a default value or a secondary value if the initial value evaluates To None now there is another way to handle this issue in Django if we're using aggregation functions such as average I'm going to go back to the way the code was before we used coreless and what we can actually do is pass a default in here to the average aggregation function so we can set the default equal to 0.0 and when we rerun this script at the bottom this time we get that back and we don't need to use the coalesce function in this case so that's quite an important thing to note about Django aggregation functions you can specify a default value if you're potentially going to get back a non-value now when we do this default equal to 0.0 if we uncomment connection dot queries and look at the resulting query that's being issued against the database you can see that under the hood Django will actually use the database coalesce function in order to perform this query and the coalesce function and a database like postgres or MySQL is going to work very similar to the Django function it takes in the first expression here and if that evaluates to null it's going to then use the second expression and we can pass in as many of these Expressions as we want until we finally get back a value that is not null so to finish this video we're going to create a toy example just to demonstrate something useful that the coalesce function can help us with let's go back to models.pi and I'm going to create another new field on the restaurant model and this is going to be a nullable field that is called nickname now let's imagine that restaurants can have a nickname that might not make a lot of sense but let's just go with it for this example and that's our car field it has a max length of 200 characters and it's also got the null equals true flag let's run the make migrations command at the bottom and then we can run the migrate command to actually make that change to the database and if we go back to the restaurant table and refresh this table you can see that we now have a nickname column at the right hand side and all values are null now let's say that we want to pull out a name for every single restaurant in this table and we're going to try and get the nickname if it exists and then we will use that if it does exist but if it doesn't exist what we're going to do is fall back to the name column that we have here coalesce can help us with this let's go back to vs code and go back to the script and I'm going to remove this line of code here and comment out connection.queries now the function that we're going to write here it's restaurant.objects.annotate and we're going to annotate each restaurant with a new field called name value and I'm going to bring this code to a new line so that we can see that clearly what we're going to do here so what the name value is going to do is use the coalesce function and what we're going to do is use f expressions and the first argument to coalesce is the one we want to check first and see if that evaluates to null or not and the field we're going to use there is the nickname field and if that's not null it's going to return that nickname but if it is now we can provide a second value here and we're just going to use the normal name field that we have on that particular model and once we've done that we can use the dot values function and we're going to get back that annotated field called name value when we print this out to the terminal so let's clear the terminal and we're going to execute this script and you can see the name values that we're getting back we aren't getting nulls here as we would if we just checked for the nickname which we know is entirely null in our table what we're getting back is the fallback which is the name column now what I want to do is set one restaurant's nickname to a non-null value so let's paste in some code here we're getting the first restaurant from the database and we're setting its nickname to some random nickname here and then we're calling the dot save function so let's do that and then we are going to rerun this script with that new code added and you can see the output at the bottom and for the very first record that's coming back in that query set you can see that the name value is set to the nickname that we just I did there and that's because when we add that nickname this statement here with the coalesce function the nickname will evaluate to non-null and that will be returned for that particular record whereas every other record does not have a nickname so that will fall back to the second field here which is the name field so the coalesce function can be handy if you have some data that is kind of matched up and you want to return one particular value but if that doesn't exist you want to fall back to a second value very useful and it avoids you writing that if else logic in your code you can just use coalesce and the database will handle that logic for you behind the scenes so that's all for this video we've covered how to add nullable fields to the database using the null equals true keyword argument and we've also seen how to use djangles as null lookup and I'll leave a link to that one below the video as well and this can be used with the filter and exclude functions to get back the rows in our table where we filter out or filter in the rows that have null values and a particular column and we also saw how null values affect ordering in Django and how we can get around the problems that might occur with that and finally we have looked at the coalesce function that we can use to provide a fallback if a particular expression evaluates the null and coalesce is a database function that we Import in Django but the actual calculation the actual logic is performed at the database level and that can help simplify our code in these circumstances so thank you very much for watching this video if you've enjoyed the video please like And subscribe to the channel and if you want to support the channel there is a link to a coffee page in the description thanks again for watching and we'll see you in the next video

Channel: BugBytes

Views: 2,429

Keywords:

Id: i5t5ONN1zY4

Length: 21min 46sec (1306 seconds)

Published: Fri Sep 15 2023