[Accelerated Tables] Getting Linked Column of Users Row

Anvil Q&A
1

Continuing the discussion from

I noticed that anvil.users.get_user no longer took ages so figured that the update was finally made.

However, the problem now is that when I actually want to fetch a column from a linked row of Users Table, it loads all other fields.

So if I try to do this

user_row['Linked']['Some Column']

This takes a really long time. Since it also fetches all other columns of user_row['Linked']

And I don’t think there is any way I can “tell” which columns I actually want unlike with standard get,search and get_by_id methods.

Is there something I can do about this or should I just wait for a fix from Anvil. Since no update
was actually provided on whether this feature was actually implemented or no, maybe it is not fully ready?

2

You can use fetch_ only, from the docs:

Explicit cache control

With Accelerated Tables, you can control which columns are loaded from a table search, using the new fetch_only search modifier.

For example, if you are querying the users table, but only need the values of the "email" and "enabled" columns, you can write:

from anvil.tables import query as q

rows = app_tables.users.search(q.fetch_only("email", "enabled"))

This will speed up the query, by only fetching the data you need. It is possible to access other columns in rows returned from this search, but it requires a round-trip to fetch that data from the database.

You can also control what data is fetched from linked rows, by using nested fetch_only specifications.

3

Yeah I am aware about that

However, I am talking about getting the user using anvil.users.get_user. If you read the discussion I shared the link to, you will have a better idea of what I am talking about

4

Any update on this? I am still unable to switch to accelerated tables because of this.

5

My understanding is that users.get_user doesn’t load and cache anything beyond the bare minimum. But then when your code gets to user_row['Linked'] it loads and caches the whole linked record, not knowing that you only intend to access ‘Some Column’. (The way the Python code works, it first processes user_row['Linked'] and then runs the ‘Some Column’ key lookup on the resulting object.)[1]

I would also like a fetch_only capability for users.get_user.

As a workaround meanwhile, you could rearrange the way your tables are organized to take advantage of the Accelerated Tables fetch_only option. Instead of storing the connection between a user and the linked record as a field in the Users table, you could instead add a ‘user’ column to the linked table and access it via app_tables.linked_table.get(user=user_row), or no?

[1] That said, I could also imagine them implementing a way to get merely a shallow reference to that linked row, without loading its content. But that doesn’t seem to be part of the plan.

edit: As a much easier workaround, something like this should work:

from anvil.tables import query as q
user_id = anvil.users.get_user().get_id()
user = app_tables.users.get_by_id(
    user_id, 
    q.fetch_only(Linked=q.fetch_only('Some_Column'))
)
6

I’ve started doing this. As a side effect, it allows any number (N) of linked_table rows to link back to the same user. 0 <= N, so if N is required to be a specific number (i.e., 1), it takes a bit of additional code (database query) to test that constraint. (Then you have to decide how to handle a constraint violation…)

3 Likes
7

Like @p.colbert, I do this too, and the main reason is that I share the same users table across dozens of apps, and I don’t want to add dozens of columns to the users table.

2 Likes
8

At the risk of going too far off topic, that has its own security implications, since if you send any of those rows to the user you’re sending the linked user rows for other users (which includes hashed passwords if they haven’t logged in with a third-party service). These days we can use only_cols with a client readable view, but it takes extra work to keep from exposing that data.

2 Likes
9

I maintain two separate tables for users. One is the main users table (no access in client) and another public users table consisting of the user data that is accessible to others (like their username, profile pic etc.)

If I provide a link to the main table from the public table, anyone can access private data (as mentioned by @jshaffstall ). Instead, a safer way would be to provide a link to the public table from the main table. And then call a server function that does this

app_tables.public_users.get_by_id(anvil.users.get_user()['Public_User'].get_id())

Otherwise, the other method mentioned by @hugetim also works well (although both can only be done on server)

from anvil.tables import query as q
user_id = anvil.users.get_user().get_id()
user = app_tables.users.get_by_id(
    user_id, 
    q.fetch_only(Linked=q.fetch_only('Some_Column'))
)

I will stick to one of these two methods for now until Anvil decides to implement this feature.

2 Likes