JOINs on Multilinked Rows

Anvil Q&A
1

Normally “multilinked row” joins, or one-to-many joins use an association table. However Anvil uses a text field that stores a JSON list of the table and row IDs. I need to join across this JSON list. What is the best way to do that?

2

To the best of my knowledge, Anvil’s simplified Table interface does not include SQL joins. On the other hand, nested iteration is pretty simple to do in Python. The outer loop would iterate over the “from” rows. The inner loop, one per row, would iterate over row’s Link column’s list of values.

Edit: added an explicit example. This is straight off the top of my head, so there may be bugs…

    for report in app_tables.reports.search():
        reduced_row = dict(report)
        del reduced_row('subreports')
        for subreport in report['subreports']:
            joined_row = { **reduced_row, **dict(subreport) }
            # do something with joined_row

“Best” is ever in the eye of the beholder, but the above (or something like it) should be effective.

3

If you really need joins, you can use SQL, available on dedicated plans.
Let me know if you need help with that, and I will give you a quick example.

4

Yes I need to do it with SQL. The joining row is not a foreign key but a string with a JSON representation of the linked rows and metadata. A SQL example would be great.

5

I could use a quick example too.