Q.any_of with multiple linking field

Anvil Q&A
1

What I’m trying to do:

Use q.any_of to filter based on a multiple linking field, to restrict results to rows that have one of a set of linked rows.

There are a number of forum posts about this, and the general answer seems to be that you have to put each filter item in a list of its own. Without that you get a q.all_of effect.

But putting each filter item into a list of its own also doesn’t return the expected results. Regardless of which filter items are chosen, it returns any row that has a non-None entry for the multiple linking field.

Should q.any_of work the way I and others have tried to use it?

Note that I know I can filter in Python using a list comprehension. The point of this post isn’t to solve the general problem of filtering, but the specific problem of using q.any_of on multiple linking fields.

What I’ve tried and what’s not working:

@anvil.server.callable
def get_posts(filters):
  # This fails to filter on the multiple link field correctly
  if filters:
    temp = [[r] for r in filters]
    return app_tables.posts.search(universities=q.any_of(temp))
    
  return app_tables.posts.search()

Clone link:
https://anvil.works/build#clone:3C4Z6A6P32QFVHNS=ASPO3LMOUCOH3ZWBNUYF2G37

1 Like
2

You need to unpack the items in your list:

q.any_of(*temp)
1 Like
3

I’d tried that at one point, it generates this error:

anvil.tables.TableError: Column 'universities' can only be searched with a List of rows from 'Universities' table (not a Row from 'Universities' table) in table 'Posts'
4

I’d need to see more of your code to be certain, but I don’t understand what your list comprehension does. Why not just unpack filters in your any_of call?

5

You can check out the clone link, everything’s there.

The docs say that a multiple linking field must be passed a list, so if I were to want to search for a single value it’d be universities=[rowfromuniversitytable].

Given that, it makes sense that a q.any_of would be something along the lines of q.any_of([row1], [row2], [row3]). That’s what the list comprehension does, putting each filter row into a list of its own.

But the version where I unpack temp to create that sort of query, q.any_of(*temp) gives the error I posted above, so that isn’t right.

Passing in a single list of lists as shown in the original post also doesn’t work. That somehow filters on non-empty fields.

Unpacking filters directly, q.any_of(filters) does an AND on the filters, not an OR.

At this point, I don’t know if using q.any_of on a multiple linking field should work, and I’m just not doing it right or there’s a bug, or if it shouldn’t work at all.

6

I’m not sure it’s the perfect solution but you could do something like this:

from itertools import combinations

def get_all_combs(iterable: list):
    for i in range(1, len(iterable) + 1):
        yield from combinations(iterable, i)

@anvil.server.callable
def get_posts(filters: list):
    if filters:
        temp = get_all_combs(filters)
        return app_tables.posts.search(universities=q.any_of(*temp))
7

That does seem to work, thanks!

9

Ok my last post didn’t quite get the intention here, but I think I figured out the right way to approach this. You need to pass the list of selected items as *args:

# in server
@anvil.server.callable
def get_posts(universities):
  if len(universities) > 0:
    return app_tables.posts.search(universities=q.any_of(*[[r] for r in universities]))
  else:
    return app_tables.posts.search()

# in form
def universities_change(self, **event_args):
    self.repeating_panel_1.items = anvil.server.call('get_posts', self.universities.selected)
2 Likes
10

It looks like something has changed in Anvil very recently. Owen suggested doing that yesterday, and when I tried it the exception about six posts up was thrown.

Today, though, it works as expected, which is great!

2 Likes