🐛 Bug: Filtering MultiSelect in more than basic way

[bannmann]

Environment

NocoDB used as docker : true
NocoDB version : 0.11.29
Database used in NC_DB URL : pg
Project was created by clicking : New Project by connecting to external database
Database on which spreadsheet is created : pg
OS on which NocoDB is running : Linux
Node.js version if running as node : v12.22.1
Database version : 13.4

Steps To Reproduce

1. Add a column named tags of type "MultiSelect" to a table, add options Foo, Bar and Quux
2. Add rows with the following tags values:
   • no values (row ❶)
   • only Foo (row ❷)
   • only Bar (row ❸)
   • only Quux (row ❹)
   • Foo and Bar (row ❺)
   • Foo and Bar and Quux (row ❻)
3. Try to add a filter so that only rows which include Foo appear (rows ❷, ❺ and ❻).

Expected behavior

There should be an easy way to filter rows based on MultiSelect fields. Using the provided filter operators is equal or is like, this does not seem possible, or at least it's not obvious how to do that.

Suggested solution

The possible filter operators should change when I select a MultiSelect field:

• contains all of
• contains any of
• does not contain any of (alternative phrasing: contains none of)
• does not contain all of (included for completeness, maybe not useful/desirable)

The existing filter operators is (not) null and is (not) equal should be kept.

However, the following operator families should be hidden for MultiSelect fields. They do not make much sense for such fields and distract from the "correct" operators.

• is (not) like - given that LIKE is string-based, how would one specify filter values?
  • option IDs? not even exposed in the UI
  • option labels? my option label could be a ? or _, breaking the usual LIKE syntax
• less/greater than (or equal) - comparing by inherent ordering is impractical as the ordering of MultiSelect options seems to be fixed as "creation order"
  • Full disclosure: I could imagine a use case for these operators if the filter criterion is a number: "all rows that have more than one tag, no matter which". But that is probably covered more intuitively by listing a separate filter field option (a "pseudo field") called number of tags, which would also allow things like number of tags is equal 1.

Example filters:

• where tags contains all of Foo
  • result: rows ❷, ❺ and ❻
  • solution for step 3 above
• where tags contains any of Foo, Bar
  • result: rows ❷, ❸, ❺ and ❻
• where tags contains all of Foo, Bar
  • result: rows ❺ and ❻
• where tags is equal Foo, Bar
  • result: row ❺
• where tags is equal Bar
  • result: row ❸
• where tags is not null
  • result: rows ❷, ❸, ❹, ❺ and ❻
• where tags is null
  • result: row ❶
• where tags does not contain any of Quux
  • result: rows ❶, ❷, ❸ and ❺

[distresscode]

This has been open for a while. Can someone please take another look at it? Thanks!

[github-actions]

Fixed in v0.104.0.