Django query objects with all related objects meeting a condition

Question

Given these Django models:

class Job(models.Model):
    pass

class Task(models.Model):
    job = models.ForeignKey('Job', related_name='tasks')
    status = models.CharField(choices=TaskStatus.CHOICES, max_length=30)
    dependencies = models.ManyToManyField("self", related_name="dependents", symmetrical=False)

I want to query all Tasks with status PENDING and ALL dependencies having status COMPLETED for a single Job.

I wrote the following query, but it returns tasks that have at least one dependency with status completed, which is obviously not want I'm after.

tasks_that_can_be_executed = Task.objects.filter(
    job__pk=job_id,
    status=TaskStatus.PENDING,
    dependencies__status=TaskStatus.COMPLETED
)

Any suggestions?

@PiyushS.Wanare added it. Sorry, I must have missed it when stripping out unrelated code for this example. — Korijn
– Korijn, Commented Feb 21, 2017 at 10:40
Why all are using Q I don't know it mostly use when we need to work with conditions? and filter always work with AND — Piyush S. Wanare
– Piyush S. Wanare, Commented Feb 21, 2017 at 10:51

avd · Accepted Answer · 2017-03-10 10:17:52Z

7

from django.db.models import IntegerField, Case, When, Count, Q

Task.objects.filter(
    job=job,
    status=TaskStatus.PENDING,
).annotate(
    not_completed_dependencies_count=Count(
        Case(When(~Q(dependencies__status=TaskStatus.COMPLETED), then=1),
             output_field=IntegerField(),
        )
    )
).filter(
    not_completed_dependencies_count__gt=0
)

edited Mar 10, 2017 at 10:17

answered Feb 21, 2017 at 12:31

avd

3181 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JanMalte · Accepted Answer · 2022-07-02 20:12:54Z

1

You could use exclude() instead or in combination with filter(), as the exclude() would just throw away any result with a status != COMPLETED

tasks_that_can_be_executed = Task.objects.filter(
    job__pk=job_id,
    status=TaskStatus.PENDING,
).exclude(
    dependencies__status=TaskStatus.PENDING
)

Or if there are multiple status values you like to exclude:

tasks_that_can_be_executed = Task.objects.filter(
    job__pk=job_id,
    status=TaskStatus.PENDING,
).exclude(
    dependencies__status__in=[s for s in TaskStatus.CHOICES if s != TaskStatus.COMPLETED]
)

answered Jul 2, 2022 at 20:12

JanMalte

1,0808 silver badges21 bronze badges

Comments

Nrzonline · Accepted Answer · 2017-02-21 10:43:18Z

0

With Q from django.db.models you can make some more complex queries. If I'm understanding you right, you want to

from django.db.models import Q

tasks_that_can_be_executed = Task.objects.filter(job__pk=job_id).filter(
        Q(status=TaskStatus.PENDING) | Q(dependencies__status=TaskStatus.COMPLETED)
)

Not sure I understood properly what you wanted as result, but in the current situation you will get all tasks from the given job_id, where the status is PENDING together with all objects where dependencies__status is COMPLETED

answered Feb 21, 2017 at 10:43

Nrzonline

1,6182 gold badges20 silver badges40 bronze badges

3 Comments

Korijn Over a year ago

Almost! I only want tasks where ALL dependencies are completed.

Nrzonline Over a year ago

Sorry, I missed the ManyToMany. I'm still searching too, but I can't find somehting satisfying yet to keep it short and in one query.

Korijn Over a year ago

Neither can I. I managed to implement a workaround by filtering out afterwards... but I would rather push it into the query :)

Ajay Gupta · Accepted Answer · 2017-02-21 10:43:16Z

-1

You need to import Q and then do something like:

from django.db.models import Q

tasks_that_can_be_executed = Task.objects.filter(
    Q(job__pk=job_id) & Q(status=TaskStatus.PENDING) & Q(dependencies__status=TaskStatus.COMPLETED)
)

answered Feb 21, 2017 at 10:43

Ajay Gupta

1,2858 silver badges22 bronze badges

1 Comment

Ярига Олег Over a year ago

It seems you just rewrote author's filtering with Q objects. It's not like he wants.

Collectives™ on Stack Overflow

Django query objects with all related objects meeting a condition

4 Answers 4

Comments

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related