Filtering search from app tables

Anvil Q&A
#1

In my application, I am trying to send keywords from a form to a server module to get filtered results from app_tables.table.search(). I want it to be as generalized as possible so I can continue to add new features to filter by and not have to always be updating the server module function.

Here is how my first attempt is starting.
content_item_list =self.get_filtered_content(Public=True, Stereo=True)

which calls:

def get_filtered_content(self, **kwargs):
  content_list = anvil.server.call('get_filtered_content_list', filter_args = kwargs)
  return content_list

Where the content is filtered in a pretty rag-tag way:

@anvil.server.callable
def get_filtered_content_list(filter_args):
      if ('Public' in filter_args):
        content_list = list(app_tables.content.search(Public=filter_args['Public']))
        if ('Stereo' in filter_args):
          filtered_content_list = []
          for content in content_list:
            if content['Stereo'] == filter_args['Stereo']:
              filtered_content_list.append(content)
      return filtered_content_list

This works for now, but I will have to repeat this nested check for each optional filter I may want to add. I would rather pass the filter_args directly into .search() but it does not work because they are passed as a dictionary. Is there a better way to do this type of filtering?

The better question is: Is there a way to pass the keyword arguments from self.get_filtered_content(Public=True, Stereo=True) in the form directly into app_tables.content.search() in the server module?

If not, is there an easy way to translate the kwargs dictionary{'Stereo': True, 'Public': True} back into the arguments format (Stereo=True, Public=True) that .search() is expecting when getting data from app_tables?

In the reference, I just now saw a filtering method like this which would work but i I would always have to be maintaining the filter options which I don’t want to do. For example:

if 'Public' in filter_args:
  public_search = filter_args['Public']
else:
  public_search = [True, False]
if 'Stereo' in filter_args:
  public_search = filter_args['Public']
else:
  public_search = [True, False]
all_my_stereo_content = app_tables.content.search(Public=public_search, Stereo=True

Seems like a headache.

Hopefully someone can help level up my knowledge on this topic. Thank you.

#2

I answered it myself. So simple. Typing out the question helped me know what to google.

@anvil.server.callable
def get_filtered_content_list(filter_args):
  content_list = app_tables.content.search(**filter_args)
  return content_list
4 Likes
#3

I spent hours trying to combine q.any_of and q.all_of without any result, then found your answer.
I think this topic is quite common.
People coming from plain SQL often build their query in the code and this way of doing that in Anvil’s DB is so different that it’s hard to come to mind.
I think it would be helpful to add it to some DOCs page, e.g. here
Thanks @joinlook

#4

This is fantastic!!

For those who arrive here, you can also wrap **filter_args in a q.all_of or q.any_of, depending on the kind of filtering you want to do.

+1 to @aldo.ercolani suggestion this be added to the docs!

2 Likes
#5

A post was split to a new topic: Unpacking arguments

#6

Great! Just what I wanted to help me solve what I want to do: pass a list of keywords as argument to query the table. If I still run into some hitch, could I ask your help later? I am really ‘green’ on Anvil but find it so much easier than diving deeper into Flask and Django web framework.

Thanks.