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!
Use the
Tan
database function