Kristoffer Rath Hansen Kristoffer Rath Hansen - 1 year ago 41
SQL Question

Only return result if a field is null for that result Django

I have a database in Django with several tables.

1 table is a person, 1 is a table with departments and 1 is a table of volunteers, which has a many-to-many relationship.

Here is the relevant parts of the Department model:

class Department(models.Model):
name = models.CharField('Navn',max_length=200)

def __str__(self):

Here is the relevant part of the Person model:

class Person(models.Model):
name = models.CharField('Navn',max_length=200)

def __str__(self):

And here is Volunteer:

class Volunteer(models.Model):
person = models.ForeignKey(Person)
department = models.ForeignKey(Department)
added = models.DateTimeField('Start',
removed = models.DateTimeField('Slut', blank=True, null=True, default=None)
approved = models.DateTimeField('Godkendt af afdelingsleder',, null=True, blank=True)

class Meta:
verbose_name = "Frivillig"
verbose_name_plural = "Frivillige"

def __str__(self):
return self.person.__str__()

def has_certificate(self):
return self.person.has_certificate

I want to be able to select all active volunteers and people who are not volunteers.

This is a part of the code I used for the filter in the admin area:

if self.value() == 'any':
return queryset.exclude(volunteer__isnull=True).exclude(volunteer__removed__isnull=False)
elif self.value() == 'none':
return queryset.filter(volunteer__isnull=True).filter(volunteer__removed__isnull=False)

The removed field is a datetime field for when the volunteer stopped being a volunteer in that department.

My problem is that if a person is a volunteer in more than 1 department, it'll not return the correct result.

For any, it should return a person if at least 1 volunteer__removed is null.

For none it should return a person if none of volunteer__removed is null.

I've searched the documentation and all around Google and Stack Overflow, I've tried things like putting it in a for loop, but I couldn't get any of it to work.

Answer Source

I ended up solving it in the following way:

    if self.value() == 'any':
        return queryset.filter(volunteer__isnull=False).filter(volunteer__removed__isnull=True).distinct()
    elif self.value() == 'none':
        return queryset.filter(volunteer__isnull=True) | queryset.exclude(volunteer__removed__isnull=True).distinct()