How to create a query set that filters multiple fields based on one condition in Django?

Question

My model

class TestModel(models.Model) field1 = models.IntegerField() field2 = models.IntegerField() field3 = models.IntegerField() field4 = models.IntegerField() field5 = models.IntegerField() 

I need a simple set of queries that applies one condition to all five fields of the model, without writing down each combination of fields and filtering them.

For example, I want to apply a None check condition to two or more fields

 TestModel.objects.filter(two_or_more_fields=None) 

I do not want to write every possible combination of 5 fields to find a set of queries with any two or more fields like None. In other words, is there a better way to achieve this than:

 from django.db.models import Q TestModel.objects.filter( #condition for exactly 2 None Q(field1=None & field2=None) | Q(field2=None & field3=None) | Q(field3=None & field4=None) | Q(field4=None & field5=None) | Q(field5=None & field1=None) | #condition for more than 2 None Q(field1=None & field2=None & field3 = None) | ''''' . . #so on to cover all possible cases of any two or more fields as None ) 

I think there must be a better and easier way for this.

Answer 1

After spending hours, I could not find an easy way to do this using the built-in Django filter constructors. However, I found this solution, which is closer to what I was looking for:

 field_list = ['field1', 'field2', 'field3', 'field4', 'field5'] def get_all_possible_filter_dict_list_for_a_condition(field_list): all_possible_filter_dict_for_a_condition = [] for field_1, field_2 in combinations(field_list, 2): all_possible_filter_dict_for_a_condition.append( { field_1:None, field_2:None } ) return all_possible_filter_dict_for_a_condition def get_qs_list_to_perform_or_operation(all_possible_filter_dict_list_for_a_condition): qs_list_to_perform_or_operation = [] for i, filter_dict in enumerate(all_possible_filter_dict_list_for_a_condition): qs_to_append = qs.filter(**filter_dict) qs_list_to_perform_or_operation.append(qs_to_append) return qs_list_to_perform_or_operation def get_qs_to_filter_fields_with_more_than_1_none(qs_list_to_perform_or_operation ): final_qs = qs_list_to_perform_or_operation [0] for i in range(len(qs_list_to_perform_or_operation ) - 1): final_qs = final_qs | qs_list[i + 1] return final_qs all_possible_filter_dict_list_for_a_condition = get_all_possible_filter_dict_list_for_a_condition(field_list) qs_list_to_perform_or_operation = get_qs_list_to_perform_or_operation(all_possible_filter_dict_list_for_a_condition) final_qs_to_filter_multiple_fields_with_same_condtion = get_qs_to_filter_fields_with_more_than_1_none(qs_list_to_perform_or_operation) 

Answer 2

I am not aware of any filtering method by the number of nonzero fields in Django.

Another inconvenient and reliable solution to the problem that I would suggest is to store the number of null fields ( null_count ) in the Model itself as a field.

You can do this by easily overriding the save TestModel method.

 def save(self, *args, **kwargs): self.null_count = 0 self.null_count += 1 if self.field1 is None else 0 # update null_count for 4 remaining fields super(TestModel, self).save(*args, **kwargs) 

And in sight the filter on null_count .

 TestModel.objects.filter(null_count_gt=2) 

Answer 3

This solution is POSTGRESQL specific due to the functions used here. However, this idea can be used if you are using a different database.

---

You can use RawSQL mix and some ARRAY magic.

The idea is to annotate each element in a query set with a number of fields that are not null:

 from django.db.models import Func from django.db.models.expressions import RawSQL field_list = ['field1', 'field2', 'field3', 'field4', 'field5'] # field1 IS NULL, field2 IS NULL, field3 IS NULL, ..... statements = ", ".join("{} IS NULL".format(field) for field in field_list) # create an array for each field by checking if those are NULL # then, remove the False values eg not null ones. # ARRAY_REMOVE(ARRAY[field1 IS NULL, field2 IS NULL, .....], false) raw_sql = "ARRAY_REMOVE(ARRAY[{statements}], false)".format(statements=statements) # now annotate each item with the number of null fields qs = TestModel.objects.annotate(num_null=Func(RawSQL(sql, ()), function='CARDINALITY') # on this queryset you can filter by the number of null items you want to check qs = qs.filter(num_null__gte=2)