Django filter for ANY value in a MySql column

Question

I want to be able to use this filter for "specific" or "null" or "any value" scenarios

#  3 scenarios for the third field : Col3 

#  Query for a specific value
sample = table.objects.filter(Col1=343, Col2=545, Col3=656)

#  Query for NULL value
sample = table.objects.filter(Col1=343, Col2=545, Col3=None)

#  Query for any value
sample = table.objects.filter(Col1=343, Col2=545, Col3=???)

Specific and NULL scenarios are handled by exact values like 656 and "None" in python. How do we handle ANY value scnearios from above ?

Imagine 3 drop down boxes based on which we can query the database.

My question is similar to this one, but specific to Django

original sql specific question is here: How do you query an int column for any value? , coloumn in my case can be of any type

resulting query might look something like this:

// Query for a specific value
select Col1,Col2,Col3 from table where Col1=343, Col2=545, Col3=656       
// Query for NULL value
select Col1,Col2,Col3 from table where Col1=343, Col2=545, Col3 IS NULL
// Query for any value
select Col1,Col2,Col3 from table where Col1=343, Col2=545, Col3=*

Have I misunderstood? so you want to either filter Col3=656, None or All dynaimcally? — Anzel
– Anzel, Commented Oct 27, 2014 at 14:02
yes the second comment is what i am looking for, and it can be any value not just 656... — maheshg
– maheshg, Commented Oct 27, 2014 at 14:05

fcdt · Accepted Answer · 2020-10-07 09:17:54Z

2

You can use the following statement:

# Query for any value
sample = table.objects.filter(Col1=343, Col2=545, Col3__regex = r'.')

edited Oct 7, 2020 at 9:17

fcdt

2,5215 gold badges17 silver badges27 bronze badges

answered Oct 7, 2020 at 8:08

Ali Almakldi

313 bronze badges

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

Comments

soulprovidr · Accepted Answer · 2014-10-27 14:22:47Z

0

I think you should be able to omit the Col3 filter like this:

sample = table.objects.filter(Col1=343, Col2=545)

Edit: Are you trying to condense all of these cases into a single statement?

I'm not familiar enough with the subtleties of QuerySets in Django to give you a one-liner, but, taking your 3 dropdown boxes example a bit further...what if your dropdowns contained 'None' and 'Any' in addition to the regular list of values? That way you could select a query based on the parameters provided.

if param3 == 'None':
    sample = table.objects.filter(Col1=343, Col2=545, Col3=None)
elif param3 == 'Any':
    sample = table.objects.filter(Col1=343, Col2=545)
else:
    sample = table.objects.filter(Col1=343, Col2=545, Col3=param3)

edited Oct 27, 2014 at 14:22

answered Oct 27, 2014 at 13:53

soulprovidr

7543 silver badges14 bronze badges

4 Comments

soulprovidr Over a year ago

@user1035818: I've edited my answer - it should do the trick until someone with a bit more experience with Django can help you.

maheshg Over a year ago

I have already done it this way, but there has to be some better way to condense it. I have 5 columns on which I want to apply these cases, so condensing it makes it really tidy, rather than going for a big if-else block. Appreciate your help. :)

theQuestionMan Over a year ago

stackoverflow.com/questions/38774133/…

theQuestionMan Over a year ago

stackoverflow.com/questions/12651211/…

Collectives™ on Stack Overflow

Django filter for ANY value in a MySql column

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related