Filter Django objects where related object exists

Question

This is bound to be a duplicate question but I can't find any others. I'm trying to get a list of photos that have complaints. I can't simply get complaints and deal with the related photos - I need a queryset of photos.

This should work but doesn't seem right:

Photo.objects.filter(complaint__id__gte=0)

This doesn't seem like the most efficient way:

Photo.objects.annotate(Count('complaint')).exclude(complaint__count=0)

Is there a better way?

jcfollower · Accepted Answer · 2012-12-11 21:57:11Z

90

how about ...

Photo.objects.filter(complaint__isnull=False)

from https://docs.djangoproject.com/en/dev/topics/db/queries/

answered Dec 11, 2012 at 21:57

jcfollower

3,15822 silver badges26 bronze badges

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

3 Comments

Christoffer Over a year ago

You might also need a .distinct() in the end to replicate the same as an annotate does. At least I did.

u.unver34 Over a year ago

@Christoffer you saved my life, thanks. It is a deadly issue, so i want to write a comment and improve emphasis.

digenishjkl Over a year ago

This is less code, even its SQL is, but to my experience the query can get extremely slow. It generates a LEFT JOIN with the referencing table and a WHERE clause checking for NULL. This doesn't let the query planner use an index that may be in place for the foreign key. Using an Exists() subquery is way faster.

user10407 · Accepted Answer · 2025-06-03 06:16:18Z

24

I'm not sure which variant is the best, but this works as well:

Photo.objects.exclude(complaint=None)

Generated SQL query is not exactly like in the case of .filter(complaint__isnull=False), but sense is identical.

edited Jun 3 at 6:16

user10407

233 bronze badges

answered Jul 13, 2017 at 10:04

Mark Mishyn

4,2113 gold badges31 silver badges32 bronze badges

5 Comments

Dan Over a year ago

This is better than the accepted answer, because it doesn't require a .distinct() call to work with multi-valued relationships.

AnaPana Over a year ago

This answer also works when using Q objects. This code works: Q(related_object=None) whether this doesn't: Q(related_object__is_null=True)

ezdookie Over a year ago

this is shorter but also slower.

Mark Mishyn Over a year ago

@ezdookie I tested it locally (50 k rows), speed is almost the same, the difference is about 5%.

digenishjkl Over a year ago

@ezdookie see my comment on the accepted answer. This kind of filtering can prevent potential index-only scans.

Marcos · Accepted Answer · 2022-05-24 19:17:57Z

4

Depending on the complexity of the relationship and filter logic you might need this (or this can turn out to be more readable):

complaints = Complaint.objects.filter(
    # some complex filter here
   Q(...) & Q(...) | Q(...)
)

Photo.objects.annotate(
    has_complaints=Exists(complaints)
).filter(has_complaints=True)

edited May 24, 2022 at 19:17

answered May 24, 2022 at 17:53

Marcos

1,48416 silver badges18 bronze badges

1 Comment

dirkjot Over a year ago

You likely want to use OuterRef in those Q(..) parts so that the complaints are linked to the photos you are querying . See: docs.djangoproject.com/en/5.0/ref/models/expressions/…

Collectives™ on Stack Overflow

Filter Django objects where related object exists

3 Answers 3

3 Comments

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related