CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
ddjjaannggoo
ddjjaannggoo

Reputation: 87

Django queryset date filter based on subelement

I have the following Django models:

class Table(models.Model):
# model fields

    @property
    def is_finished(self):
        return not Meal.objects.filter(table=self)\
            .filter(finished_time=None).exists()

class Meal(models.Model):
    table = models.ForeignKey(
        Table,
        null=True,
        blank=True,
        on_delete=models.CASCADE,
        related_name='meals'
    )
    finished_time = models.DateTimeField(null=True, blank=True, default=None)

A table is considered finished if all meals for it have a finished_time. I would like to annotate is_finished in a queryset, to exclude tables where are meals are finished.

Something like:

Table.objects.all()\
    .annotate(has_meals=Count('meals__finished_time'))\
    .annotate(finished=Min('meals__finished_time'))\
    .filter(finished=None)\
    .exclude(has_meals=0)

But this does not work if I have finished meals and unfinished meals on a table. How can I fix this?

Upvotes: 1

Views: 63

Answers (1)

schillingt
schillingt

Reputation: 13731

I'd use an Exists subquery.

from django.db.models import Exists, OuterRef
Table.objects.all().annotate(
    is_finished=~Exists(
        Meal.objects.filter(table=OuterRef('id'), finished_time__isnull=True)
    )
)

The ~ flips the Exists value. To determine if there are any finished this will look to see if there any unfinished meals left, then flip it.

Upvotes: 1

Related Questions