Filtering linked rows in data tables

Anvil Q&A
1

I think I’m going about this the wrong way, but I’m unwell.

I have two tables called “projects” and “tags”
“projects” has a multi-row link to “tags”
“tags” has a text column called “name”

I want to pull all project rows that have one or more tag matches in a list called “filters”, where filters contains the required "name"s.

Does that make sense?

If so, can I do it using a query or am I going to have to pull all rows and filter out with code?

2

Hey David,

Does this work?

tag_rows=app_tables.tags.search(name=q.any_of('tag_x', 'tag_y')) # or use * notation
tag_rows=[[r] for r in tag_rows]
projs=app_tables.projects.search(tag_links=q.any_of(*tag_rows))
3

I can’t make any variation of that work I’m afraid.
I’ll try again tomorrow - bit wood/trees tonight.

1 Like
4

I took that from a similar set up that I have where the table structure is (I think) the same as what you’ve described.

If I have time, I’ll do a little mock up app but it is entirely possible that I made a mistake.

1 Like
5

Hey David,

Here’s a little clone to demonstrate. I did make some mistakes above, and now they have been fixed.

Let me know if this is what you were after.

https://anvil.works/build#clone:VMD5VUY6IRYLJEGR=KYFFPFAELVRU4WEJ5F32I7IX

2 Likes
6

Yes, I think that does indeed do what i want.

Thank you!

7

You are very welcome!

8

Hello,

The clone link is no longer valid so I couldn’t do more homework on my own.

I believe that I’m trying to do a similar query against a multi-row linked column. In my Pricing table, an entry/row can be valid for multiple Products (the multi-row linked column). I’d like to search/query the pricing table by one or more of those Products and get back all matching rows. If any of the products I’m searching for are attached to that Pricing row, I want the results.

This works if I’m only trying to search for one product:
app_tables.pricing.search(Products=["Stantt MTO"])

However, trying to search for results that match multiple products does not work–It doesn’t return any rows.
app_tables.pricing.search(Products=["Stantt MTO", "5-Pocket"])

See the picture for more of a visual of what the data looks like. I’ve substituted the Product row with the values shown in the screenshot to make this easier to follow.

This also doesn’t return any results:
app_tables.pricing.search(Products=q.any_of(*["Stantt MTO", "5-Pocket"]))

Any ideas on how to modify my query to get the relevant rows?

Thank you!

Jeff

Pricing
Pricing1062×723 68.6 KB

9

According to the documentation, any_of does not take a list. It takes individual parameters.

If you need to feed it a list, then you may unpack it, e.g.,

my_list = ["Stantt MTO", "5-Pocket"]
app_tables.pricing.search(Products=q.any_of(*my_list))

See Unpacking Argument Lists.

10

Sorry, I had a syntax error in my last example, unpacking the list with q.any_of still doesn’t seem to work on linked rows. This is the error received when trying to do that:

TableError: "Column 'Products' can only be searched with a liveObjectArray (not a Row from 'Products' table) in table 'Pricing'"

11

Sorry, I should have taken a closer look at your table definition. Yes, a value in column Products is essentially a list of database table rows.

You can search using a “link to multiple rows” column. If you pass a list of row objects to search(), it will return only rows that link to all of those rows. (If you specify multiple rows, the order doesn’t matter - it matches anything that links to all of them.)

(From Links Between Tables. Emphasis mine.)

Unfortunately, this does an “all of” match. So you’ll need to build up a set of “any of” matches by doing it the long way: first create a list of matching products, then looping over each product in the list, to collect its matching Pricing rows.

12

Thank you for the answer! I was hoping that there was a more elegant solution than that.

Jeff

13

Treat it as theoretical. I expect to need something similar soon, but I haven’t actually built a solution around this idea.

At least Python sets will take care of eliminating the duplicates for you on each pass.

14

Hi @jeff,

Values in Multiple Link columns are represented as lists in Python. This means you need to query your Pricing table with a list of rows from your Products table:

# Construct a list of lists, each containing a row from the Products table
rows=[[r] for r in app_tables.products.search(name=q.any_of("Stantt MTO", "5-Pocket"))]
print(rows)
# Unpack the list of Product rows into your query against the Pricing table
matching_prices=app_tables.pricing.search(products=q.any_of(*rows))

This is equivalent to:

stantt_row = app_tables.products.get(name=("Stantt MTO"))
pocket_row = app_tables.products.get(name=("5-Pocket"))
# Query your Products table with Product rows represented as Python lists
matching_prices=app_tables.pricing.search(products=q.any_of(
  [stantt_row],
  [pocket_row]
))
3 Likes