Max column value of related django model where condition is true

March 16, 2022

TAssignment model has many entries related to TSlot model like for 1 pk of TSlot model there are many entries in TAssignment model.Now this queries outputs values from Tslot table and also latest related created on and updated on from Tassignment table.But what i want is latest value of
‘assignment_Slot__created_on’ and ‘assignment_Slot__updated_on’ when assignment_Slot__is_deleted=False.

QUESTION: How to add "assignment_Slot__is_deleted=False" condition along with ‘assignment_Slot__created_on’ and ‘assignment_Slot__updated_on’ inside annotate without duplicating results.

** assignment_Slot here is related name

TSlot.objects.filter(request__id=request_id, is_deleted=False
                ).values("slot_date", "type_of_work", "reason_for_less_assign", "request_id","slot", "remarks",
                slot_id=F("id"), request_status=F('request__request_status')).annotate(
                assigned_on=Max('assignment_Slot__created_on'), modified_on =Max('assignment_Slot__modified_on'))

>Solution :

Add a filter to your annotations, see filtering on annotations

from django.db.models import Max, Q

TSlot.objects.filter(...).annotate(
    assigned_on=Max('assignment_Slot__created_on', filter=Q(assignment_Slot__is_deleted=False)),
    modified_on=Max('assignment_Slot__modified_on', filter=Q(assignment_Slot__is_deleted=False))
)