Filter CSV Fields _in & _nin don't work when multiple values present

[w0kyj]

Describe the Bug

Data Studio filters allow for Is one of(_in) and Is not one of(_nin) operations, however, they don't work when multiple values present in the field. Since it is an available option for the operations of this field type, the results should include any items with matching values in the csv list.

Existing/Related Issues:
There are some related tickets, but they were closed as fixed with the recent _contains PR or closed due to overlap with JSON filtering.
#12496
#11309

OPTIONAL:
It would also be great to provide selection of available values(at least the preset ones) like the current dropdown fields do.

Known workaround is the use multiple Contains(_contains) and Doesn't contain(_ncontains) with logical AND/OR, but is hard for Data Studio users to set up and use. Noted workarounds in other tickets:
#12496 (comment)
#11309 (comment)

To Reproduce

Create a collection with a tags csv data type field eg keywords
Add a few records to the collection

• record with a single tag value eg content
• record with multiple values eg data content OR content data
  Add a filter to the tags field in the collection layout
• eg keywords Is one of content

Before filter and showing the valid options:

Filter Example with missing records:

• Only shows one of three available records that include content in the tags

Directus Version

v10.10.5

Hosting Strategy

Directus Cloud

[hanneskuettner]

As correctly pointed out in #11309 (comment) the CSV fields are stored as simple strings in the DB and filter operations are plain string operations. In this context the _in and _nin operators check for the presence of the full string value in an array of possible values and is expected not to work in the desired way for this issue.

Effectively the "is one of" operation for a CSV value should read more as a "contains one of", since we want to check the overlap between the CSV values and the filter values.

Since on the DB side of things they are strings only and have no notion of being separate items, there is no nice ways of filtering the records that works reliably and efficiently.

As such we should think about maybe disabling the _in and _nin filters for CSV fields altogether and solely rely on a combination of and/or and contains in order to avoid confusion. I would appreciate input on that decision.

In the particular case of having tags, the cleaner solution from a DB standpoint would be to have a separate tags collection and utilize a M2M relation that can be properly filtered. For example with the M2M Tags Interface.