Then if I search for “Mic administrateur” it should only return the second line
I can do it this way but I cannot find information on Nom column in client (cannot figure why it say row not there anymore when I put x[‘Client’][‘nom’])
And it slow du to fact I need to load entire table in memo then parse every row x number of time. I cannot figure how to ask that to the anvil.tables.query function ive try to match some but not luck for the moment, I just thinking about future and I think this gona not work for maby 20000 entry du to 30Seconde of time out task.
it should be a lot faster if I can figure how to get the sql do the search via the orm.
for x in app_tables.credential.search(): #Table returning Client, username, password, description more or less 2000 entrys
ok = 1
for each in array_query: #This run on server side function, array_query is simply a “stence stencen word word”.split()
if each.casefold() in str( str(x['Client']) + x['Username'] + x['Password'] + x['Description']).casefold() and ok == 1:
ok = 1
else:#dont loop on other word if one not match
ok = 0
break
if ok == 1 :
result.append(x)
I have already done some try there (Mathing String) and with q.any_of or q.all_of to mix with ilike query (Cause i need casefold search) . But where i stuck is i need to lookup if each in a array can be found in any collumn. each term in the array need to be found or is not a valid result. think a a search bar more term you add more precise the search is.
maybe is some way to search on a concat field via the orm ? duno. but yes i have read several time this documentation you link thank @alcampopiano
EDIT:
In fact the q.full_text_match could be perfect but it search on only one column the difference is all term need to match but this can be from some collumn not only one.
Also, this is one way of searching all your columns with a query operator, without having to loop during the search:
# what you are searching for
my_str='%c%'
# list of dictionaries containing your table's column information
cols=app_tables.my_table.list_columns()
# build a search query dictionary
my_dict={d['name']:q.ilike(my_str) for d in cols}
# search the table with the dictionary
rows=app_tables.my_table.search(
q.any_of(**my_dict)
)
return rows
I @alcampopiano and thank for gidance i was not aware of this syntax (zip and cycles) very interesting !!
I think we are close to but not yet functionnal. have play around with the example you give me. very interesting to dynamic create collumn.
The issus is if i have “c z” in search field i would like the 3e line to match. cause they have the c on the name collumn and the z on the product collumn. but the any_of dont take on consederation the Array of search string (search string split by space) it why i loop in my exemple.
Then on summary each word on the search string need to match on row but is not neccessary match on same collumn.
Other thing i dont know how to manage that, i my exemple the “client” collumn is a link row. with a collumn name “Nom” how to search on the Nom [Client][Nom] string in that type of search ?
Thank for the advice on that issue, i appreciate the time you take to do complete answers is very welcome
EDIT: Just want to add dont hesitate to ask question if you dont understand my question, im far away form perfect on english
You can extend @alcampopiano’s example to get the desired matching behaviour on space-separated search terms.
First, we can take the code that builds a search query dictionary and move it into a separate function:
cols=app_tables.my_table.list_columns()
def any_column_contains(text):
query = q.any_of(**{d['name']:q.ilike(f"%{text}%") for d in cols})
return query
Then we can build this search query dictionary for each space-separated search term, and combine these dictionaries into a search iterator:
def search_customers(search_term):
rows = app_tables.my_table.search(
q.all_of(*[any_column_contains(term) for term in search_term.split()])
)
return rows
Hi @bridget and @alcampopiano i just test this out and it seem to work well thank i just change some thing because one of my field (Client) is a link table and i need to look on field name “Nom” did you have a bether way to make it work ?
cols=app_tables.credential.list_columns()
def any_column_contains(text):
dictionnaire = {d[‘name’]:q.ilike(f"%{text}%") for d in cols if d[‘name’] != “Client”}
dictionnaire[“Client”] = q.any_of(*app_tables.client.search(Nom=q.ilike(f"%{text}%")))
query = q.any_of(**dictionnaire)
thank for help, this issue is resolve but i gona wait if some one have some thing to add before closing it (duno if some one can add someting when closed ) .
Thank all !!
