Question 89577 in PostgreSQL Question posted in
The official documentation can be found here.

Django ORM – SQL part of WHERE if parameter is null – Postgresql

I have a problem in django trying to exclude or include part of the filter if the parameter is null.

I have 4 parameters in my model:
StartDate, EndDate, Severity and Source.

The filter can have one or more of these parameters. If I pass 1 of them the others could be None unless they are defined.

I was able to solve my problem with query raw by doing

WHERE (param IS NULL OR param=param)

and the same in django

"""SELECT * FROM mymanagelogs_logs WHERE (%(severity)s IS NULL OR severity=%(severity)s) AND (%(source)s IS NULL OR source=%(source)s) AND (%(start_date)s IS NULL OR timestamp >= %(start_date)s) AND (%(end_date)s IS NULL OR timestamp <= %(end_date)s)""",
params={'severity': severity, 'source': source, 'start_date':start, 'end_date':end}
)

but I can’t do the same QuerySet:

objects.filter(Q(date__range=(start, end)) & (Q(severity=severity) | Q(severity=None))

Any ideas?

Tags:

2

Answers


  1. 0

    You could try and build the queryset incrementally. For example:

    queryset = MyModel.objects.all()

if severity is not None:
    queryset = queryset.filter(severity=severity)

if source is not None:
    queryset = queryset.filter(source=source)

if start is not None and end is not None:
    queryset = queryset.filter(date__range=(start, end))
    Login or Signup to reply.
  2. 0 
    queryset = MyModel.objects.filter(Q(severity=severity) | Q(severity__isnull=True), date__range=(start, end))

    __isnull is used to check for NULL data.

    Also no need of using & for MYSQL ‘AND’ as filter checks using ‘AND’ by default.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search