Jump to content

Query specific value in multi-select enum attribute

Justin Porter

Recommended Posts

I'm trying to query for a specific value that exists in a multi-select dynamic enum. Here's the value of the attribute:

In [10]: task['custom_attributes']['userDynEnum_Coordinator']
Out[10]: [u'greyc', u'jennifera']

If there's only one value selected in the enum, I can use this query:

'Task where custom_attributes any (key is "userDynEnum_Coordinator" and value is "greyc")'


But with multiple values, that doesn't work. I can use this:

'Task where custom_attributes any (key is "userDynEnum_Coordinator" and value like "%greyc%")'

But that's dangerous because if "grey" is ever a value, it would return that as well, and I don't want it to.


I've also tried these query options and they throw errors:

'Task where custom_attributes any (key is "userDynEnum_Coordinator" and value any ("greyc"))'
'Task where custom_attributes any (key is "userDynEnum_Coordinator" and value has ("greyc"))'
'Task where custom_attributes any (key is "userDynEnum_Coordinator" and "greyc" in value)'


Any suggestions on what I could do here?

Link to comment
Share on other sites

Hi Justin,


Since we store the multi-select enumerator value as a ", "-delimited string, you'd need to search the four possibilities: Your value is the only value, it's the first value, last value or a middle value. That snippet would look like:

custom_attributes any (key is "userDynEnum_Coordinator" and (value is "greyc" or value like "greyc, %" or value like "%, greyc" or value like "%, greyc, %"))


Since that's tedious and error-prone, I wrote this:

import itertools

def subquery_builder(*args):
    perms = itertools.permutations(args)
    for perm in perms:
        spacers = itertools.product((None, '%'), repeat=len(args) + 1)
        for spacer in spacers:
            if all(elem is None for elem in spacer):
                op = 'is'
                op = 'like'
            condition = ', '.join(
                e for sublist in itertools.izip_longest(spacer, perm)
                for e in sublist
                if e is not None)
            yield '{} "{}"'.format(op, condition)

def query_builder(attr, *args):
    subq = subquery_builder(*args)
    return '({})'.format(
        ' or '.join(
            '{} {}'.format(
                attr, item
            ) for item in subq

def custom_attr_multi_search(key, *args):
    return 'custom_attributes any (key is "{}" and {})'.format(
        key, query_builder('value', *args)

I don't recommend using it with more than a few args.

Link to comment
Share on other sites

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...