Table search question

Anvil Q&A
1

Hi guys,
a question about anvil query syntax.

I have a games table with a field players linking to multiple rows of the players table.
Then, given a list of players, I am trying to build a query that returns all the games played only by a sub-set of this list.
So, to give sample data, this is my ‘games’ table:

chrome_2019-08-02_17-24-13
chrome_2019-08-02_17-24-13.png411×562 6.75 KB

I am looking for all the games played by only A, B and C players, but not necessarily all 3 of them.
So, the query should return games 1, 2, 4, 7.

I can’t figure out a way to write that in anvil’s query syntax.

Doing that would be perfect.

So, I tried to simplify the query by searching all the games played at least by one of A, B or C, in order to clean the results later.
My attempts led to this kind of code:

games = app_tables.games.search(game_players=q.any_of(players))

where players is the list of players table rows.
But this gives me only game 7 as a result.

Here’s the small test-app clone link to see tables and code.
https://anvil.works/build#clone:VLCFMV3HSEQLG44J=TNLZDEK5GKDBAMIXHCS63T2C

Any help is appreciated.

Thanks and BR

1 Like
2

Checking any_of() examples, note that it takes a series of arguments. You’ve given it just one: players.

Fortunately, Python has an easy way to turn that list into a series. Instead of
any_of(players)
you might try
any_of(*players)

4

Hi p.colbert
and thanks for your help.
I know that, and tried your suggestion.
Unfortunately, changing line 23 of Form1 source code to:

games = app_tables.games.search(game_players=q.any_of(*players))

would throw the error:

anvil.tables.TableError: Column 'game_players' can only be searched with a liveObjectArray (not a Row from 'players' table) in table 'games'

Converting the search iterable to list and then using the * operator:

players_list = list(players)
games = app_tables.games.search(game_players=q.any_of(*players_list))

still raises:

anvil.tables.TableError: Column 'game_players' can only be searched with a liveObjectArray (not a Row from 'players' table) in table 'games'
5

I think this might have to be done in two queries. At least, I think there has to be a way of avoiding players not included in the query (player D).

Also, in case it helps, since players is a multi-link column, I believe it has to be searched with lists (even a single row has to be inside a list).

This gets part of the way there but, doesn’t filter out player D (which is why I think there may be a composite query needed). I could be wrong though.

players = app_tables.players.search(name=q.any_of('player A','player B', 'player C'))
players = [[r] for r in players]
games = app_tables.games.search(game_players=q.any_of(*players))
6

You might end up doing this the long way, something like:

games = [ row
  for row in app_tables.games.search()
  if set(row.game_players) <= set(players)
]
1 Like
7

Thanks campopianoa,
I see the point, it must build the list of rows.
Your example works, and addresses the workaround, second-choice solution.

8

Ooohhh I didn’t know python has such set management.
This approach is the exact solution I was looking for, maybe a little slow though.
It gives exactly the expected result of games 1, 2, 4, 7.

Thanks a lot, I’ll try it immediatley.

9

Probably a little faster if you compute
set(players)
just once, before the loop.