I'm having trouble selecting the first occurrence of the same value with multiple dates. I have the two following models in a logistic app:

class Parcel(models.Model):
    """ Class designed to create parcels. """
    name = models.CharField(max_length=255, blank=True, null=True)
    tracking_number = models.CharField(max_length=255, blank=True, null=True)
    description = models.TextField(blank=True, null=True)

class ParcelStatus(models.Model):
    """ Class designed to create parcel status. """

    SLA_choices = (
        (_('Parcel shipped'), 'Parcel shipped'),
        (_('Delivered'), 'Delivered'),
        (_('In transit'), 'In transit'),
        (_('Exception A'), 'Exception A'),
        (_('Exception B'), 'Exception B'),
        (_('Exception C'), 'Exception C'),
        (_('Other'), 'Other'),
        (_('Claim'), 'Claim'),
    )

    parcel = models.ForeignKey(Parcel, on_delete=models.CASCADE, blank=False, null=True)
    status = models.CharField(max_length=255, blank=True, null=True, choices=SLA_choices)
    event = models.ForeignKey(GridEventTransporter, on_delete=DO_NOTHING, blank=True, null=True)
    reason = models.ForeignKey(GridReasonTransporter, on_delete=DO_NOTHING, blank=True, null=True)
    date = models.DateTimeField(blank=True, null=True)

I'm getting multiple statuses for a parcel. For example:

Inside my class, I'm retrieving parcels such as:

    def get_parcels(self):
        return Parcel.objects.filter(company=self.company)

    def get_parcels_delivered(self):
        parcels = self.get_parcels()
        parcels = parcels.filter(parcelstatus__status='Delivered', parcelstatus__date__date=self.date)
        parcels = parcels.distinct()
        return parcels

My issue is the following: as you can see, I get multiple Delivered status with different dates. I would like to only retrieve the parcel with the date 2022-12-12 15:21. In my app, if I filter with a date form, the parcel would show on the 2022-12-12, 2022-12-15 and 2022-12-22. If I select the 2022-12-15 or 22, I don't want the parcel to be shown. Basically, I only want the parcel to show if the first Delivered occurrence date matches the date I select. Do you have any idea on how I would need to modify the filter?

CodePudding user response：

You can try like this using Subquery:

from django.db.models import OuterRef, Subquery

def get_parcels_delivered(self):
    parcels = self.get_parcels()
    query= ParcelStatus.objects.filter(parcel_id=OuterRef('pk'), status='Delivered').order_by('date')
    parcels = parcels.filter(parcelstatus__status='Delivered').annotate(parcel_delivery_date=Subquery(query.values('date')[:1])).filter(parcel_delivery_date__date=self.date)
    return parcels

Here I am annotating the earliest of the ParcelStatus object's date with the Parcel queryset, then filtering on it.

CodePudding user response：

You could try this:

parcels = parcels.filter(parcelstatus__status='Delivered', 
parcelstatus__date__date=self.date).earliest('date')

According to the documentation it should get the earliest object with the date given by you.
Doc: https://docs.djangoproject.com/en/4.2/ref/models/querysets/#earliest