Iterate over multiple Data tables

Anvil Q&A
1

What I’m trying to do:
I’m building an Advanced search UI.
I want the user to use checkboxes to pick which Data Tables could contain relevant results. Each checkbox has a tag “table_name” - a string with the data table’s name. Once selected, the user can click the Search button, that does this:

def search(self, **event_args):
    selected_tables = []
    for box in self.checkboxes :   # list of Anvil checkbox elements filled in form init
        if box.checked :
            selected_tables.append(box.tag.table_name)    
    results = anvil.server.call('search_in_tables', selected_tables, query=self.search_box.text)

On the server side I want to programatically search within these tables, ideally in this fashion:

@anvil.server.callable
def search_in_tables(table_names, query) :
    results = {}
    for table in table_names:
        results[table] = get_matching_rows_from_datatable(table_name=table, query=query)
    return results
    
def get_matching_rows_from_datatable(table_name, query) :
    all_rows = app_tables.table_name.search()
    # search in all rows algoritm - not the issue

The (expected) error I get is that there’s no table called “table_name”… Any ideas how I could do this - search in different tables based on a list of strings with their names?

2

where table_name is a string-valued variable.

If you are using the Accelerated Tables option, you can access a table using subscript notation:

my_table = app_tables[table_name]

Otherwise, there is an undocumented syntax you can use:

my_table = getattr(app_tables, table_name)

If table_name isn’t found, that will raise an Exception. If you’d rather deal with that case another way, then

my_table = getattr(app_tables, table_name, None)

will set my_table to None, in that case.

2 Likes
3

Thanks, this is what I was looking for.

It feels like this should be documented though, I don’t think it’s that uncommon to search in a list of tables. Anyway, thanks again for your help :slight_smile:

4

My humble opinion is that documenting this would be encouraging a bad practice.

Ok, ok, calm down!
I know, I know, this is a legitimate way to work. There is nothing wrong with it!

But… I have hundreds of apps and hundreds of tables, and I have never done it. I know, the fact that I have never done it doesn’t mean that no one should do it.

I have seen this request many times in the forum, and after the reason for doing this was explained, turned out to be bad practice. I know, the fact that it’s usually a bad practice doesn’t mean it’s always a bad practice.

Usually rather than doing app_tables[table_name].search(this=that) is better to do app_tables.big_table.search(this=that, table_name=table_name).

In other words, having two tables with the same set of columns is just looking for trouble and making your future app maintainance more difficult.

1 Like
5

i.e., where big_table aggregates (and replaces) all of the little tables, with an additional column, table_name, which identifies the little table each row came from. A view can be defined on big_table, to function like any little table.

I’ve heard of setups where each customer gets their own little table, but each of those must be created manually. @stefano.menci’s approach eliminates that manual step.

That said, there are rare occasions where one may want to layer over Anvil’s objects and routines with higher-level functions, e.g., functions respecting (and enforcing) foreign key relationships (on ___ cascade/restrict/...). For these functions, the table name may necessarily be a variable or parameter.

1 Like
6

It should be noted though, to use the subscription bracket syntax with app_tables, you need to be using the Accelerated tables. I will say that I have used similar levels of abstraction to use the same creation/update/reading functions for different datatables and it works well.