Limitations of q.any_of with booleans

Anvil Q&A
#1

attempting to use q.any_of to help with building queries on the fly for listing users that are/aren’t enabled, have/haven’t confirmed their email address, and getting an unexpected error.

Server code:

    @anvil.server.callable
    def admin_user_search(query, enabled=None, confirmed_email=None):
      anvil.server.call('admin_check')
      
      if enabled is None:
        enabled = [True, False, None]
      else:
        enabled = [enabled]
      print('admin_user_search: enabled = ' + repr(enabled))
      
      if confirmed_email is None:
        confirmed_email = [True, False, None]
      else:
        confirmed_email = [confirmed_email]
      print('admin_user_search: confirmed_email = ' + repr(confirmed_email))
      
      if query not in ('', None):
        query = '%' + query + '%'
        users = app_tables.users.search(
          tables.order_by('signed_up', ascending=False),
          q.any_of(
            first_name=q.ilike(query),
            last_name=q.ilike(query),
            full_name=q.ilike(query),
            email=q.ilike(query),
            mobile_phone=q.ilike(query)
          ),
          enabled=q.any_of(*enabled),
          confirmed_email=q.any_of(*confirmed_email)
        )
      else:
        users = app_tables.users.search(
          tables.order_by('signed_up', ascending=False),
          enabled=q.any_of(*enabled),
          confirmed_email=q.any_of(*confirmed_email)
        )
      return users

Error:
anvil.tables.TableError: Column 'enabled' can only be searched with a bool (not a string) in table 'users'

This occurs even though the list contains True, False, None, not “True”, “False”, “None”. I’ve tried it with [True, False] as well, same issue.

Shouldn’t this work? This syntax works just fine with text fields.

#2

Hello,

Just so I understand correctly in case I can help; are you using q.any_of on a boolean column with a list of bools as the query?

If so, I would expect that to always return everything since any_of would select for both True and False. I am probably missing something here.

On the other hand, if you passed a list of values that contained strings, I would expect the error you reported above (e.g., [True, "False", False]).

#3

A boolean column can have any one of three values:

  • None (its initial value)
  • True
  • False
1 Like
#4

I am not using strings. A boolean query can have something like ‘enabled=True’, or ‘enabled=False’, or, to @p.colbert 's point, ‘enabled=None’. In none of these cases would you succeed with something like ‘enabled=“True”’, because enabled is not a string, it’s a boolean. Thus my example code has:

enabled = [True, False, None]

and not

enabled = ['True', 'False', 'None']

the issue seems to occur with the q.any_of(*[some list]) support for booleans.

A query without q.any_of, like

enabled=True

works just fine.

Since there is no way to construct a query string and pass it to app_tables.[table_name].search(), as if it were as SQL query, setting my search term to a list of desired values simplifies the number of hard-coded query commands i have to build and maintain. Unfortunately, it doesn’t appear that q.any_of(*[some_list]) work with booleans the way it does with text fields or row reference fields.

How are other people handling building more complex search/filter queries for data grids?

#5

Basically, what I’m doing is if enabled is set to None, then turn it into a search for any valid value of enabled ([True, False, None]), otherwise (i.e., if it is True or False), pass the value through to the actual query being run.

In no case am I quoting “True”, or “False”, or “None”, and you are correct. The error is one I would expect to see if I were quoting those values and turning them into strings.

I think q.any_of(*[some_list]) support is broken.

#6

Thanks for clarifying.

This seems to work for me. That is, something like,

valids=app_tables.my_bools.search(bools=q.any_of(*[True, None, False]))

does not give me the error you are experiencing. It just returns everything.

https://anvil.works/build#clone:W7L7HCV3GCOR5HT2=6U7IAID72J66NR6LFFJTCSRB

1 Like
#7

Solved, found support for query objects being assembled and got the following working:

@anvil.server.callable
def admin_user_search(query, enabled=None, confirmed_email=None):
  anvil.server.call('admin_check')
  
  if enabled is None:
    enabled = q.any_of(
      q.any_of(enabled=True),
      q.any_of(enabled=False),
      q.any_of(enabled=None)
    )
  else:
    enabled = q.any_of(enabled=True) if enabled == 'Yes' else q.any_of(enabled=False)
  
  if confirmed_email is None:
    confirmed_email = q.any_of(
      q.any_of(confirmed_email=True),
      q.any_of(confirmed_email=False),
      q.any_of(confirmed_email=None)
    )
  else:
    confirmed_email = q.any_of(confirmed_email=True) if confirmed_email == 'Yes' else q.any_of(confirmed_email=False)
  
  if query not in ('', None):
    query = '%' + query + '%'
    users = app_tables.users.search(
      tables.order_by('signed_up', ascending=False),
      q.all_of(
        q.any_of(
          first_name=q.ilike(query),
          last_name=q.ilike(query),
          full_name=q.ilike(query),
          email=q.ilike(query),
          mobile_phone=q.ilike(query)
        ),
        enabled,
        confirmed_email,
      )
    )
  else:
    users = app_tables.users.search(
      tables.order_by('signed_up', ascending=False),
      q.all_of(
        enabled,
        confirmed_email,
      )
    )

  return users
#8

@alcampopiano thank you!

valids=app_tables.my_bools.search(bools=q.any_of(*[True, None, False]))

is perfect!

1 Like
#9