Queries across columns - modifying the SearchHints example

Anvil Q&A
3

@jeff

Hi Jeff.

In general, if you want to search across multiple columns, you might try to use Anvil’s powerful query operator.

For example, this query will return all rows that contain a particular string (represented by the variable text) and considers two columns in the search.

@anvil.server.callable
def get_data(text):
  
  search_string='%{}%'.format(text)
  
  rows=app_tables.my_tools.search(
    q.any_of(
      name=q.ilike(search_string),
      material=q.ilike(search_string)
    )
  )
  
  return rows

Query operators are powerful and there is a nice tutorial here explaining how to use them.

You will see in that tutorial that there is even a way to do an intelligent full-text search.

If you are asking how to pass a query along to a server function, one way to do that, as you can see above, is to pass your search text as an argument when you call your server function. Then, inside the server you can make use of that string.

Here is a simple app that uses the above query to autocomplete items in a repeating panel (similar to what SearchHints does).

https://anvil.works/build#clone:ZQEQY3CUTFCOP7I6=U6LXTJKKYBDF5ZNJ3PSDDGA7

com-resize%20(1)

Now, the SearchHints component, and companion example app, are a bit more complicated if you are a beginner (it confuses me a bit too). I would make sure you understand my basic app first.

The SearchHints example app does not use queries in a server function as I did above, rather it returns all rows from the data table to the client. Then, the SearchHints component filters the returned strings using standard python string methods (e.g., .startswith())

There are a few things that make the SearchHints functionality a bit confusing at first. Namely, the raise_event and set_event_handler methods. Once you understand these, it should make sense how the component and companion app are working together.

Please see the documentation on events and the SearchHints tutorial.

Finally, here is a modification of the SearchHints app that searches across multiple columns using query operators.

https://anvil.works/build#clone:J7IXTXMODTJQGXQN=IZS7FMH2VZMWWDTJTPWZX4BW

Let me know if there are any other questions.

3 Likes