How to query using a combination of columns

Anvil Q&A
1

Hi All,

Looking for some suggestions on the most efficient way to query my datatable using a list of dictionaries or combination of requirements.

Lets say I have a list of dict like:

my_data = [
    {"ProdName": "ProdA", "Location":"UK", ...},
    {"ProdName": "ProdB", "Location":"IT", ...},
    {"ProdName": "ProdB", "Location":"US", ...},
    {"ProdName": "ProdC", "Location":"UK", ...},
    ....
]

I would like to fetch all the db rows where the rows match the combination of ProdName and Location per row. So based on the list provided above, I would hope to get all the rows where (ProdName=ProdA & Location=UK) and (ProdName=ProdB & Location=IT) and (ProdName=ProdB & Location=US) and etc…

I’ve tried using q.all_of but can’t quite see how to get the syntax right or even if this is the right query operator to use for a multi-column combination search. I’ve considered the code below but doubt it’s even close to right.

db_rows = app_tables.my_table.search(
    q.all_of(*some_combo_of_queries?)
)

Thanks in advance,

2

I think you are on the right track, this is completely untested:

db_rows = app_tables.my_table.search(
    q.any_of( *[ q.all_of(**x) for x in my_data ] )
)

It is unstated in the docs, but I would intuit that there has to be a limit to this, if the query q. operators actually resolve to some part of a PostgreSQL query, that query will eventually have some sort of character limit. (65k characters? maybe? I don’t remember)
If they instead resolve to some ORM, maybe that would handle it better than I think.
Edit:
If you do run into a limit, you can replace this with a generator of

def combo_col_search_generator(list_of_searches):
  for col_search in list_of_searches:
    yield from app_tables.my_table.search(**col_search )

…it might be slower or faster depending on what you are going to do with the data once you have an iterator of row objects.

1 Like
3

Works brilliantly, thanks for the advice @ianb