Q.fetch_only questions

Anvil Q&A
1

I hope someone could explain this to me, as i have read everything i can find and still seem to be misunderstanding the q.fetch_only function, or maybe I am using it incorrectly?

In the following code I get a row from a table which has “current” being true and the account_org matches the server.session data (org)

@anvil.server.callable(require_user=True)
def get_account_org(): 
  org = anvil.server.session['current_org']
  data = app_tables.org_accounts.get(q.fetch_only('acc_ref', 'acc_name', 'vrn', 'eori', 'added'), account_org = org, current = True)
  print (dict(data))
  return data

I want to use this data to populate datagrid on the client side, however in future there will be potentially hundreds of rows collected through search rather than a single row through get, based on the two conditions of the account_org and the current columns, and each row links to potentially many other rows. So to save on processing and sending pointless data to the client, I want to only fetch the columns I need to populate in the datagrid (‘acc_ref’, ‘acc_name’, ‘vrn’, ‘eori’, ‘added’ )

But, and this is maybe my misunderstanding of fetch_only, the printed (dict(data)), and the data returned to the client, contains all columns, rather than just the ‘acc_ref’, ‘acc_name’, ‘vrn’, ‘eori’, ‘added’ requested.

{'acc_awrs': <anvil.tables.Row: awrs='ASDF000001234', current=True>, 'acc_eori': <anvil.tables.Row: current=True, eori='123456789000'>, 'acc_name': 'Test Co Limited', 'acc_ref': '1234', 'acc_vrn': <anvil.tables.Row: current=True, vrn='123456789'>, 'account_org': <anvil.tables.Row: org_name='CB Test', plus 2 more columns>, 'added': datetime.date(2023, 8, 3), 'archived': None, 'awrs': 'ASDF000001234', 'com_reg': None, 'current': True, 'eori': '123456789000', 'vrn': '123456789'}

you can see from the above that there are currently 3 columns that are linked and many other unlinked columns which it is pulling through that I didn’t request.

I know the fetch_only is supposed to only cache the columns requested and will round trip back to server to get the data if required, but I’m not calling for it.

If I remove one of the fetch_only conditions I would expect the datagrid to be blank in that column on the client side, but its populated with the correct data. Its still calling incredibly fast, even with the linked columns but when i have a few hundred in a search call its seems pointless to call all that data when I only need a little from each row.

Am I using this wrong and should I just use only_cols instead? I don’t know what the use for fetch_only is otherwise.

Many thanks.

1 Like
2

Hi @andrewbillcliffe,

When you call dict(data), that is in effect calling all of the data from the row in order to print it.

Regards,

3 Likes
3

Thanks Neeeco, that makes sense, so print(dict(data) is making a round trip back to the table to pull the rest of the data from the row and overriding the fetch_only condition.

Does that mean where I have a binding for ‘added’ in the datagrid on the client side, but omit it from the server callable,

@anvil.server.callable(require_user=True)
def get_account_org(): 
  org = anvil.server.session['current_org']
  return app_tables.org_accounts.search(q.fetch_only('acc_ref', 'acc_name', 'vrn', 'eori'), account_org = org, current = True)

that the datagrid then calls the missing ‘added’ column and populates it as well, in effect making a round trip to the server for the data that is missing?

Is there a way to print the cache to check what has been called when using fetch_only?

apologies for all the questions! Your help is much appreciated!

4

q.only_cols() makes only certain columns visible.

q.fetch_only() pre-caches only certain columns.

dict(row) iterates all the visible columns triggering one round trip per uncached column.

Unless you have a good reason to create a dict, you can use row objects directly.

Or, this is how I always do it, you can create a dict specifying which columns you want to include. This will allow you to add columns that don’t exist in the database, like calculated or formatted values.

3 Likes