CODEWITHSUNDEEP
pythondjangodjango-queryset
Mikey Lockwood
Mikey Lockwood

Reputation: 1261

Querying Django - Return objects where exactly one field is not null

Using Django 1.8

I'm trying to filter a model on whether only exactly one of the chosen fields are populated. If I had a model like this, I would like to be able to filter on it as below.

class MyModel(models.Model):
    field_a = models.IntegerField(null=True)
    field_b = models.IntegerField(null=True)
    field_c = models.IntegerField(null=True)
    field_d = models.IntegerField(null=True)

(field_a__isnull=False, field_b__isnull=True, field_c__isnull=True, field_d__isnull=True)
OR
(field_a__isnull=True, field_b__isnull=False, field_c__isnull=True, field_d__isnull=True)
OR
(field_a__isnull=True, field_b__isnull=True, field_c__isnull=False, field_d__isnull=True)
OR
(field_a__isnull=True, field_b__isnull=True, field_c__isnull=True, field_d__isnull=False)

So the queryset should return all objects which have only one of the fields in the model populated and the rest of the fields as null. Is there a way to achieve this through Django queries?

Upvotes: 1

Views: 287

Answers (2)

Mikey Lockwood
Mikey Lockwood

Reputation: 1261

I was able to build this dynamically with Q

fields = ['field_a', 'field_b', 'field_c', 'field_d']

q_query = reduce(operator.or_, (
    (reduce(operator.and_, (
        eval('Q({}__isnull={})'.format(f, False if f == field else True))
        for f in fields
    )))
    for field in fields
))

MyModel.objects.filter(q_query)

This builds a Q object with AND filters nested with an OR filter and queries on that

<Q: (
  OR: (
    AND: ('field_a__isnull', False), ('field_b__isnull', True), ('field_c__isnull', True), ('field_d__isnull', True)
  ), (
    AND: ('field_a__isnull', True), ('field_b__isnull', False), ('field_c__isnull', True), ('field_d__isnull', True)
  ), (
    AND: ('field_a__isnull', True), ('field_b__isnull', True), ('field_c__isnull', False), ('field_d__isnull', True)
  ), (
    AND: ('field_a__isnull', True), ('field_b__isnull', True), ('field_c__isnull', True), ('field_d__isnull', False)
  )
)>

EDIT

I took another look at this and changed creating q_query to not use eval()

q_query = reduce(operator.or_, (
    (reduce(operator.and_, [
        Q(**{'{}__isnull'.format(f): False if f == field else True})
        for f in fields
    ]))
    for field in fields
))

Upvotes: 1

labennett
labennett

Reputation: 327

If you need a quick and dirty solution, you could try adding a property to your model and using that to query whether your model has only one populated field.

class MyModel(models.Model):
    field_a = models.IntegerField(null=True)
    field_b = models.IntegerField(null=True)
    field_c = models.IntegerField(null=True)
    field_d = models.IntegerField(null=True)

    @property
    def populated_field_count(self):
        fields = [self.field_a, self.field_b, self.field_c, self.field_d]
        return len([f for f in fields if f != None])

objects_with_one_field = [obj for obj in MyModel.objects.all() if obj.populated_field_count == 1)]

Please note that the populated_field_count property cannot be used in the Django Query, meaning it does not operate at the database level, making this a hit to performance.

# THIS WILL NOT WORK IF USING OPTION ONE
objs = MyModel.objects.filter(populated_field_count=1)

EDIT: As an option two, if you need this to be done via queries you could use the Django Q to make an advanced query. Answers to this option have already been given.

As an option three, if you do not want to use Django Q, you could add populated_field_count as an IntegerField property of the Model. Then overwrite the save function to calculate the number of fields populated upon each save. This would then allow you to query using:

MyModel.objects.filter(populated_field_count=1)

Upvotes: 0

Related Questions