Iterating over many rows in linked table

Anvil Q&A
#1

I have a table that references itself in a one to many relationship, i.e. each row can be related to plenty of rows in the same table.

I need to find all related rows to the row selected by the user. here is what I tried

for table_accessory in app_tables.accessories.search():
      for accessory in table_accessory['requires']: # here is where the error points to.
        if accessory_id == accessory.get_id():
          self.add_accessory(accessory.get_id())

yet I get an error

TypeError: 'NoneType' object is not iterable
at order_mainframe, line 43

I would rather be able to do a search on related contains accessory_id

Thanks

#2

It looks like, for at least one row in app_tables.accessories, column requires is empty (None).

You could check for this before the second for. (E.g.,
if table_accessory['requires'] is not None:
.)

Or add conditions to the .search() call, so that it skips over the empties. See the Querying Data Tables cookbook for examples.

#3

@yanky

As @p.colbert says, your second loop is trying to iterate over a None.

I would suggest just searching for the target row (based on the ID derived from the user’s selection), and using that row as a filter in your accessories table.

In other words,

  • grab the single row that corresponds to accessory_id (let’s call it “target_row”)
  • return rows from table_accessory where the requires columns contains “target_row”
target_row=app_tables.accessories.get(accessory_id='1')

# notice that you can pass a list to search within a multi-linked row
related_to_target=app_tables.accessories.search(linked_accessories=[target_row])

Here is what my self-referencing table looks like:

sc
sc.png605×502 18.1 KB

I can then show that row 1 and 3 are related to the target row:

for related_row in related_to_target:
  print(related_row['accessory_id'])

# output
1
3

Here is an app to demonstrate:
https://anvil.works/build#clone:Z7UAKZQRMFXO6KNU=3EBPLBN4R6V3NFJZ57ZEX643

I hope I’ve understood you correctly and that this helps.