How to filter DB query with OR in Django

Question

I am trying to structure a WHERE question LIKE 'Who%' OR question LIKE 'What%' SQL query from inputs of a POST request. What is the correct way to do this?

If showall POST value is True then no next filter needs to be matched. All entries returned. If showall is False then combine the next filters for the OR statement to return entries matching only of the filters provided.

https://docs.djangoproject.com/en/3.1/topics/db/queries/#complex-lookups-with-q-objects

from django.db.models import Q
def getuserlist(request):
    if request.method == "POST":
        showall = request.POST['showall']
        showfl_1 = request.POST['showfl_1']
        showfl_2 = request.POST['showfl_2']

        if showall == 'true':
            filt = Q(listing=any)
        elif showfl_1 == 'true':
            filt = Q(listing="Filtered1")
        elif showfl_2 == 'true':
            filt = filt | Q(listing="Filtered2")

        searchresult = list(User_data.objects.filter(listing=filt).values_list("Country","gender","listing").order_by('-added_date'))
   return searchresult

You can use the |= operator to combine two Q objects queries with OR: filter |= Q(attr=value) — Iain Shelvington
– Iain Shelvington, Commented Dec 31, 2020 at 9:18
@WillemVanOnsem Will fix that. It didn't work on the first elif either. — Majoris
– Majoris, Commented Dec 31, 2020 at 9:18
It is furthermore not entirely clear to me how the filtering should behave: if none of showall, showfl_1 and show_fl2 are checked? Then what is the expected result? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Dec 31, 2020 at 9:19
@WillemVanOnsem showall to select all entries, irrespective of next filters. If filters are True then apply OR on those filters. Entries to be returned for any of those filters matched. — Majoris
– Majoris, Commented Dec 31, 2020 at 9:25

willeM_ Van Onsem · Accepted Answer · 2020-12-31 10:18:34Z

1

You can construct a Q object that is a disjunction of the options:

from django.http import JsonResponse

if showall != 'true':
    filters = []
    if showfl_1 == 'true':
        filters.append(('listing', 'filtered1'))
    if showfl_1 == 'true':
        filters.append(('listing', 'filtered2'))
    if not filters:
        searchresult = User_data.objects.none()
    else:
        searchresult = Q(*filters, _connector=Q.OR)
else:
    searchresult = User_data.objects.all()

searchresult = list(searchresult.values_list(
    'Country','gender','listing'
).order_by('-added_date'))
return JsonResponse({'data': searchresult})

answered Dec 31, 2020 at 10:18

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Majoris Over a year ago

Interesting. Let me try.

Vivek Srivastava · Accepted Answer · 2020-12-31 09:19:40Z

0

You may try something like this

from django.db.models import Q
def getuserlist(request):
    if request.method == "POST":
        showall = request.POST['showall']
        showfl_1 = request.POST['showfl_1']
        showfl_2 = request.POST['showfl_2']
        c = {}
        if showall:
            c['listing']= Q(listing=any)
        elif showfl_1:
            c['listing']= Q(listing="Filtered1")
        elif showfl_2:
            c['listing'] = Q(listing="Filtered2")

        searchresult = list(User_data.objects.filter(**c).values_list("Country","gender","listing").order_by('-added_date'))
   return searchresult

answered Dec 31, 2020 at 9:19

Vivek Srivastava

2071 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to filter DB query with OR in Django

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related