Is it possible to do arithmetic operation on OuterRef expression?

Question

I'm building an Experience booking system in django and I've the following models.

class Experience(models.Model):
 name = models.CharField(max_length=20)
 capacity = models.IntegerField()

class Booking(models.Model):
 experience = models.ForeignKey(Experience)
 occupied = models.IntegerField()

Each experience has a limited number of capacity and when user perform booking, it will be added to the Booking table with occupied number. Now how will I find the experiences which are not occupied completely?

available_experiences = Experience.objects.all().exclude(id__in=Subquery(Booking.objects.filter(occupied__gt=OuterRef('capacity') - request_persons).values_list('experience', flat=True)))

Here, request_persons is the number of required vacancy in an experience. This is not working and showing an error like 'ResolvedOuterRef' object has no attribute 'relabeled_clone'. Is it possible to do arithmetic operation on OutRef() expression like F()?

Without adding request_persons, the above code works. Why it is not possible to add a value to the OutRef() expression?

NOTE: My actual code is much complex one and it will be really great to get an answer without modifying the entire structure of the above code.

Answer 1

By doing arithmetic operations in the query referenced by OuterRef() directly you can resolve this issue:

available_experiences = Experience.objects.annotate(
    total=models.F('capacity') - request_persons
).exclude(
    id__in=Subquery(Booking.objects.filter(
        occupied__gt=OuterRef('total')
    ).values_list('experience', flat=True))
)

If you found another way without modifying your structure or using RawSQL() or .extra(), let us know!

Answer 2

This seems to be fixed in Django 2.0: https://github.com/django/django/pull/9722/files

The fix can be backported to 1.11.x in a similar fashion:

from django.db.models.expressions import ResolvedOuterRef
if not hasattr(ResolvedOuterRef, 'relabeled_clone'):
    ResolvedOuterRef.relabeled_clone = lambda self, relabels: self