Query on a column with multiple links and other options

Anvil Q&A
1

What I’m trying to do:
I have 2 databases:

  1. sample_database
  2. results_database

Samples contain a column with multiple row links to results.

For my case I need now to get all samples that contain some specific result:

What I’ve tried and what’s not working:

    results = app_tables.results_database.search(q.fetch_only('sample_id'), study_id=study_id, application=q.all_of(sprep_assay_1_data))

Then use it to query the samples with found rows:

  samples_total = app_tables.sample_database.search(q.fetch_only('barcode', 'name', 'matrix', 'ext_barcode', 'volume', 'order_number',
                                                                 'location_storage', 'box_bag', 'position', 'status', 
                                                                 results=q.fetch_only('concentration', 'unit', application=q.fetch_only('name_short'))),results=q.any_of(*results))

But it’s not possible to query a multiple row link column like above:

anvil.tables.TableError: Column 'results' can only be searched with a List of rows from 'results_database' table (not a Row from 'results_database' table) in table 'sample_database'

Question 1: how to query a multi column link with search iterator?
Question 2: Now my samples are linked with multiple results. Is it smart to create a single row link back to the sample just to save myself the double query? So I would have multiple links to results on sample database and from result one link back. That would make it easier to show the right data in data grid and process it later, because I need to show some columns from samples with corresponding results in data grid.

2

Convert the search iterator to a list of rows, which is what’s required.

That seems reasonable for the convenience, especially if you’re using the backlink pretty frequently.

1 Like
3

Ok did some work to rewrite the code that transfer the data from excel with pandas to database.

Now I’m adding a samples and rows in 2 nested loops while saving the added rows and assigning/updating it right away. By doing so I’ve managed to get about 28 min for 30.000 samples + 30.000 results. So I lost about 7 minutes to create a an additional back link. Thank you for your help

1 Like