In Anvil data tables, you need to first query Table 2 to get a list of rows that match the query, then do a query on Table 1 using that list of rows on the linked field. You can’t do it in a single query.
Why? You’re trying to find rows from table 2 where remove is false. That query can be done first, giving you a list of rows from table 2 where remove is false.
You then do the query on table 1, combining the resume = my_resume and linked_keyword=q.any_of(*the list of rows from the first query).
That’s the Anvil data tables equivalent of the SQL you posted.
I only have the resume to know what keys to filter. It doesn’t work the other way. The resume tells me what keywords to pick up and I use table 2 to see which ones I also need to remove. I can’t go the other way around.
Do some searching through the forums. You’ll find that what I’ve described is the way to do it in Anvil with multiple links.
If that doesn’t suit you, then you’ll need to use an external SQL database so you can use SQL queries the way you’d normally do in a relational db. There are tutorials on doing that you can review.
Also, I have a quick comment, a little out of topic. I think that this is not pythonic and, at least for me, fluent in python, not very readable:
I would change it to:
if key['in_resume'] and not key['keyword_link']['remove']:
The behavior is not identical, for example if you need to distinguish between False and None. But if it’s equivalent, it’s more readable, because it tells you what’s happening, instead of doing math that you don’t need and making you wonder why that math is there.
Thanks for the python comment. I don’t like writing it that way with all the brackets, so will use that in the future.
I have figured it out using the code I have above. Its pretty fast as all the data is manipulated in the client side
@jshaffstall I have had read that in the forums, but it doesn’t work for my use case.
However, I did get the above code to work for me for those who are reading in the future. The key was to change the row into a dictionary then I could modify and save in memory then I save it in a background task when the process was done.
The code could have be simpler if I wanted it to be slower and pull in and out of database but this way I also got what I needed and a faster user experience. The key is you can pull data from a linked table. modify it with dict and then save it. This requires the least amount of pulls from the datatable
This way, I only needed to pull the data out one time versus two
Basically I pull out Table 1, in table 1 is table 2 as they are linked. So I can iterate on the client.
To modify in the client browser I needed to change to a dictionary: dictionary dict(row). In the below code I am filtering out all keywords that were removed and then adding it to be changed in memory and the database.
It would be helpful for future people looking at this post if you could explain why, instead of just saying it doesn’t work. That way people will know if they fall into the specific situation that you’re in.
For example, “I can’t query table 2 first because there are a million rows in it, so I can’t convert all those rows to a list for the query for table 1”. Giving specifics helps future readers.
Otherwise future readers may get the idea that you can’t query based on linked fields, which is incorrect.
@KR1 , if you want to show support for the linked feature request, it’ll do more to like and post a reply on the feature request itself, rather than here. Activity on the feature request itself shows how much support there is for a feature by the community.