Django advanced query on the same model

I have a Kit and KitInOut models:

class Kit(models.Model):
    name = models.CharField(max_length=255, blank=True, null=True)

class KitInOut(models.Model):
    kit = models.ForeignKey(Kit, on_delete=models.CASCADE)
    out = models.BoolField(default=True)
    creation_timestamp = models.DateTimeField()

I want to find out which kits are out and dont have the the same kit again in the resulting query, which is something like : "select * from KitInOut where, for each kit k, keep it in the result

  • if it went out** (kit_went_out=1) and
  • there's no other KitInOut k2 where k2.kit=k and k2.creation_timestamp > k.creation_timestamp"

Here's the code I have, which is so un-optimized that I can't use it with more than 500 KitInOut rows:

k_in_out_s = KitInOut.objects.filter(out=True)
result = []
for obj in k_in_out_s:
    if (KitInOut.objects.filter(
            kit=obj.kit,
            out=False,
            creation_timestamp__gt=obj.creation_timestamp,
    ).count() == 0):
        result.append(obj)
return result

How to optimize this?

I think you can try like this using Count:

Kit.objects.annotate(
    kit_out_counter=Count('kitinout', filter=Q(out=True,creation_timestamp__gt=F('creation_timestamp')),distinct=True),
    kit_not_out_counter=Count('kitinout', filter=Q(out=False,creation_timestamp__gt=F('creation_timestamp')),distinct=True)
).filter(kit_out_counter__gt=0,kit_not_out_counter=0)

Here I am counting both IN and OUT KitInOut instances for every Kit object in queryset, then filtering based on that.

Back to Top