How to filter by range OR "null" value? (I.e. combine NumberFilter range and BooleanFilter-CodePudding

I have a Item model with a numeric number field. This number field defaults to null.

# models.py
class Item(models.Model):
  number = models.IntegerField(default=None, blank=True, null=True)

I want to set-up filters that can return a queryset of Items where number is in range - which is straightforward enough:

# filters.py
class ItemFilter(django_filters.FilterSet):
  min_num = django_filters.NumberFilter(method="min_num_filter")
  max_num = django_filters.NumberFilter(method="max_num_filter")

  class Meta:
    model = Item
    fields = ("min_num", "max_num", "incl_null")

  def min_num_filter(self, queryset, name, value):
    return queryset.filter(number__gte=value)

  def max_num_filter(self, queryset, name, value):
    return queryset.filter(number__lte=value)

But what if I want to have an additional Boolean filter that can include Items that has null for number along with whatever Items matches the min_num and max_num range?

So for example, a URL query in the form of ?min_num=1&max_num=10&incl_null=True should return all Items where number is between 1 and 10 OR number is equal to None.

The following code does not work:

class ItemFilter(django_filters.FilterSet):
  ...
  incl_null = django_filters.BooleanFilter(method="incl_null_filter")

  class Meta:
    model = Item
    fields = ("min_num", "max_num", "incl_null")

  // doesn't work
  class incl_null_filter(self, queryset, name, value):
    if value is True:
      return queryset | Item.objects.filter(number=None)
    if value is False:
      return queryset

Edit: I've tried the methods in the "Filtering by empty values" documentation but I think that's for null values exclusively - where I'm looking for a range match OR a null value.

CodePudding user response：

Well, the only solution I can think of is to pass the min range, max range, and is_null boolean into a single char field then convert it into the 3 individual filters for actioning.

So the query URL will look like ?master_num=1-10-1 for range 1 - 10 incl. None and ?master_num=1-10-0 for range 1 - 10 excl. None.

class ItemFilter(django_filters.FilterSet):
  master_num = django_filters.CharFilter(method="master_num_filter")

  class Meta:
    model = Item
    fields = ("master_num")

  def master_num_filter(self, queryset, name, value):
    # array = [min, max, 1 or 0 for True and False]
    array = value.split("-")
    min = Q(year_published__gte=int(array[0]))
    max = Q(year_published__lte=int(array[1]))
    if array[2] == "1":
        incl_null = Q(year_published=None)
        return queryset.filter((min & max) | incl_null)
    else:
        return queryset.filter(min & max)

Would like to know if there's a better way to do this.

CodePudding user response：

Try this query:


from django.db.models import Q

min_ = 0
max_ = 10

Item.objects.filter(Q(number__gte=min_, number__lte=max_) | Q(number__isnull=True))