# How to efficiently render calculated fields on a table? **URL:** \ **Category:** Forms & APIs\ **Created:** [September 21, 2020, 6:22am UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438 "2020-09-21T06:22:41Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 21, 2020, 6:22am UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/1 "2020-09-21T06:22:41Z")
Hello everyone, me again. I think I have some design problem. I have a **Product** model with some properties: ```py class Product(models.Model): length = DecimalField width = DecimalField height = DecimalField ``` * * * Also I have a **Variable** model acts like a [singleton](https://en.wikipedia.org/wiki/Singleton_pattern), just one register to store variables used in my system. ```py # This model isn't related to Product class Variable(models.Model): var1 = DecimalField var2 = DecimalField var3 = DecimalField var4 = DecimalField ``` * * * For each **Product** , I have 4 model methods to calculate different prices: - def **cost\_price** = length + width + height + var1 - def **sale\_price** = `cost_price` + var2 - def **finished\_price** = cost\_price + var3 - def **sale\_finished\_price** = finished\_price \* var4 ## Problem This are not the exact calculations but you can start to see where the problem is. Calling `sale_finished_price` calls `finished_price` which calls `cost_price` and so on. Also the `vars` count as queries to the DB. The problem arises when I render a table of **Products** , with one of the fields being `sale_finished_price`. This shoots **a lot** of similar queries for making the calculations. Django Debug Toolbar Information ![imagen](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/c/cb4905f0f64d01fdd7aed97dd08b5ea9188d045d.png) ```sql SELECT "variables_variable"."id", "variables_variable"."created", "variables_variable"."modified", "variables_variable"."is_removed", "variables_variable"."precio_hora", "variables_variable"."precio_pintado", "variables_variable"."ganancia_por_mayor", "variables_variable"."ganancia_por_menor", "variables_variable"."ganancia_fibrofacil" FROM "variables_variable" WHERE ("variables_variable"."is_removed" = false AND "variables_variable"."id" = 1) 28 similar queries. Duplicated 28 times. ``` I think I can’t use `select_related()` and `prefetch_related()` in this case, because there are no relations between `Product` and `Variable`. If you read until here, thanks! Hopefully we’ll find some efficient way to do this. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [September 21, 2020, 11:44am UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/2 "2020-09-21T11:44:33Z")
Hmmm… Interesting question! My first reaction is to think about whether or not this is really a problem. Yes, it’s a lot of queries. But assuming you’re using Postgresql as the database, there’s going to be a lot of caching going on along the way. It’s not like the second and subsequent queries are going to need to hit the physical disk. (For example, in the results you show from DDT, it took 73,41 ms, which is less than a tenth of a second. It’s longer than it needs to be, but is it a problem? That’s not for me to say.) It might be helpful to see your model methods to see how you’re calculating these. There’s a chance there might be a better way of doing it without making any significant changes. My next thought would be to prefetch the values in the model methods and use the local variables in the calculations rather than trying to do the calculations at the database layer. Depending upon how stable these variables are across the running of the application, I might also think about loading them into Django’s cache at startup and referencing them from there. Ken ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 21, 2020, 2:01pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/3 "2020-09-21T14:01:08Z")
Thanks for answering Ken! I tend to think that what I’m trying to do it’s already done by someone so, surely there’s a better way to do it than mine. [Here](http://linkode.org/#tM0nsOnQKNCfZiVnkROLk5) is the code from the models. > [@KenWhitesell](#): > > My next thought would be to prefetch the values in the model methods and use the local variables in the calculations rather than trying to do the calculations at the database layer. This is what I wanted to do from the beginning but I did it wrong and you corrected me. > [@Model property using another model field \[Solved\]](https://forum.djangoproject.com/t/model-property-using-another-model-field-solved/3413/6): > > Right - the error is caused because you’re trying to use Variable as a module-level variable. Remove the module-level reference and the error goes away - no need at that point to comment lines. Briefly, yes. it would be perfectly valid to write: def real\_price(self): return self.price \* Variable.objects.get(pk=1).hour\_price \ (Side note: @property is syntactic sugar. In the context of Django, there are very few situations where it’s needed - and this isn’t one of them.) \… * * * Possible solutions: - I never implemented caching because this is a school project but I’d like to implement it if there’s the need. - Another solution would be to refactor the functions to use `Variable.objects...` only one time. (What you’ve said above) - Last but not least, I can exclude this fields from the table, avoiding the calculations in batch. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [September 21, 2020, 2:08pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/4 "2020-09-21T14:08:41Z")
Cool - just one quick question before I offer a suggestion: Do you _always_ need to calculate the four values together, or are there situations where you might need to only call precio\_costo without calculating the other values? ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 21, 2020, 2:25pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/5 "2020-09-21T14:25:39Z")
Not at all. Maybe I need only the last function `precio_venta_terminado`, which is the sale price to the client and the most important to look in a table. I wrote it this way, by “concatenating” functions, as a way to imitate how it works on an spreadsheet. Now I know this is not like a spreadsheet 😅 Just to update, I just tried to cache the view with `@method_decorator(cache_page(60 * 5), name='dispatch')` and the result is great. The downside is it doesn’t invalidate the cache when I modify the `Variable` instance. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [September 21, 2020, 2:33pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/6 "2020-09-21T14:33:09Z")
So the “least invasive” / “least disruptive” solution I came up with looks like this: ```auto class Product(models.Model): largo = DecimalField ancho = DecimalField alto = DecimalField tiempo = PositiveIntegerField def precio_costo(self, vars) -> float: precioBase = (self.largo / 100) * (self.ancho / 100) precioLatCorto = ( (self.ancho / 100) * (self.alto / 100) ) precioLatLargo = ( (self.largo / 100) * (self.alto / 100) ) horas: float = self.tiempo / 60 # Cast to Decimal to be able to multiply it with another Decimal precio_tiempo = Decimal(horas) * vars.precio_hora ### Repeated query return precioBase + precioLatCorto + precioLatLargo + precio_tiempo def precio_venta_crudo(self, vars) -> float: """ Calcula el precio de venta al público del producto crudo """ # Precio costo * % de ganancia return self.precio_costo(vars) * ( (vars.ganancia_por_menor / 100) + 1 ### Repeated query ) def precio_terminado(self, vars) -> float: """ Calcula el precio del producto terminado, sin la ganancia """ tiempo_terminado = (self.tiempo * 2) / 60 precio_tiempo_terminado = ( Decimal(tiempo_terminado) * vars.precio_hora ### Repeated query ) return ( self.precio_costo(vars) + vars.precio_pintado + precio_tiempo_terminado ) def precio_venta_terminado(self) -> float: """ Calcula el precio de venta al público del producto terminado """ vars = Variable.objects.get(pk=1) # Precio terminado * % de ganancia return self.precio_terminado(vars) * ( (vars.ganancia_por_menor / 100) + 1 ### Repeated query ) ``` (I tried to maintain the style and structure of what you’ve already done - I mostly wanted just to illustrate what I was talking about in my previous reply.) This is not a “perfect” solution - there’s no error checking on the `get`, and there’s no way to call the other functions without passing a variable, or no way to use this without the Variable object being present. Just hoping it gives you some ideas. Ken ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 21, 2020, 8:36pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/7 "2020-09-21T20:36:31Z")
As usual, thanks for your answer Ken, it helped reducing the amount of similar queries and optimizing the time of the query. If you have another idea, I’d like to know it, even if it’s _invasive_. This is not being used by anyone so this is the best moment to make these kind of changes. * * * On caching, where should I look into to be able to invalidate the cache on certain model changes? Is this a common problem? I cached a view with `cache_page()` but I’m not finding the way to invalidate it. [Cache Docs](https://docs.djangoproject.com/en/3.0/topics/cache/#django-s-cache-framework) ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [September 21, 2020, 9:00pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/8 "2020-09-21T21:00:11Z")
I would need to ask a couple more questions before I’m able to offer more suggestions: Are these calculated for 1 row at a time, or are you needing to perform these calculations across a number of rows at once? Are the changes to Variable frequent or infrequent? (How often / regularly do they change?) Ken ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 21, 2020, 11:36pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/9 "2020-09-21T23:36:03Z")
At least for now they’re calculated 1 row at a time, one calculation for each product. Normally the changes to Variable could be between 1 to 3 months. With the current state of Argentine economy, the changes could be between weeks or days sadly. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [September 22, 2020, 12:24am UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/10 "2020-09-22T00:24:31Z")
I’m sorry, I wasn’t clear enough for what I was asking. The views that are displaying the results of these calculations - is one view showing one row (a “detail view”), or is a view showing multiple rows (a “list view”), where these calculations need to be performed on each row being displayed in that view? Also, while I’m thinking about it regarding the cache - what I would do for updating the cache would be to hook into the save method of the variable model. Ken ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 22, 2020, 5:36am UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/11 "2020-09-22T05:36:50Z")
I’m using these calculations in a ListView with multiple products, each product making it’s own price calculation with `precio_venta_terminado`. I was thinking and maybe it isn’t very useful to cache a list of values which could change very frequently. And on hooking into the save method, do you reccommend to use the [cache API](https://docs.djangoproject.com/en/3.0/topics/cache/#django.core.caches.cache.delete) provided by Django? Something like `cache.delete('some_key')`. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [September 22, 2020, 9:57pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/12 "2020-09-22T21:57:48Z")
Hi! Really sorry about the delay in getting back to you on this. What you can do is use an [annotation](https://docs.djangoproject.com/en/3.1/ref/models/querysets/#annotate) to perform the calculations across all elements of your queryset. For example, let’s say you’re doing something like this: `qs = Product.objects.all()` to retrieve all Product. You could, then, modify your queryset to do something like this instead: (again, I’m trying to effectively mimic what you have done - any refactoring is up to you) ```auto v = Variable.objects.get(pk=1) qs = Product.objects.all().annotate( precioBase=(F('largo')/100) * (F('ancho')/100), precioLatCorto=(F('ancho')/100) * (F('alto')/100), horas=F('tiempo')/60, precio_tiempo=horas*v.precio_hora, precio_costo=F('precioBase')+F('precioLatCorto')+F('precioLatLargo')+F('precio_tiempo'), precio_venta_crudo=F('precio_costo') * ((v.ganancia_por_menor / 100) + 1), tiempo_terminado=F('tiempo') * 2 / 60, precio_tiempo_terminado=F('tiempo_terminado') * v.precio_hora precio_terminado=F('precio_costo')+v.precio_pintado+v.precio_tiempo_terminado, precio_venta_terminado=F('precio_terminado') * ((v.ganancia_por_menor / 100) + 1) ) ``` When this query executes, you’ll have all the values you’re looking for, plus all the intermediate values available as attributes, on each row. (Note: I typed this live - there’s likely one or more syntax errors.) * * * As a separate topic; I wouldn’t ever delete the key, just rewrite it when it’s updated. (You can set a cache key even if it’s already set - the new value overwrites the old.) But, with the modifications we’ve made in these last two iterations, I really don’t think it’s necessary to bother with the cache on this. Ken ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 23, 2020, 8:48pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/13 "2020-09-23T20:48:17Z")
Hi! No problem for the delay. I tried your approach and it ended being more efficient than my model methods, mainly in the amount of queries. This is the query for one product with all his annotations. ![imagen](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/2/2b4c5dbfbed24d548fb27dfe9c3a2349cc89c9e9.png) Last question: Do you think, in this case, that Qs with annotations is a good **replacement** for the model methods, or just a different way of calculating the same information? Thanks for your time Ken, really appreciated. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [September 23, 2020, 9:49pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/14 "2020-09-23T21:49:18Z")
It’s different, and in part, the differences relate to how/when you’re doing these queries. If you look closely at that generated query, you can see that it’s calculating the same value multiple times. The F clauses creating intermediate results aren’t actually saving those results - it’s effectively defining them as a formula. Later uses of that calculation don’t reuse the earlier results, it’s calculating them again. This means that once you reach some level of complexity, it ceases to be advantageous to calculate values within the context of a query. That you are reducing the total number of queries does not necessarily translate into faster performance, it’s something you would need to evaluate on a case-by-case basis. `` Just as a guess, with nothing in the way of facts to back this up, I’d believe that a streamlined method (doing everything in one function and not split across 4 functions) would be faster than this method of chaining the calculations in the query. I generally limit my use of annotation-based calculations to those situations where I may need to perform calculations like this involving multiple tables through foreign key relationships. I much prefer doing them within my “regular code”. In your specific case, I’d be inclined to consider a different approach. I would move the `get` of the `Variable` row into the View so that the view becomes responsible for the calculations - possibly through a model method, or probably through a manager method. (Not sure exactly, I’m just thinking aloud at the moment.) Again, most of this is likely going to be a judgement call based upon all the different situations in your application you need to perform these calculations. For just one list view, it’s not likely going to make a difference what you choose to do. However, if this table and its contents are the core of this entire application and you’re going to be calculating this thousands of times per day, I might come up with different answers. `` ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [September 24, 2020, 4:03pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/15 "2020-09-24T16:03:00Z")
I considered using a custom manager or refactoring the function to calculate the different prices. Surely it should solve some of the issues I have. Thanks for your help Ken 👍 ---
**Author:** ![cazazo](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/cazazo/32/4916_2.png) [@cazazo](https://forum.djangoproject.com/u/cazazo)\ **Post date:** [June 19, 2021, 12:55am UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/16 "2021-06-19T00:55:15Z")
Hi there, I’m quite new to Djando… Just learning! I would like to know where this example would go? Within the models.py or could be within the views.py? Thank you! ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [June 22, 2021, 12:52pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/17 "2021-06-22T12:52:08Z")
Hi! You can certailnly use it as model methods or properties like in this comment: > [@How to efficiently render calculated fields on a table?](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/6): > > So the “least invasive” / “least disruptive” solution I came up with looks like this: class Product(models.Model): largo = DecimalField ancho = DecimalField alto = DecimalField tiempo = PositiveIntegerField def precio\_costo(self, vars) -\> float: precioBase = (self.largo / 100) \* (self.ancho / 100) precioLatCorto = ( (self.ancho / 100) \* (self.alto / 100) ) precioLatLargo = ( (self.largo / 100) \* (self.alto / 100) … You can also insert it in a view: (I commented it for some reason I cant’ recall) > Where’s best to put it may depend on other factors. ---
**Author:** ![marcorichetta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/marcorichetta/32/26473_2.png) [@marcorichetta](https://forum.djangoproject.com/u/marcorichetta)\ **Post date:** [November 4, 2023, 8:21pm UTC](https://forum.djangoproject.com/t/how-to-efficiently-render-calculated-fields-on-a-table/4438/18 "2023-11-04T20:21:17Z")
I come from the future ?) and [Generated Fields](https://docs.djangoproject.com/en/5.0/ref/models/fields/#django.db.models.GeneratedField) are a thing on Django 5.0! Take a look at this post from Mariusz to see a great example on how to use it. [New goodies in Django 5.0 · Django Beats](https://fly.io/django-beats/new-goodies-in-django-50/)