Django Query Annotation Does Not Support math.tan() Calculation

99 Views Asked by At

I have a django 3.2/mysql website. One of the tables, Flights, has two columns, angle and baseline. These two columns are combined in a view to display the value altitude using the formula altitude = baseline * tan(angle). I am building a search form for this table, and the user wants to search for flights using a minimum altitude and a maximum altitude.

My normal approach to the problem is to gather the inputs from the search form, create appropriate Q statements, and use query the table with filters based on the Q statements. However, since there is no altitude field, I have to annotate the query with the altitude value for the altitude Q statement to work.

It seems that a query annotation cannot use an expression like math.tan(field_name). For example,

Flights.objects.annotate(altitude_m=ExpressionWrapper(expression=(F('baseline') * math.tan(F("angle"))), output_field = models.FloatField(),),)

generates the error builtins.TypeError: must be real number, not F

whereas the expression

Flights.objects.annotate(altitude_m=ExpressionWrapper(expression=(F('baseline') * F("angle")), output_field = models.FloatField(),),)

does not generate an error, but also does not provide the correct value.

Is there a way to annotate a query on the Flights table with the altitude value so I can add a filter like Q(altitude__gte= min_altitude) to the query? Or, is the best approach to (1) go back to the existing ~1,000 rows in the Flights table and add a column altitude and insert the calculated value, or (2) filter the Flights query on the other search criteria and in python calculate the altitude for each Flights object in the filtered queryset and discard those that don't meet the altitude search criteria?

Note: I have simplified the problem description somewhat by only describing a single altitude value. There are actually 2 altitude values, one in meters and one in feet.

Thanks!

1

There are 1 best solutions below

6
On

Use the Tan database function

from django.db.models.functions import Tan

Flights.objects.annotate(
    altitude=ExpressionWrapper(expression=F('baseline') * Tan('angle'), output_field=models.FloatField())
)