Annotate Queryset with logarithm of field value in Django

user_4511620 0
Viewed 280

I've researched available django aggregation functions and noticed that logarithm is missing there. So, my question is: how can I use logarithm in annotating the Queryset? I cannot take logarithm after Queryset evaluation, because I need to annotate not exactly with logarithm, but rather with an expression containing it, for example for some models User and Task I need to annotate User with F('task__cost') / Log('task__solved_count').

UPD: It'd be great if I could do it without using database-specific functions (for Postgres), but that solution is possible too.

annotations orm django python database
1 Answers

Django has these functions, these have been added in pull request 9622 [GitHub]. In the development branch, these already exist under django.db.models.functions.math module. But not in the django-2.1 release. A page is available in the Django dev documentation that lists the source code.

It turns out that this function is on most popular database systems the same [Django ticket]. You can add the source code [GitHub]:

from django.db.models import (
    DecimalField, FloatField, Func, IntegerField, Transform,
)
from django.db.models.functions import Cast

# ...

class DecimalInputMixin:

    def as_postgresql(self, compiler, connection, **extra_context):
        # Cast FloatField to DecimalField as PostgreSQL doesn't support the
        # following function signatures:
        # - LOG(double, double)
        # - MOD(double, double)
        output_field = DecimalField(decimal_places=sys.float_info.dig, max_digits=1000)
        clone = self.copy()
        clone.set_source_expressions([
            Cast(expression, output_field) if isinstance(expression.output_field, FloatField)
            else expression for expression in self.get_source_expressions()
        ])
        return clone.as_sql(compiler, connection, **extra_context)

class OutputFieldMixin:

    def _resolve_output_field(self):
        has_decimals = any(isinstance(s.output_field, DecimalField) for s in self.get_source_expressions())
        return DecimalField() if has_decimals else FloatField()

# ...

class Log(DecimalInputMixin, OutputFieldMixin, Func):
    function = 'LOG'
    arity = 2

    def as_sqlite(self, compiler, connection, **extra_context):
        if not getattr(connection.ops, 'spatialite', False):
            return self.as_sql(compiler, connection)
        # This function is usually Log(b, x) returning the logarithm of x to
        # the base b, but on SpatiaLite it's Log(x, b).
        clone = self.copy()
        clone.set_source_expressions(self.get_source_expressions()[::-1])
        return clone.as_sql(compiler, connection, **extra_context)

and then import your defined Log function, and use it like:

User.objects.annotate(cost_solve_ratio=F('task__cost') / Log('task__solved_count'))
user_675790
Related