Filtering Data Grid with MultiSelectDropDown

Anvil Q&A
1

What I’m trying to do:
I’m trying to filter a data grid with a MultiSelectDropDown from anvil_extras. This component returns a list of strings, but when I pass the list with two or more strings to my function, the data grid doesn’t show any row.

image
image1127×472 60 KB

When just one item is selected in the MultiSelectDropDown it works fine.

image
image1051×443 69.9 KB

What I’ve tried and what’s not working:
I’ve tried to iterate over the list of strings called brand (brand = self.multi_select_drop_down_1.selected), and append the result to a list of queries.

Code Sample:

queries = []

if brand:
  for a in brand:
     queries.append(q.any_of(brand=q.ilike('%{}%'.format(a))))

if len(queries) > 0:
     result = app_tables.email.search(q.all_of(*queries))
else:
     result = app_tables.email.search()

But as I mentioned, the data grid shows no rows.

Thanks

2

try:
result = app_tables.email.search(q.any_of(*queries))

1 Like
3

Hi @meelvoorfer ,

Thank you for your feedback. The problem with result = app_tables.email.search(q.any_of(*queries)), is that works with filtering by multiple items but when I add other filters it doesn’t.

My mistake, I didn’t share the whole function:

@anvil.server.callable
def search_table(query=None, brand=None, country=None, language=None, from_date=None, to_date=None):
    queries = []

    if query:
        queries.append(
            q.any_of(
                subject=q.ilike('%{}%'.format(query)),
                brand=q.ilike('%{}%'.format(query)),
                country=q.ilike('%{}%'.format(query)),
                language=q.ilike('%{}%'.format(query))
            )
        )

    if brand:
      for a in brand:
        queries.append(q.any_of(brand=q.ilike('%{}%'.format(a))))

    if country:
        queries.append(q.any_of(country=q.ilike('%{}%'.format(country))))

    if language:
        queries.append(q.any_of(language=q.ilike('%{}%'.format(language))))

    if from_date:
        queries.append(q.any_of(email_date=q.greater_than_or_equal_to(from_date)))

    if to_date:
        queries.append(q.any_of(email_date=q.less_than_or_equal_to(to_date)))

    if len(queries) > 0:
        result = app_tables.email.search(q.all_of(*queries))
    else:
        result = app_tables.email.search()

    return result
4

hi,

what would help then is to print out your generated query statement at the end to check that it is what you expect it to be.

5

I’ve used the same technique for flexible queries without any issues, and I don’t see anything in your code that particularly jumps out at me as wrong.

What’s the structure of the data table you’re searching? Are any of the fields linked fields instead of strings?

6

I think the brand query should be something like

if brand:
    brand_queries = (q.ilike(f"%{a}%") for a in brand)
    queries.append(
        q.any_of(brand=q.any_of(*brand_queries))
    )

I think you can also simplify the query code somewhat, maybe something like:

from collections import defaultdict

@anvil.server.callable
def search_table(query=None, brand=None, country=None, language=None, from_date=None, to_date=None):
    queries = defaultdict(list)

    if query:
        query = q.ilike(f"%{query}%")
        queries["subject"].append(query)
        queries["brand"].append(query)
        queries["country"].append(query)
        queries["language"].append(query)
    if brand:
        queries["brand"].extend(q.ilike(f"%{a}%") for a in brand)
    if country:
        queries["country"].append(q.ilike(f'%{country}%'))
    if language:
        queries["language"].append(q.ilike(f'%{language}%'))

    queries = {col: q.any_of(*query_list) for col, query_list in queries.items()}

    # handle dates
    if from_date and to_date:
        queries["email_date"] = q.between(from_date, to_date)
    elif from_date:
        queries["email_date"] = q.greater_than_or_equal_to(from_date)
    elif to_date:
        queries["email_date"] = q.less_than_or_equal_to(to_date)

    result = app_tables.email.search(**queries)

    return result
1 Like
7

Hi @stucork ,

Right, this code does what I want:

I have troubles with the second one:

image
image1622×731 99.2 KB

I cannot identify the problem.

Thanks a lot.