How to order queryset by foreign key field without duplicates?

March 14, 2022

I have Django models Ticket and Confirmations:

class Ticket(Model):
    name = models.TextField()


class Confirmation(Model):
    ticket = models.ForeignKey(
       "ticket.Ticket",
       related_name="confirmations",
    )
    expire_date = models.DateTimeField()

I want to order them by expire date of confirmations, and it works but if ticket has more than one confirmation then it will be returned in queryset multiple times:

tickets = Ticket.objects.order_by('confirmations__expire_date')
for ticket in tickets:
   print(f"id: {ticket.id}")
>
id: 1
id: 2
id: 1
id: 3
id: 4
id: 1

I don’t want to return duplicates. I just need the first element and get rid of the rest. I need to take into account the latest confirmation in response.

>Solution :

You should work with the largest confirmation date, so:

from django.db.models import Max

tickets = Ticket.objects.alias(
    latest_confirmation=Max('confirmations__expire_date')
).order_by('latest_confirmation')