Building a "contains" operator with Query operators, Client Data Tables, Tabulator, ..i.e. searching via list intersection

Show and Tell
1

This took me a little bit to sort out, and I am mostly leaving this as a note to my future self, but hope doing so in public might be helpful to other folks.

I am basically tying to filter by a list of values containing any item in a search list. (i.e. intersection of the two lists.)

It was a little tricky, since the Query operators do not support a q.contains()… so have to link together a list of q.any_of’s.

The database has a multiple linked column - imagine a link to multiple site records. Each site is a physical location, might be an office, or kiosk in a mall, or a pop-up at a library.

A person can work at multiple sites.

I am trying to find a list of people can work at list of locations (perhaps town A, town B, town C)

So I need the intersection of these these two lists.

I am using Anvil Tabulator and with Client Tables.

Code Snippet (in client code)

   # site_search_list is constructed from all sites in Town A, Town B, Town C, ...
   #  Partner sites is a multi-link column in the database (and hence the client table/set_query
   # my tabulator is using client tables as the data items
   print([s['site_name'] for s in site_search_list])
   query_operator = [q.any_of(partner_sites=[x]) for x in site_search_list]
   self.tabulator_persons.set_query(q.any_of(*query_operator))

The result is a person (Joe) has partner_site = [“Town A Library”, “Town A Office”, “Town C Library”]

and you query for all sites in Town C = [“Town C Office 1”, “Town C Office 2”, “Town C Library”]

Joe will be returned/retained and displayed on the Tabulator Table, because “Town C Library” is on his person profile as well as a member of the Town C locations.

1 Like
2

Not sure if this is the exact condition you have been looking for but it is actually possible to search if a particular element is present in a list stored in data table.

If I have a column X in my data table with contains links to multiple rows and I want to get all rows where a row “A” is present in X, I can do this.

app_tables.my_table.search(X=[A]) #There can also be other rows present in the column X besides A and this will still work

Also works in Simple Object Columns

This might save you multiple server calls.

2 Likes
3

@divyeshlakhotia

Thanks for the feedback.

FWIW, I tried this and I could not get the results I needed.

What I found, is that syntax will only return when the two lists match and not the intersection of elements, or search list is a single element.

if I expand the contents of your “X” column

my_table.search([cars, trucks] = [trucks]) … returned row.

but
my_table.search([cars, trucks] = [trucks, boats]) … does not return row.

But I need that second example to return the row, based on the overlapping element trucks

So the above snippet basically expands the search list into a collection of single element queries (i.e. any_of([[cars, trucks] = [trucks], [cars, trucks] = [boats]])

4

Thanks for clarification. As suspected, I misunderstood your situation earlier.