Provide operators 'In' and 'Not in' when filtering a list

[Johan Vallon-Christersson]

Expand the list of operators supported by the free-text filter. Add operators 'In' and 'Not in'.

When these operators are used the user should be able to input a (comma?) separated list of strings.
For example: Gene symbol In BRCA1,ESR1,ACTB would list items matching either of these three gene symbols.

The list of strings could include wildcards.
For example: Gene symbol In BRCA%,ESR1,ACTB would in addition list BRCA2, BRCA3 and so on.

[Nicklas Nordborg]

It would be great to have this functionality. The syntax needs to be discussed. I think , could work as a separator, but we probably need a single-character symbol prefix to indicate the IN function. The same symbol can be used with ! to indicate NOT IN. % should be supported as a wildcard in the values.

[Nicklas Nordborg]

I have been thinking a little bit of this problem. My proposal is that the regular operations (=, <> and !=) are made more intelligent. Ie. if a list separator, (comma?) is found in the expression this will cause IN or NOT IN to be used in the query instead of = and <>.

To support literal query expressions where % and , are not treated specially we add == and !== operators that always searches for an exact match.

What about the list separator symbol? Is comma a good symbol or should we use something that is not so common in texts? A pipe symbol (|) for example?

[Nicklas Nordborg]

I though that this should not be too hard to implement, but I was wrong. The code that is parsing the input and generating the HQL restrictions are split into several places (PropertyFilter.java, Operator.java, ItemContext.java, Base.jave). As it is designed the Operator class only supports simple two-valued operations like: value1 OP value2. In other words, it is not trivial to extend this to the case where one of the values is a list of values. We could move this up one layer and implement it as a special case, but then we have to do this for several types of queries, ie. regular queries, annotation queries, dynamic queries or any other type of query that we may add in the future. This solution is not future-proof.

Another problem is that the structure of the query is different if wildcards are used or not. If wildcards are not used we can use the IN operator: name IN ('nicklas', 'martin'). If wildcards are used we must expand it to multiple LIKE operations and combine them with OR: name LIKE 'ni%' OR name LIKE 'ma%'. It may be better to make the first case look like the second by doing: name = 'nicklas' OR name = 'martin'

[Martin Svensson]

(In [4058]) References #762 Added four new Operators and started to implement them in the web client.

[Martin Svensson]

(In [4067]) References #762 Updated PropertyFilter class to use the new operators.

[Martin Svensson]

(In [4071]) References #762 The functionality is now completely implemented. It needs to be documented before this ticket can be closed.

[Martin Svensson]

(In [4072]) Fixes #762 Provide operators 'In' and 'Not in' when filtering a list