Django Query for current stock list from operations table field with IN, OUT value and field with date and time

Django Query for below case to find list of available stock based on ops_type=IN entry with date and time and ops_type=OUT entry with date and time.

date and time field are separate because the data is uploaded from sheet and needs to be in same format to process.

Operations model is as follows

class Operations(BaseModel):
    class OPS(IntEnum):
        IN = 0
        OUT = 1

        @classmethod
        def choices(cls):
            return [(key.value, key.name) for key in cls]

    date = models.DateField()
    time = models.TimeField(default=None, null=True)
    ops_type = models.IntegerField(choices=OPS.choices())
    serial = models.CharField(max_length=255, default=None, null=True, db_index=True)
    product = models.CharField(max_length=255, default=None)

Error: Cannot resolve keyword 'latest_out_datetime' into field. Join on 'serial' not permitted.

from django.db.models import F, Q, Subquery, OuterRef, ExpressionWrapper, DateTimeField
from datetime import datetime

in_entries = Operations.objects.filter(ops_type=Operations.OPS.IN.value)

out_entries = Operations.objects.filter(
    ops_type=Operations.OPS.OUT.value,
    serial=OuterRef('serial')
).values('serial').annotate(
    latest_out_datetime=Subquery(
        Operations.objects.filter(
            ops_type=Operations.OPS.OUT.value,
            serial=OuterRef('serial')
        ).annotate(
            datetime=ExpressionWrapper(F('date') + F('time'), output_field=DateTimeField())
        ).order_by('-datetime').values('datetime')[:1]
    )
)

available_stock = in_entries.annotate(
    in_datetime=ExpressionWrapper(F('date') + F('time'), output_field=DateTimeField()),
    latest_out_datetime=F('serial__latest_out_datetime')
).filter(
    Q(latest_out_datetime__isnull=True) | Q(in_datetime__gt=F('latest_out_datetime'))
)

stock_list = [stock.serial for stock in available_stock]

print("List of available stock:", stock_list)

Some of the code here does not make sense. For example why do you have two OuterRef in out_entries? Why it is not wrapped with Subquery?

I think you are making the implementation overcomplicated here. I think you can simply try like this:

from django.db.models import Q, Max, F

in_entries = Operations.objects.annotate(
    datetime=ExpressionWrapper(
        F('date') + F('time'),
        output_field=DateTimeField()
    )
   .values('serial')
   .annotate(
        max_out_date=Max(
            'datetime',
            filter=Q(
                serial=F('serial'),
                ops_type=Operations.OPS.OUT.value
            )
        )
    ).filter(ops_type=Operations.OPS.IN.value).values('serial', 'max_out_date')

For more information, please see the Django aggregation cheat sheet in the documentation.