django can you filter for different value for each object in one query

how to filter for different value for each object and is there is a better way to validate that amount of sales items wont be bigger than purchases items

here is what I think is wrong in SaleItemListSerializer I am making a for loop and for each item I get its medicine and expiry date then finding sum by making 2 subquery and a query so if I have 15 item there will be 15 query on the database the total response time is in average about 0.8 s in sqlite but in mysql it grows to 1.5 ( still i want to add retrived items and disposed items in the supquery so it will grow bigger )

so is there is a way to annotate all medicine that are in items each with its expiry date ( which is stored in sale items and purchase items ) in one database query ?

I think I am making alot of mistakes I am still new to django any help will be appreciated

class SaleAddSerializer(serializers.ModelSerializer):
    items = serializers.ListField(child=serializers.DictField(),write_only=True,min_length=1)
    class Meta:
       model = Sale
       fields = ['doctor_name','coustomer_name','items']

    def validate_items(self,items):     
        seen_items = {}

        for item in items:

            if set(item.keys()) != {'medicine', 'quantity', 'price', 'expiry_date'}:
                raise serializers.ValidationError(_('item should have have medicine quantity and price only'))

            key = (item['medicine'])

            if key in seen_items:

                existing_item = seen_items[key]

                if existing_item['price'] != item['price'] or existing_item['expiry_date'] != item['expiry_date']:
                    raise serializers.ValidationError(_('same item with diffrent price exist or expiry_date exist'))
                existing_item['quantity'] += item['quantity']
            else:
                seen_items[key] = item

        items.clear()
        items.extend(seen_items.values())
        return items
    
    def create(self, validated_data):
        items = validated_data.pop('items')
        pharmacy_id=self.context['pharmacy_pk']
        seller_id=self.context['seller']
        data = validated_data

        with transaction.atomic():
            self.instance = Sale.objects.create(pharmacy_id=pharmacy_id,seller_id=seller_id,**data)
            new_context = {'sale':self.instance,'pharmacy_pk':pharmacy_id}
            item_serializer = SaleItemSerializer(data=items,many=True,context=new_context)
            if not item_serializer.is_valid():
                raise serializers.ValidationError({'error':_('some items are invalid')})
            item_serializer.save()

        return self.instance

the SaleItemSerializer is

class SaleItemSerializer(serializers.ModelSerializer):
    class Meta:
        model = SaleItem
        fields = ['id','medicine','quantity','price','expiry_date']
        list_serializer_class = SaleItemListSerializer

and SaleItemListSerializer for creating multiple items at once

class SaleItemListSerializer(serializers.ListSerializer):
    def create(self, validated_data):
        sale = self.context['sale']
        pharmacy_id = self.context['pharmacy_pk']

        for item in validated_data:
                
                medicine = item['medicine']
                expiry_date = item['expiry_date']

                sales = SaleItem.objects.filter(medicine=OuterRef('pk'),sale__pharmacy_id=pharmacy_id,expiry_date=expiry_date).values('medicine').annotate(amount_sum=Sum('quantity')).values('amount_sum')
                purchase = PurchaseItem.objects.filter(medicine=OuterRef('pk'),purchase__pharmacy_id=pharmacy_id,expiry_date=expiry_date).values('medicine').annotate(amount_sum=Sum('quantity')).values('amount_sum')
                amount = Medicine.objects.annotate(amount=Coalesce(Subquery(purchase),None) - Coalesce(Subquery(sales),0)).values('amount').get(id=medicine.id)['amount']
            
                if amount and amount - item['quantity'] >= 0:
                    if medicine.min_quanity > amount - item['quantity'] >= 0:
                        print('smaller')
                else:
                    raise serializers.ValidationError(f"There is no purchase for medicine {medicine} in pharmacy {pharmacy_id} with this date or not enough amount")

        items = [SaleItem(sale=sale,**item) for item in validated_data]
        return SaleItem.objects.bulk_create(items)

source https://stackoverflow.com/questions/76335524/django-can-you-filter-for-different-value-for-each-object-in-one-query