GeneratedField in Django 5 - New Django Model Field!

in this video we're going to look at the generated field that's been added in D Jango version 5 and this is a model field that allows you to create generated columns in a database table and generated columns if you're not familiar with them these are columns that are always computed from the values that are stored in other columns in the table so imagine you have a user model like Jango that has a first name and a last name an example of a generated column would be a full name column and the expression to generate that full name would be to concatenate the first name and the last name together so that full name can be computed or derived from values in other columns in the table we can create this functionality Now using Jango version 5 so let's dive in and see some examples of that in this video and don't forget to give the video a thumbs up if you're enjoying this content so let's start with a look at the Jango documentation on generated field so this is a field that is always computed based on the other fields in the model and the field is managed and updated by the database itself and it uses this generated always SQL syntax and we're going to see an example of that later in the video the important takeaway for a generated field is that it's output is based on the values of other fields in the model and therefore the output is based on the values in other columns in that databased table now there are two kinds of generated columns we have stored and virtual so what's the difference between these a stored generated column is computed when it's written in other words when you insert the data into the table or when you update existing data with new values and as the name implies a stored generated column will occupy storage as if it was a normal column in a table on the other hand we have the concept of a virtual generated column and this occupies no storage and the value for that is computed when the column is red so if you're familiar with relational databases a virtual column is similar to a view and a stored generated column that's similar to a materialized view now the way to control whether or not the generated column will be stored or computed when it's needed is based on this parameter which is dbor persist now there are some caveats so let's scroll down a little bit here for the DB persist parameter it determines if the database column should occupy storage as if it was a real column and if that's false it's going to act as a virtual column but postgresql will only support persisted columns and if you're using Oracle for generated columns that's only going to support virtual columns and the final caveat before we start is that when we update the value by calling the model. saave method because the value of these generated columns is computed by the database and not the python layer of the application if you need to access that generated column you need to call the refresh from DB function and that will reload the object from the database so let's get started and we're going to look at some examples of generated column so let's open vs code and we have a project here that we've been working on in this series and we're going to take a look at one particular model in this project and that was the sale model so let's scroll down here and it's this model here called sale now remember a generated column is based on the values in other columns in the table so let's say we have this income and expenditure column for the sale income represents how much money was taken in during the sale and in this contrived example expenditure represents how much money was spent in order to make that sale now we'll see other practical examples later but what we're going to do is add a generated column here and that column is going to calculate the profit in other words it's going to take the income made and it's going to subtract the expenditure and that's going to give the profit which might actually be a negative number if you've made a loss on the sale now remember that to use a generated field you need to have Jango version five or above and that's because this field type was introduced in Jango version 5 so let's add the field profit and that's going to be equal to a models. generated field now how do we set up a generated field what we need to pass is the expression and this expression is how it's going to calculate the value that it's going to return based on other fields in the model or other data that we pass to the expression so what I'm going to do is pass an expression that uses an F object in jangle to refer to the value on another field in this model so we're going to use the F object and we're going to reference the income and then we're going to subtract the value for the expenditure so again let's use an F object and we're going to copy the expenditure field into this expression so our generated field is called Pro profit and it's going to calculate that profit by taking the income and subtracting the expenditure what we also need to do is set an output field so that jangle or the database will know what the data type we're expecting here is and we're going to set this to a decimal field and I'm actually going to copy the parameters from the two decimal Fields above and we can paste them in here so the output of that subtraction is also going to be a decimal and finally we pass that dbor persist parameter now the database that we using is SQL light and for a generated column that's going to store the value in the database so we're going to set that to true in order to actually create a column in a table for this calculated or generated expression so now that we've added this profit generated field what we're going to do is go to the terminal and we're going to run python manage.py make migrations that creates a migration file and that will add the field profit that we've just created to the sale model once we've done that what we're going to do is run the migrate command and you can see it's applied that new migration and it's going to add the profit column to our database table now after this migration what we're going to do is take a look at the SQL that was used to actually change the database and this is going to use a new jangle management command that we've not used in this series so let's clear the terminal and we're going to run Python manage.py and it's the SQL migr command and we can use this command to look at a migration file and get back as output to the SQL that was used to change the database table so the name of the app is the first parameter and in our project here we have an app called core and if we go to the core application and we go to the migrations directory this contains all of the migrations that we've used in this project the most recent one that added the generated field is this one at the bottom so what I'm going to do is copy the name of that and it starts with 0014 and if you're following along you can copy the name of your migration file and we're going to paste that in here and what we're going to get back here is the SQL that was used to change the underlying table so I'm going to copy this and bring it into a new text file and I'm going to format this so that it looks more clear and that we can see the generated field so I've now reformatted this and we're going to see exactly what happens when we make a change to a database table using ajango migration so what actually happens is that it creates a new empty table and this happens no matter whether you're using generated fields or not we create a new table with the new schema based on the changes that we've made and then after we've created that new table and you can see it's called new _ core sale we insert all of the data from the original table and the original table here was the core sale table into that new table with the new schema and then after the insertion into the new table is complete we drop the original table and we rename the new table to the name of the original table so that's just a bit of background about how jangle performs these migrations and how it actually changes the underlying tables but what we're going to look at now is the profit column that's been added and that has a data type of decimal but we can see now that generated always syntax in SQL and this is what's used to actually create a generated column and what we have here is that we're casting the result of subtracting the expenditure from the income to the numeric type in SQL light and we're storing the result based on those values so that's our generated field our generated column in the database table and we've run the manage.py SQL migrate command in order to inspect this SQL here so I'm now going to close this file and what we're going to do now is query the database based on this new profit field so I'm going to open the script that we've been working on and that's omm script.py and I'm going to remove all of these lines of code and we need to import the sale model at the top here so let's grab the first sale from the database we can use sale doobs do first to do that and then what we're going to do is use a print statement here and we're going to refer to that new profit field on the sale and let's now run the orm script on the terminal and we can see for the given sale The Profit here is 4068 and that should be equal to the sale do income minus the sale. expenditure and when we rerun this we get the same value outputed at the bottom now the difference here is that this value is calculated in the python code so we pull out the values from the database and then we perform the calculation in Python whereas this value here is coming directly from the database because we're now storing it in that generated column now if you're working with a lot of data using a generated column and pulling the data directly that can be a bit more efficient than performing all of the calculations in the python layer so that's just something to keep in mind if you're doing these generated Expressions they can help if you're commonly referring to a calculated or generated value in your code and I want to quickly also look at the database SQL query that's performed when we pull out the sale from the database so let's import connection from django.db and then I'm going to remove this second print statement and we're going to print out con ction do queries and let's rerun the script at the bottom and we're going to see the outputed SQL now because we're using a persisted or a stored generated column all we need to do is use a select statement and refer to our new profit field because it's actually being stored in the database as a new column now if you use the virtual column the expression to compute the value would be performed by the database dynamically so rather than pulling from the column it would directly take the income and subtract the expenditure so let's go back to the script now we're going to show how to perform some calculations and some orderings based on this new generated field so let's say we wanted to find the sale with the maximum profit what I'm going to do at the top is import the max model from django.db Doods and then within the run method I'm going to add a comment so we want to find a sale with the maximum profit in order to do that we can use the order buy method and we can actually order by this new generated field and to get the highest profit first we can use this syntax here and to get the sale with the highest profit after we've done the ordering we can pull out the very first value using the first method so I'm going to remove the print statement for the connection do queries and we're going to go to the terminal here and we're going to run the script again on the terminal and we can see that the maximum profit generated was 83.99 now what we can also do is aggregate by the generated field so I'm going to use the sale doobs do aggregate method and let's call the aggregated value Prof it and for the value itself we're going to use the max model and we're going to pass in the generated field as the parameter to the max model and again I just want to print the results of that to the terminal so let's go back to the terminal and run the script and you can see the output here we performed the order buy and we're getting the very first sale and that's the profit for that sale and on the other hand if we perform an aggregation over the entire database table and find in this case the maximum profit we're getting back the dictionary that contains the decimal and that's the same value as we got above so that's two different ways we can just query the database based on the generated field in order to get back the values we need and we can perform ordering and aggregation and annotation based on these generated Fields now a big benefit of this is that all of the logic behind calculating the generated expression has been moved to the generated field so it's very easy to therefore perform orderings and aggregation based on what is a derived value so this makes our orm queries a lot simpler to right because we've extracted some of that logic to the generated field now let's see another example and we're going to stick to the sale model again so let's go back to models.py now notice that the sale has a foreign key to the restaurant and let's imagine in this system that we have that we are going to create a generated field for the suggested tip for the sale so when you make a sale or you buy food from a restaurant it's good practice let's say to tip the waiters and waitresses so we're going to create a very contrived example here that looks at the income or the value of the sale and uses a generated field to return a suggested tip so let's invent some logic if the income is below $10 we're going to default to zero tip otherwise we're going to suggest 20% of the total income now of course in a relap you might not perform this Logic on the sale model itself but we're going to do it here for Simplicity so let's create a new generated field and I'm going to call this one suggested tip and again that's going to be an instance of models. generated field and the expression for this one is going to be a bit different from above remember we have a conditional expression so if the income is less than $10 we're going to default to a zero value for the tip otherwise we want to return 20% of the value of the income field now we've done a previous video on conditional expressions in the Jango orm that should be appearing on the screen now so check that out if you're unfamiliar with the case and when expressions in jangle at the top here I'm going to import these from django.db Doods so let's import when and case and we're also going to import the value object from the models module now once we've got these Imports let's go back down to our generated field and the expression here is going to be a conditional expression so we're going to use the case object in the Jango orm and we're going to have a condition here so to perform a condition we use the when object and let's say when the income from the sale is greater than or equal to 10 then the value we want to return for the generated field is going to be equal to models. f and we're going to reference the income value on the model and when the income is greater than 10 we want to return a suggested tip of 20% so we're going to multiply that income by 0.2 so that's the conditional Logic for when the income is greater than 10 and we want to return a tip of 20% let's now return a default value here so we use the default argument to the case object and we're going to return zero as our default value now we can use the value object from the jangle orm and this represents a wrapped primitive value as a node within an expression now again we need to specify an output field I'm just going to copy the decimal field above and we're going to paste that in here to the case expression and that tells this conditional expression that we want the output to be a decimal now it turns out that for the generated field itself we also need an output field and again we're going to set that to a decimal field and finally we can use that dbor persist parameter and we're going to set that to true so that's our slightly more complicated generated field that uses a conditional expression what we're going to do now is go back to the terminal and we can run the make migrations command and then after we've created the migrations file we can actually run the migration against the database and that's going to add that new suggested tip column to the database table now let's go back to our script here orm script.py and I'm going to remove these lines of code and we're going to paste in some new code so again we're going to pull out the very first sale from the database and we're going to print the income but I'm also going to print the new generated field so that was sale. suggested tip now let's go back to the terminal and run the script so I'm going to clear the output here and let's run orm script. Pi so the sale income was 85.62 and the suggested tip for that particular sale is 17.12 and that is exactly 20% of the above value so that's returning the correct value now we can find a sale where the income is less than 10 so in order to do that I'm going to use sale. objects. filter and we can look at the income and find examples where the value is less than 10 and let's just get the very first one of those out of the database and hopefully now the suggested tip is going to be zero and that's because of that conditional expression that we added to the generated field so let's rerun the script and you can see at the bottom the sale income was 9.82 but the suggested tip is now zero and again we can go back to models. Pi and the reason for that is because this when expression this evaluates the income when it's is greater than or equal to 10 to return 20% tip otherwise we go to the default which returns a value of zero and that's what's happening below here now let's see one more example of this in action so what I'm going to do is go back to models.py and in fact I'm already in there so let's go to the bottom of this file and I'm going to create a new model here and we're going to call this event that's going to inherit from jango's model class and I'm going to add a couple of fields here so the name is going to be the name of the event and let's say that an event can happen between a start date and an end date so we're going to add two datetime fields to the event class one called start date and one called end date and finally let's just add a thunder string method to this event class as well that's going to return the name of the event now what I'm going to do just before we start with the generated field is make the migrations to add this new model and then we're going to run the migrate command and that's going to create the new database table in SQL light now we're going to create a generated field in a second and what the generated field is going to do is compute the time between the start date and the end date now before we do that we need to have some test events in the database so I'm going to go to the orm script.py file and I'm going to remove all of this code at the top let's import the new event model and I'm going to create a for Loop here so for I in range and let's say between 1 and six that's going to give us back events 1 to 5 inclusive we're going to call event doobs doc create and then we can pass parameters for each of the fields on the the event model so for the name of the event I'm going to use an F string and we're just going to call it event and then we're going to reference the index I and then for the start date I'm going to create an expression here and that's going to use time zone. now and we've got time zone from Django do utils at the top so we're going to take the current time and we're going to subtract a Time zone. Time Delta object and we're going to set the number of days that we're subtracting again to that index I so that's going to give us a start date that's progressively one day further back each time the for Loop runs and finally let's also create an end date and for Simplicity we're going to set that just to time zone. now now after the for Loop runs we want to verify that these new events have been added to the database so we're going to use event. objects. count to get back that value let's now save the file and go back to the terminal I'm going to clear this and we're going to run the orm script and see that we get back five new events now as I said if we go back to models.py for a given event we want to create a generated field that's going to compute the duration between the start date and the end date so let's create a new field here called duration and again it's going to be a models. generated field and we need an expression for that now again we're going to use f objects in jangle so we're going to reference the end date so models. F end date and then we're going to subtract models. F and we're going to reference the start date so in the database table we have an end date and whatever the value of that column is for a given row we're going to subtract the start date from that in order to get back the duration now for the output field here we're going to set that to a field in jangle and that's a duration field and again we'll set the dbor persist parameter to true so that it actually stores the computed value in a database column now as always we've changed the model and we're going to run make migrations now once we've done that we can run the migrate command which is going to add the new column to the database table and finally we can go back to our orm script at the top here and what I'm going to do is just remove all of the code that we used to actually create the objects we know those objects are now in the database so we're going to look over them with a for Loop so for each event in event doobs doall and again that will give us back every event in the database what we can do for each one of those is print out the duration and that's our new generated field that we have in the database table so let's print that out and go back to the terminal I'm going to clear this and we can rerun the orm script and we're going to get back a duration for each event in the database and we can see that we have 1 Day 2 days and up to 5 days and each of these is telling us what is the duration of the event in other words from the start date how many days do we have until the end date and these are nicely rounded on one day and two days and so on we don't have any component here for the actual time period and that's because when we generated the events the start date was time zone. now and then we subtracted a given number of days and the end date was also just time zone do now so the time component is the same but that could be different too if we wanted to get a duration that included that time component so that's a very useful generated property if we have a system working with events that have a start time and an end time and we could even create properties around this generated field as well an example of why we might do that is to get the number of days the event is on for or the number of hours the event is on for in total now if you want to know more about model properties and methods we did a previous video on that and that should be appearing on the screen now what we're going to do is create a property here so we can use the property decorator in Python and let's say we wanted to take this duration and get the number of days that the event is on for so I'm going to create a property called duration in days and that takes self as a parameter from the class and what we're going to do is return a value here and that's self. duration and the duration in Python is going to be a time Delta object and that has a property called days and that's going to give you the number of days in the time Delta so let's now test this new property out if we go back to orm script.py so rather than referencing just the duration field itself we can now reference that property duration in days and if we go back to the terminal and we rerun the script here we get back the value for the number of days and that's just going to be a number it's just an integer so rather than a Time Delta object we've created a property that will return an integer based on the number of days for the duration of the event so that's all for this video we've looked at generated fields in jangle and this is a new model field type that's been introduced in jangle 5 and it allows us to compute a value based on the values in other fields in our model classes and return that to us from the database and we've seen some useful examples but there's a lot more that you can do with the generated field and in future projects involving jangle I'm going to use the generated field as we build out applications in order to show how it can make certain operations a lot more simple when you calculate that kind of value at the database layer so thanks again for watching if you've enjoyed the video give it a thumbs up and subscribe to the channel if you've not already done so and if you find this content useful consider buying a coffee for the channel there's a link in the description and we'll see you in the next video

Channel: BugBytes

Views: 3,499

Keywords:

Id: iTsp-QnlHAQ

Length: 23min 18sec (1398 seconds)

Published: Fri Apr 19 2024