Allow for nested subqueries in Data Tables query API

Feature Requests
#1

Hi!

I often find myself trying to search the data tables based on the value of a linked row. It would be great to be able to do this using the Anvil query API.

For example, say I have an app with a Users table and another Comments table with a single row reference to that Users table. Now (for some reason) I want to search my Comments table for all users that have a gmail address. I’d like to be able to:

comments = app_tables.comments.search(users = q.any_of({'email': q.i_like('%@gmail.com')})

This is analagous to the searching of a dictionary simple object listed in the documentation, here: https://anvil.works/blog/querying-data-tables.

This would be an intuitive way to handle the problem also discussed here: How to get values from a nested multi-link table?

Alternatively it feels like we should at least be able to search using a Search Iterator. For example:

user_search = app_tables.users.search('email'=q.i_like('%@gmail.com'))
comments = app_tables.comments.search(users = q.any_of(user_search))

I know that I can achieve this using list comprehension (or just *user_search), but in both cases I have to load the entire search object, and give up the laziness of the search iterator. This is devastating to performance as the database grows, so my request is specifically on how to achieve this while pushing the actual work to the database (which will be very good at this job!).

10 Likes
Querying based on linked table values
Query linked tables with filter in second table
Performance Issues Working with Anvil Data Tables and Row Iterators
#2

I don’t think this is possible given the current query system, but wanted to check to see if anyone knew how to do it.

I’d like to query based on fields in linked tables. For example, if I’m querying an order items table and it has a link to an order row, I’d like to be able to query based on the date of the order. Something like:

app_tables.order_items.search(
  order['date']=q.less_than(
    datetime(day=14, month=12, year=2017),
  )
)

That doesn’t work.

I have a couple of workarounds in mind, but they either use up additional space in the data tables (denormalizing to store the date in the order items), or additional time on the server (fetching the order rows so I can query order items using q.any_of).

Does this feature exist and I just can’t work out how to use it?

2 Likes
#3

That’s where I might resort to sql directly on the tables. (as long as your plan allows it).

#4

Sadly, my plan does not, and won’t until I can get this project far enough off the ground to generate some revenue.

I’ve been toying with the idea of going to an external cloud database instead of data tables. Data tables are very convenient, though.

#5

I’ve found your second approach works well too. The extra server time is generally less than to time for any client/server transfer.

#6

Lazy search iterators are a big win for data tables. Can you emulate something like that for a data grid to use when you’re going to an external database?

1 Like
#7

@danbolinson, @jshaffstall,

I agree this would be a great addition - this FR is on our list! :slight_smile:

5 Likes
#8

Did this feature ever get implemented?

2 Likes
#9

That would be a great option to have. Now even more, because we have the new accelerated tables. Are there some plans to implement nested subqueries?

2 Likes
#10

Would love to see those suggestions implemented!

1 Like