[Python] How to efficiently exclude already assigned objects in a Django QuerySet?

I am working on a Django project where I need to filter objects based on their status while excluding those that are already assigned in another model.

I have two models:

1. CartObject – Stores all objects.
2. OnGoingProcess – Tracks objects that are currently assigned.

Each OnGoingProcess entry has a OneToOneField relationship with CartObject, meaning each object can only be assigned once.

My goal is to fetch all objects with a specific status but exclude those that are already assigned in OnGoingProcess.

Models:


class CartObject(models.Model):
object_id = models.CharField(max_length=100, unique=True)
status = models.CharField(max_length=50, choices=[("pending", "Pending")])
# Other fields...

class OnGoingProcess(models.Model):
user = models.OneToOneField(DeliveryProfile, on_delete=models.CASCADE, related_name="ongoing_process")
associated_object = models.OneToOneField(CartObject, on_delete=models.CASCADE, related_name="associated_process", blank=True, null=True)
# Other fields...

Current View Code:


@user_passes_test(lambda user: user.is_staff)
def process_manager_view(request):
# Get objects that are already assigned in OnGoingProcess
assigned_objects = OnGoingProcess.objects.values_list('associated_object', flat=True)

# Exclude objects that are already assigned
available_objects = CartObject.objects.filter(status="pending").exclude(id__in=assigned_objects).order_by("-id")

context = {
"available_objects": available_objects,
}
return render(request, "useradmin/available-objects.html", context)

Issue:

1. I am using values_list('associated_object', flat=True) to extract the assigned object IDs.
2. Then, I am using exclude(id__in=assigned_objects) to filter out those objects.
3. Is this the most efficient way? Or is there a better Django ORM method to achieve the same result?
4. Should I use Subquery(), isnull=False, or any other approach for better performance?

Alternative Solutions I Considered:

Option 1: Using isnull=False


available_objects = CartObject.objects.filter(status="pending").exclude(associated_process__isnull=False)


Pros: Simple, avoids extra queries.
Cons: Not sure if it's the best approach for performance.

Option 2: Using Subquery


from django.db.models import Subquery

assigned_objects = OnGoingProcess.objects.values('associated_object')
available_objects = CartObject.objects.filter(status="pending").exclude(id__in=Subquery(assigned_objects))


Pros: Optimized for large datasets.
Cons: More complex.

Option 3: Using Raw SQL (if necessary)


from django.db import connection

with connection.cursor() as cursor:
cursor.execute("""
SELECT id FROM useradmin_cartobject
WHERE status='pending'
AND id NOT IN (SELECT associated_object FROM useradmin_ongoingprocess)
ORDER BY id DESC
""")
result = cursor.fetchall()

available_objects = CartObject.objects.filter(id__in=[row[0] for row in result])


Pros: Performance boost for huge data.
Cons: Less readable, database-dependent.

Question:


What is the best and most efficient Django ORM approach to filter objects while excluding those that are already assigned?
Would values_list(), isnull=False, or Subquery() be the recommended way?
Are there any performance considerations when working with large datasets?

Continue reading...