Obtain the link between a Table and its linked tables

Anvil Q&A
1

Hi

I am trying to create a form that will enable the inserting of data into the db.

I am reading the tables, and get the fields, but the linked fields only have a reference to the table_id of the linked table and the column name, but not the column in the linked table.

How can I get the complete link between a table and its links.

thanks
Tobie

2

Are you operating directly on the row object from the app tables code? Or are you turning it into a different type, like a dict.

Is this code on the client or the server module?

3

Hi,

I am operating on the app tables directly.

One of the tables called Request_Data has a column that is called Tags, the Tags has a many relationship to a table called Tags to the column Tag.Tag_text

I want to create a form that reads the structure of the Request_Data (or any other tables). i can do this with get_columns. this returns a list of columns in Request_Data with its type etc. The relationships to other tables is showed as a tableid.

i want to add a dropdown to my form with a list of all the tag_texts in Tags but there is now way to get the links between Request_Data.Tag and Tag.tag_text… or this is what i am trying to achieve

So I am not trying to get the values of Tags for the Request_Data BUT i am trying to obtain a list of all the TAG values that Request_Data.Tag can be
Regards

4

Something like SQL’s SELECT DISTINCT, e.g.,

SELECT DISTINCT tag FROM request

? But for Anvil’s DB?

5

First I need to know which two columns are linked with each other in the tables. Once i know this i can query the distinct. But I cannot find a way to get the link between the two tables programatically.

How can i find out that Request.Data.Tag is linked to Tags.Tag_Text?

regards
Tobie

6

It isn’t. Anvil’s links point to an entire row, not to individual columns within the row.

Edit: If you want to “point” to individual columns, that’s more of a relational approach, not built into Anvil’s database. However, you could record that additional information yourself, as data or code, for your routines to use.

7

So that would mean that it is Impossible to build a form that reads the structure of a table and its relations and dynamically build a form to create a new entry.

8

Not at all. It just means that not all of the information to do that is built in to Anvil.

The rest must be supplied by your own code or data sources. An extension of Anvil’s built-in schema, if you will. You get to decide for yourself

  • what additional information your process needs
  • where and how to record it
  • when and how to retrieve it

within the existing system.

For example, you might decide to create one or more database tables to hold that information, and look up information from there, as needed. Or, you might decide that in-code lookup tables are better suited for your job.

Edit: Oracle Designer 2000 used the former approach. You could enter not only column and table names, but prompts and hints. You could define your own “data types” (“domains”, in database lingo), with their own validation rules. All readable at run-time, explicitly for use in building user interfaces.

9

thanks for your inputs. We could do this but…

If i have made the effort to define my database and provide the links to other tables (which i can see and change in the editor), but thenI do not have access to that information afterwords that is not a win, for our spesific use case.

Obviously one can fall back on the up/down link and use our own Postgres server wich is ok, but that sort of defeats why we wanted to use Anvil because its Database setup en management is super easy. Anvil is just so cool with some things!

That being said Anvil realy great but I think we were maybe a bit to excited about its capability. We realy wanted to move away from the heavy Django/FastApi - React/Vue space but ok, I think we understand what role Anvil can play and where it will fir in with our offerings.

Anyways, we will see how it pans out and post possible solutions like your proposals above if we find an acceptable solution

10

Anvil’s database schema is deliberately minimal, in order to provide flexibility, with as few constraints as possible.

It looks like you want a database with more internal detail than Anvil provides. So do I. Oracle kind of spoiled me in that regard: a dozen data-dictionary tables, standard, where I can look things up. And Oracle Designer, to add the details that those tables didn’t contain.

You can get any level of detail you want – but only some of that is going to be built in, no matter which database you use. For the rest of the details, it’s roll-your-own.

And in one of my Anvil works-in-progress, I’m doing exactly that. I define the schema entirely via TreeLine, so that I can quickly make the schema fit my needs. TreeLine saves in JSON format, which is trivial for Python to read!

Then I extract my data to a JSON structure (actually a Python dict), which fits neatly into an Anvil SimpleObject column, which the App can trivially read.

This isn’t perfect. As I discover new needs, I change the schema structure to suit. (TreeLine makes most such changes pretty easy.) That means I have to change the code in my “exporter” and in my App, to match. But once the groundwork has been laid, it’s fairly nimble.

The point is, it can be done, and with the right tools, it isn’t necessarily even hard.

11

This would not be fast (in python) but isn’t this possible?

uniq_tag = list({r['Tag']['tag_text'] for r in app_tables.Request_Data.search()})
12

Since we know that tag_text is a column in table Tag, this should go faster:

tags_used = set(row['tag_text']  for row in app_tables.Tag)

This will give you the set of tags that actually appeared in this table.

Of course, if there are additional, valid tags that were not actually used there, this will not find those valid tags. One would need to go to the original source, e.g., an enum or other listing, to get them.

13

Yeah, that is what I was trying to do that was ‘slow’, only getting the used tags, and not all possible ones.
(He asked for a list so I used a set comprehension and turned it into a list)
I’m kind of confused about what @tobie.nortje is trying to accomplish, but it’s definitely not impossible.

If you want to map table id’s to their names so you can use something like

getattr(app_tables, your_table_name_here).get_columns()

to programmatically get the column names of a linked table (again unsure of the reason for needing this)

You can use some ill advised private methods that I never recommend using but they still exist:
(It’s python, you can do whatever you want, even if its a bad idea)

#regular app tables map id's to names
import json
tables_id_map = {json.loads(getattr(app_tables, x).__dict__['_spec']['id'])[0] : x for x in app_tables} 
    
#accelerated tables map id's to names
tables_id_map = { getattr(app_tables, x).__dict__['_cap'].__dict__['_scope'][2]['id']:x for x in app_tables}

This would get you a dictionary that looks like:
{711728: 'Request_Data', 711729: 'Tag'}
Where the keys are the id’s of the tables in app_tables by name.

1 Like
14

Thanks! I’ve been looking for a way to get those numeric table ids! They seem to be globally unique, so they can be used to distinguish between tables of the same name that come from different databases.

They’re available in the full-project export .yaml file, but only for one of an App’s multiple databases. To get the ids from each of my databases, it takes code like yours, running in an Anvil Environment attached to that database.

They can also be found in row ids, i.e., links. Given a link, you can therefore find out which table it points at. (This may be close to what @tobie.nortje is looking for.) However, if you have a linked column, with no actual links (in any row), then you’d have to resort to other sources, to identify that connection. E.g., exporting and reading the schema section of anvil.yaml.

I’ve never caught a table id changing over time. But as an implementation detail, Anvil is not obliged to make them durable. Caveat emptor.