[Django]-How to do an exclude django query on multiple foreign key

What about this,

from django.db.models import Q

User.objects.filter(Q(alpha_thing__assigned_at__isnull=False) | Q(beta_thing__assigned_at__isnull=False)).distinct()

Screenshots

1. Auth model structure – User

2. Thing model

There is another way, since you want to filter user which “things” contains all an assigned_date.

You could:

User.objects.filter(
    alpha_thign__assigned_at__isnull=False,
    beta_thign__assigned_at__isnull=False,
)

Simple.

There are no need to Use Q objects here or | (or) operations.

What you want is not

• alpha_thing__assigned_at__isnull=False OR
• beta_thing__assigned_at__isnull=False

What you’re looking for is

• alpha_thing__assigned_at__isnull=False AND
• beta_thing__assigned_at__isnull=False

For all users which don’t have a Thing with an empty date try:

return User.objects.exclude(
    alpha_thing__assigned_at=None
).exclude(
    beta_thing__assigned_at=None
).all()

By the way, I got the same result whether I used .all() at the end or not, so:

return User.objects.exclude(
    alpha_thing__assigned_at=None
).exclude(
    beta_thing__assigned_at=None
)

returned the same result as the first example.

Have you tried something like this?

from django.db.models import Q

has_null_alpha = Q(alpha_thing__isnull=False, alpha_thing__assigned_at__isnull=True)

has_null_beta = Q(beta_thing__isnull=False, beta_thing__assigned_at__isnull=True)

User.objects.exclude(has_null_alpha | has_null_beta)

Reasoning

I think the reason you’re seeing unexpected results may not have anything to do with the fact that there are multiple ForeignKey paths in the queryset. Your statement that “the thing table is empty” might be the key, and the reason users aren’t showing up is because they have no alpha_thing or beta_thing relation.

NOTES:

• The QuerySet User.objects.exclude(alpha_thing__assigned_at__isnull=True) produces a left outer join between the User table and the Thing table, which means that before doing any comparisons in the WHERE clause, you’re getting NULL for assigned_at in any row where there is no Thing.

• One really weird thing here is that a filter causes an INNER join, so that the statement User.objects.filter(alpha_thing__assigned_at__isnull=False) actually only yields the users who actually have alpha_thing related objects with a non-NULL value for assigned_at (leaving out those guys with no related alpha_thing).