First off, I love the new query operators. They’re allowing me to do filtering on results really easily.
But, I have a situation I haven’t been able to work out how to do. I don’t know if that’s because the query operators don’t support it yet, or I’m just not clever enough to see how to apply them.
I have a multiple links field in my Users table to the Activities table. This represents activities the user has completed. What I want is a query that returns all activities a user has yet to complete.
Right now I have:
user = anvil.users.get_user ()
activities = app_tables.activities.search()
I’d like to use q.none_of to say that the activity shouldn’t match any of the activities from user[‘activities’], but the only field I have in the activities table that uniquely identifies it is the auto-generated id itself. And I can’t seem to search based on id.
Is what I’m trying to do possible using query operators?
I’m currently pulling all the activities and then removing the ones that show up in user[‘activities’]. That works, but requires pulling all the data and then filtering it.
Would something like this work for you? I’m still getting used to the query operators as well so there may be better ways.
@anvil.server.callable
def return_non_activity():
user=app_tables.users.get(name='anna')
user_act=[r['activity'] for r in user['activities']]
# gobble up those positional arguments with "*"
no_acts=app_tables.activities.search(activity=q.none_of(*user_act))
for r in no_acts:
print(r['activity'])
# returns activities that Anna does not do
"jumping"
"swimming"
clone:
https://anvil.works/build#clone:BY6VXUHAIROT3IAR=5XPRXCPJN4UU7QSYLW2MMBLV
2 Likes
That’s really, really, close, thanks!
If I add in a field that uniquely identifies each activity (I can’t depend on the name to do it), that would work. I could generate a UUID for each activity as a sort of pseudo primary key.
Is there any way to do that search using the built in row id? When I try to search by id I get a message about there not being a field named id in the table.
Okay, great!
Hmm, without the ID column being physically present in the table, I’m not sure it is possible to use the query operator (others please correct me if I’m wrong). I tried:
row_ids=[r.get_id() for r in user['activities']]
no_acts=app_tables.activities.get_by_id(activity=q.none_of(*row_ids))
But it returns None. I’m unsure if I’m just doing it wrong, or if it is impossible.
What about first doing something along these lines to add activities (assuming an empty table with a “name” and “ID” column)?
- in a transaction, add a row to the activity table and give the activity a name (name is not unique)
- then, immediately fetch that row back and get its ID with
row.get_id()
- update that row to explicitly add that ID to the ID column
@anvil.tables.in_transaction
def add_actitivty_and_ids():
app_tables.activities.add_row(activity='pontificating')
row = app_tables.activities.get(ID=None)
row.update(ID=row.get_id())
After a couple runs:
I’m stretching my brain a bit here, so I could be missing a simpler solution. Presumably the query operator should work against the explicit IDs now.
1 Like
Are you on a plan where you can use SQL? If so, I’d be tempted to use a LEFT JOIN on the _id field.
Not on a plan with SQL, unfortunately.
I’m really hoping that the query operators either can work on the id in some way that isn’t obvious, or that the feature gets added at some point.
In the meantime, it looks like adding another field to be the unique identifier is the best bet. That’s a cool trick of adding the id field in as a regular field, I think I’ll adopt that.
Hi there!
Yes, this is a good feature request - I’ve added it to our issue tracker 
In the meantime, I’d recommend using a comprehension; something like:
activities = [activity for activity in app_tables.activities.search()
if activity not in user['activities']]
2 Likes