Queries across columns - modifying the SearchHints example

[jeff]

Hi Team,

What’s the best way to modify the SearchHints module to search across multiple columns and do a full text search? I don’t quite grasp how get_search_keys in the Server module is receiving what to search for (or if it’s not receiving a query now, how do I send one in)?

Thank you,

Jeff

[adhamfaisal]

Hi @jeff,

Meredydd has a great post about the most efficient way to ask a question, please review: How to access a drop down list from an ItemTemplate form
This is a very supportive forum, everyone here is willing to help if they understand what are you trying to accomplish. I would suggest that you rewrite your question based on the tips on the link, and I am pretty sure you will get your answer in no time.

Good luck

[campopianoa]

@jeff

Hi Jeff.

@jeff

… to search across multiple columns

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.

@jeff

… and do a full text search?

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

@jeff

how do I send one in?

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

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.

@jeff

… modify the SearchHints module

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.

[jeff]

Thank you for such a detailed response! I’ve diving into the docs and example app that you provided. It’s very much appreciated!

Jeff