Django ORM: Get maximum value of a field with corresponding other fields values

Django ORM: Get maximum value of a field with corresponding other fields values.

I have this Table (Counters): enter image description here

I want to get the specific date_time value of the maximum traffic (which is calculated based on the the fields tftralacc, tfnscan, thtralacc and thnscan) for every cell_id.

I've managed to get this maximum value for every cell_id by using the annotate() and group_by() functions of the Django's QuerySet API:

result = Counters.objects.filter(
    date_time__gte = date_start,
    date_time__lte = date_end
).annotate(
    # calculate the traffic for each row.
    traffic = Case(
        When(Q(tfnscan=0) or Q(thnscan=0), then=0),
        default = Round((F('tftralacc')*1.0/F('tfnscan')) + 
                        (F('thtralacc')*1.0/F('thnscan')), 2),
        output_field=FloatField()
    )
).order_by('cell_id').values(
    # Group by cell_id.
    'cell_id'
).order_by().annotate(
    # calculate the max traffic for the grouped Cells.
    max_traffic = Max('traffic')
)

The calculated traffic for every date_time is demonstrated here: enter image description here

My code successfully returns the maximum traffic for every cell_id:

cell_id max_traffic
13997 3.92
14000 4.59

But my goal is to get the Corresponding date_time value for every max value. like this:

cell_id max_traffic date_time
13997 3.92 2022-02-22 11:00:00+01
14000 4.59 2022-02-22 10:00:00+01

or

cell_id date_time
13997 2022-02-22 11:00:00+01
14000 2022-02-22 10:00:00+01

Because that max value is just a mean to get the date_time and not the goal.

Note: There is this question that describes my problem, but its answer refers to a work-around solution, which is not possible with my problem. SO Question

Use models.Subquery with models.OuterRef to join on cell_id and traffic fields and use queryset.values() to select only date_time column.

counters_with_traffic = Counters.objects.filter(
    date_time__gte = date_start,
    date_time__lte = date_end
).annotate(
    # calculate the traffic for each row.
    traffic = Case(
        When(Q(tfnscan=0) or Q(thnscan=0), then=0),
        default = Round((F('tftralacc')*1.0/F('tfnscan')) + 
                        (F('thtralacc')*1.0/F('thnscan')), 2),
        output_field=FloatField()
    )
)

counters_date_time = counters_with_traffic.filter(
    cell_id=models.OuterRef('cell_id'),
    traffic=modesl.OuterRef('max_traffic'),
).values('date_time')

result = counters_with_traffic.order_by('cell_id').values(
    # Group by cell_id.
    'cell_id'
).order_by().annotate(
    # calculate the max traffic for the grouped Cells.
    max_traffic = Max('traffic'),
    date_time=models.Subquery(counters_date_time),    
)
Back to Top