Query specific value in multi-select enum attribute

[Justin Porter]

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?

[Guest]

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'
            else:
                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.

[Justin Porter]

Ah ok, so it's not actually storing as a list. Great, thank you!!!