Querying Data-Table-A based on column-subsets in Data-Table-B, which it is linked to

Anvil Q&A
1

Dear Friends:

This is a contrived example so I can ask a question.

I have a Data Table, my_table, with these Columns:

  • locations_row (a Linked column to single Rows in Data Table: locations)
  • foo (a Text column)
  • bar (a Text column)

We’re really focused on locations_row Column here, and not the other two.

Now, the locations Data Table, in turn, has these Columns (all contrived):

  • Continent (Text)
  • Country (Text)
  • State (Text)
  • City (Text)
  • Town (Text)

So, to query my_table using a fully-qualified (non-duplicated) Row in the locations Data Table, I think I can do:

app_tables.my_table.search(locations_row = R # Where R is a fully-qualified Row in the locations Data Table.

But I also need to query my_table based on subsets of the Columns in the locations table, not necessarily just whole locations table Rows. (Although I do need that as well in other areas).

You see, I have the concept of scopes, where I need to query larger or smaller subsets of my_table based on varying columns in the locations Data Table
(which is linked to via my_table.locations_row Column).

I hope I got this example correct.
What is the syntax for this case?

Thank you!

EDIT: Maybe an example would be better:

app_tables.my_table.search(...)

How do I search based on, say, just, Continent and Country – or, say – State and City? (In other words, arbitrarily widen or narrow searches based on those columns).

PS: I normalized
my tables, so this is why this arises. :slight_smile: So now I need to determine how the any-value for this or that column in a linked-to-destination-table is expressed.

2

You currently need to search for the Locations rows, e.g.:

locations = app_tables.locations.search(...whatever criteria you have...)

And then search for matching rows in the other table:

rows = app_tables.other_table.search(Location=q.any_of(*locations))
2 Likes
3

Yes, thank you as always @jshaffstall That’s exactly right. I was just tinkering via the Server Uplink and came back here to report what you rightfully said. Thank you very much. Whew! Thank goodness that your answer wasn’t “You need to refactor the tables” (I have them nicely normalized) :grinning_face_with_smiling_eyes: Though not perfectly normalized.

1 Like
4

@jshaffstall

I have a quick follow-up question, by example.

How does one specify, say, any Continent (the only qualifier), yet ensure that the rows are deduped. Conceptually, almost like this:

  continents = set([Row['Continent'] for Row in app_tables.locations.search()])
  Rows = [app_tables.locations.search(Continent = c) for c in continents]
  for row in Rows:
      x = app_tables.my_table.search(row)
      # Do something with 'x'.

Thank you again.

PS: Actually, if there’s an opportunity to qualify the first search() with a “list” of sorts, that sounds better (eg, “North America”, “Asia”). I don’t yet know the search() syntax well to provide qualifications. I think there’s a Blog on queries. I’ll find it.

5

Definitely look at the blog post on the query syntax, it has lots of useful techniques. But in general, I think your general approach is right. You can use the query syntax to do a single location search, e.g. app_tables.locations.search(Continent = q.any_of(*continents))

1 Like
6

@jshaffstall Thanks brother. I read it last night and yes, q.any_of(...) is the operator that is applicable here. Sadly, I don’t think the query operator can be used for columns involving linked Rows. Here’s what I got just now:

"Column 'location_row' can only be searched with a Row from 'locations' table (not a anvil.tables.SearchIterator object
) in table 'my_table'".

EDIT: The offending query looked like this (again, translating to my contrived example here):

>>> app_tables.my_table.search(Continent = q.any_of(some_locations_row,))

I may have to add extra columns in the Data-Table being searched to essentially break-out some of the columns in the linked-to Row (if that makes sense).

7

Expand the iterator to a list of row objects with a *, like in the previous answer from @jshaffstall should fix this.

1 Like
8

Thank you @stefano.menci I was just re-reading @jshaffstall noticing the asterisk. Let me immediately go back and try some more. If no luck, I’ll share a code link. BRB

9

@jshaffstall @stefano.menci Adding the asterise (*) indeed fixed it. :tada: I had tried @jshaffstall recommendation, including the (*) (although my example above didn’t show that attempt), and it failed. REASON: I had added an extra parenthesis-pair around things, thinking I needed to manufacture a tuple() iterable to unpack, when the results-sets were already unpackable.

Since I had to work through my contrived example determine my error, here it is for future visitors:

https://anvil.works/build#clone:YP3SEU7JFAYX4O3B=YXH3NFCIVSYXNOFJ3PUCRBMK

Thank you guys!

1 Like