Bug: Filtering multivalue fields with with "negative" comparator

juliusverrel commented 4 years ago

"Negative" comparators (!=, !~) don't seem to work in the desired way on multivalue fields. A multi-value field F with value "A, B, C" is listed in an aggregation filtered with "f != A". My preferred interpretation of != in this case would be "is not equal to any of the values"; the current implementation seems to be "there is some value to which it is not equal".

I think a small change here would solve the issue, setting $op="AND" for negative comparators.

auto-comment[bot] commented 4 years ago

Thank you for opening this issue. CosmoCode is a software company in Berlin providing services for wiki, app and web development. As such we can't guarantee quick responses for issues opened on our Open Source projects. If you require certain features or bugs fixed, you can always hire us. Feel free to contact us at dokuwiki@cosmocode.de for an offer.

Chris75forumname commented 1 year ago

I can confirm this bug! Unfortunately, the above proposed solution by juliusverrel does not work.

I coded my own negative filter for struct aggregation tables in /dokuwiki/conf/userscript.js, like so:

setTimeout(function() {                                                                  // handles each struct aggregation table
    jQuery("div.structaggregation th[data-field='schema.fieldname']").each(function() {  // find column by schema.fieldname
        $flt_index = jQuery(this).index() + 1;                                           // get the index of the column
        jQuery(this)
            .closest("div.structaggregation")
            .find("td:nth-child(" + $flt_index + "):contains('filterword')")             // filter the column by filterword
            .parent('tr')                                                                // get the whole row ...
            .remove();                                                                   // ... and remove it from the table
    });
}, 300);

Works like a charm. Just fill in your schema, fieldname and filterword in the code. For more negative filtering just repeat this code (with different parameters) as many times as you like. (Be aware that all parameters are case sensitive, e.g. fieldname should be given including uppercase letters and spaces, if any. If necessary this code can be expanded to only filter certain tables and/or to only work on certain pages.)

Cheers! 😀

splitbrain commented 1 year ago

Here are some analysis results....

@juliusverrel approach doesn't work because it handles multiple user supplied values. The bug however occurs when there are multiple values in the database (because the field is marked as multi-value). The problem lies in how multi values are currently queried.

Let's assume a dataset like this, with field2 being a multi value.

field1	field2
first	green, blue
second	green, red
third	blue, black

Because multi values come from a different table, that table is joined to the main table for applying the filter, then the whole result is GROUP BY'd using GROUP_CONCAT to combine the single values again. Basically it looks like this:

field1	field2	comparison
first	green, blue	green
first	green, blue	blue
second	green, red	green
second	green, red	red
third	blue, black	blue
third	blue, black	black

When you now say field2 != green you get this:

field1	field2	comparison
first	green, blue	blue
second	green, red	red
third	blue, black	blue
third	blue, black	black

Finally the grouping throws away the comparison part and a DISTINCT will only output unique results:

field1	field2
first	green, blue
second	green, red
third	blue, black

This explains the current behavior.

The comparison setup is in https://github.com/cosmocode/dokuwiki-plugin-struct/blob/4b6f5fd9c1f83af36c068bfb531518f67bf977a7/types/AbstractBaseType.php#L371

The join is added in https://github.com/cosmocode/dokuwiki-plugin-struct/blob/fb2252eac3382a79b6feb00e14ea0d13e5b4bfd4/meta/Search.php#L493-L500

Now that we know what happens, how can we fix it? Unfortunately it's not trivial. When doing exact comparisons we would probably need to do a NOT IN (subselect) while a LIKE filter would need to match against the GROUP_CONCAT result...

An alternative might be to always use some clever regexp matching against the GROUP_CONCAT result. This would be closer to the JS hack suggested by @Chris75forumname above. It would not take advantages of any indexes. Though I'm not 100% sure if we even have any on the value column of multi data tables...

cosmocode / dokuwiki-plugin-struct

Bug: Filtering multivalue fields with with "negative" comparator #512