Annotations as models fields

[Olivier Dalang]

Dear list,

I was wondering whether there's a package or pattern to define annotations as model fields, so that they could really be used as database-side computed fields.

Currently, I do something like this (not tested, it's a simplified case):

class SalesManager(models.Manager):

def get_queryset(self):

return super().get_queryset().annotate(

profit=F("selling_price")-F("buying_price")

)

class Sales(models.Model):

objects = SalesManager()

buying_price = models.FloatField()

selling_price = models.FloatField()

...

Ideally, I'd see something like :

class Sales(models.Model):

buying_price = models.FloatField()

selling_price = models.FloatField()

profit = models.AnnotationField(F("selling_price")-F("buying_price"))

...

An AnnotationField would behave like a read-only field, excepted that it wouldn't have a database column.

This would have the following benefits :

- much less verbose

- all instance related stuff in one place

- it would make it possible to refer to "profit" as a model field

    - you could use the field in Meta.ordering

    - you could easily display in django.admin without additional methods

Of course similar result can be achieved with @property and a python method, but I really need this to be on the database side to use subqueries, sorting and filtering, etc.  

Does anything like this exist ? Is it possible to emulate this behaviour somehow ?

Thanks !!

Olivier

[Chetan Ganji]

I had a similar need in one of my clients project, I ended up using @property and a python method. 

AFAIK, AnnotationField is absent from django models. If you would like to write one, below is the material you need to refer.

https://docs.djangoproject.com/en/2.2/howto/custom-model-fields/

Regards,

Chetan Ganji

+91-900-483-4183

ganji....@gmail.com

http://ryucoder.in

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAExk7p3eXvCUCxB3LgZJHTrVXzLuD%3DNqUE79a-dqtUXzekXgJg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Olivier Dalang]

Thanks for the answer. As said, the @property + python method wouldn't work for my use case, as I need to be able to use the field in the queryset to filter/order (plus my actual computed field use aggregates expressions).

Cheers,

Olivier

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAMKMUjscehKiqt4Gcu3Cnm%2BHKhH8Y98RnGhR6chVZ7NS5qc-sw%40mail.gmail.com.

[Chetan Ganji]

I think this would solve your problem. Whatever calculations needs to be done, you can do it manually before saving the Sales instance. 

Preferably after checking if form.is_valid() in case of django and in the function perform_create() in case of django rest framework.

class Sales(models.Model):

buying_price = models.FloatField()

selling_price = models.FloatField()

profit = models.models.FloatField()

Cheers! 

Regards,

Chetan Ganji

+91-900-483-4183

ganji....@gmail.com

http://ryucoder.in

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAExk7p1PBMC7rR4iBu_80pwTBoie4NydB9gk0jKAqOqNwCGhZQ%40mail.gmail.com.

[Olivier Dalang]

Thanks Chetan for trying to help, but please read carefully the issue, as you didn't understand what I'm trying to do.

In the meantime, and for the record, I found this, which works for simple cases.  https://github.com/schinckel/django-computed-field  

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAMKMUjtqs1a9t7XKoDQcPxZm1CrC7eqyD7jCZSDG12-KKyd0HA%40mail.gmail.com.

[Chetan Ganji]

Yes Olivier, You are right :P 

If I understand the problem correctly this time, you want the field available on django model, but does not want the python to do the calculation. 

You want the database to compute the value.

If yes, you could use a row level trigger i.e after create and after update trigger. PostgreSQL supports it. 

class Sales(models.Model):

buying_price = models.FloatField()

selling_price = models.FloatField()

profit = models.models.FloatField(null=True, blank=True)

For your reference - 

RE: SQL Triggers

http://www.postgresqltutorial.com/introduction-postgresql-trigger/

RE: database-side computed fields

Instead of concatenation as in the below example, you would do some other operation and/or aggregation which would be calculated by the database. 

https://dba.stackexchange.com/questions/21897/set-a-columns-default-value-to-the-concatenation-of-two-other-columns-values

If it works, as it would be on database level, you have to document it in the readme file of your project. 

Otherwise, next developer on the project might not know about the trigger and bang his head on the floor like I did earlier, lolz :P

I hope it helps this time :P 

Regards,

Chetan Ganji

+91-900-483-4183

ganji....@gmail.com

http://ryucoder.in

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAExk7p1ddVqskXV%3DKQXtri5cLTwJutzFeZDz5aeDanVGTcKHtQ%40mail.gmail.com.

[Devender Kumar]

Hi,

You can use Django annotation feature which will add a field to your queryset objects and that field can behve like any real field 

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAMKMUjsi5JE2jSp5_p_LyHm5V1P%3DBHyH6V%2BCN37HsEoK3sr5gw%40mail.gmail.com.