DataTable Join queries

Anvil Q&A
1

I can’t seem to wrap my head around how to use DataTables, Search() and Queries to achieve what I could normally do with a SQL statement or an ORM.

Let’s say I have Table A -> Table B -> Table C where the -> is a foreign key.

How can I use DataTables, Search, Queries to only return back rows in Table A where TableC.Column1 = ‘some value’

Thanks

2

:thinking: there might be a better way but here’s an idea

Primary keys here would the equivalent of linked rows in anvil.

example:

  • teachers table has linked_student column
  • students table has linked_parent column

We want all the teachers who have a student who have a parent whose first name is bob

bob_parent = app_tables.parents.search(name='bob')
students = app_tables.students.search(linked_parent=q.any_of(*[[r] for r in bob_parent]))
teachers = app_tables.teachers.search(linked_student=q.any_of(*[[r] for r in student]))

When you do a linked row search with a query operator you need the arguments to be liveObjectArrays
(which I believe is just a table row inside a list)

some linked docs (none of which really satisfy the question but might support the underlying idea)

https://anvil.works/docs/data-tables/links-between-tables#links-between-tables
(here the docs show simple queries with liveObjectArrays and the idea above is an extension to this)
e.g.

jane_smith = app_tables.people.get(Name="Jane Smith")
friends_of_jane = app_tables.people.search(Friends=[jane_smith])
# [jane_smith] is a liveObjectArray

And the a go to for query searches - but it doesn’t cover linked rows.

2 Likes