Just wondering if which way would be the most efficient way to search through the anvil app_tables.
I have a list where I am returning the complete dataset of the table and I have a text box in which I search for the text as the person types and it will bring up partial matches. As far as I know the .search() doesn’t work for partial searches (e.g “This is my awesome title that i want to search on” would only bring up the match for the beginning of This as opposed to me start searching for awesome.
Return a list() object from the .search() and use the list comprehension on the fully loaded list to run through this has worked fine but its a long loading time initially return the full list. However subsequent searches run very fast.
Return the .search object and run the list comprehension on that which is what I currently have it set to do. This loads the page up nice and fast but then takes a while to search through the 5000+ rows for each new type / search term.
Is there a 3rd option I am missing? Or is there a way to do wildcard searching on the anvil tables? i.e the equivalent of %searchstring% in MySQL?
Update: This post is out of date! We now have the q.ilike() search operator – see the docs!
You’re right; we don’t currently support substring search with Data Tables. (This is for technical reasons to do with how the database indices work. Unless you’re on a dedicated plan, you share a database engine with everyone else, so we have to keep the expensive operations to a minimum!)
The optimal way to do this kind of search is:
Filter on the server, not in the Form. That way, you don’t ship your whole database to the client!
Don’t make a list of the whole query before you filter. app_tables.foo.search() returns an iterable that fetches rows as you ask for them, so reading everything into memory up-front is unnecessary.
Especially for as-you-type search results, limit the number of results you return if you can. (Eg perhaps only show the 10 most recent items that match your search, using tables.order_by.) You can have a “see more results” button that loads the full list if you need to.
Here’s a really simple search:
@anvil.server.callable
def search_records(search_string):
search_string = search_string.lower()
return [r for r in app_tables.records.search()
if search_string in r['description'].lower()]
Here’s a more advanced version that returns only the first 10 items:
def take(sequence, n):
l = []
for e in sequence:
l.append(e)
if len(l) >= n: break
return l
@anvil.server.callable
def search_records(search_string):
search_string = search_string.lower()
# NB this is a lazy generator ((round brackets), not [square brackets]).
# This means results aren't computed until they're pulled out, so we
# don't fetch anything from the database we don't need.
matching_results = (r for r in app_table.records.search()
if search_string in r['description'].lower())
return take(matching_results, 10)
Did some performance testing… so I already (client side) filter the maximum returned items by 100 as it killed the client side browser when displaying them all (plus why would I want to display them all!)
With the first change moving the list filter to the server module from the client side form code that has decreased the wait time by half (15 seconds originally down to 6-7 seconds)
Putting in the return of results didn’t seem to make much difference (it was maybe slightly quicker but not noticeably so).
Thanks so much for the suggestions thats been a great help. Its for an internal helper tool so I can live with a few seconds searching time
I am trying to implement exactly this search function, but can not figure out what I am doing wrong. It sometimes searches well, but more often it just doesn’t work it displays rows that certainly don’t have the letters or words in search_string:
I am calling the search function on server this way:
@anvil.server.callable
def search_order(search_string):
search_string = search_string.lower()
print (search_string)
return [r for r in app_tables.tasks.search()
if search_string in r['order_description'].lower() or search_string in r['order_client'].lower()]
@jan.kyselak are you able to show an example of this not working as expected?
I created a little app to try your code and it seems to work. Perhaps you could show a small example of the strings in the database that are not found despite a reasonable search string.
I would also note that this is a reply to an old topic, and Anvil now has a more advanced set of search operators (look for q.ilike('%foo%')). I suspect @alcampopiano is right, though, and this will yield the same results as the manual query!
I have found out, that when I turn off live search - searching only on pressed enter (same as you have it in your app), not on change, it works perfectly. So maybe it would need different approach ?
But for now it’s ok for me as the results are good.
If live search means calling the server to execute the search every time a key is pressed, then you might have timing problems. Chances are that the second change event is triggered before the form has finished rendering the rows returned by the previous server call.
This sometimes (it depends on how the events are managed by your code) results in two requests being sent, the repeating panel (or whatever you use to show the rows) being cleared twice in a row, then the rows returned by the two calls added to the repeating panel.
If this is the problem, then you can try making sure that no requests are pending before clearing and updating. You can also try using a timer: every change event sets timer.interval = 0.5, then the timer event sends only one request every half second.
Sorry to bump an old thread. I thought I would add a nice way to grab results from a search that uses tables.order_by. It allows you to grab items by the index of the search result. By default, it grabs the first 10 items. I’m using this to grab n items at a time while the user scrolls.
Given a search:
def take(sequence, start_index, stop_index):
l = []
for index, e in enumerate(sequence):
if index >= start_index and index <= stop_index:
l.append(e)
if index > stop_index:
continue
return l
@anvil.server.callable
def load_feed(utc_date, start_index=0, stop_index=9):
results = app_tables.feed.search(tables.order_by('datetime', ascending=False))
return take(results, start_index, stop_index)
The examples in the original thread use the take function and a generator because they are also filtering the search results but there’s no filtering in your example, so it shouldn’t be necessary.
Haha of course it does! Why wouldn’t I try that? Thanks!
A note on slicing though, it’s exclusive (like all/most slicing in Python) so be sure to add 1 to your stop_index to ensure you get the results you want.
