Malformed query in popups when MultiEnum fields are present

[piciuriello]

Issue

When you have one or more MultEnum field in a Popup, you select more values in one MultiEnum filter field and you have some other filter value within other fields, the where clause is built without brackets and you get OR and AND clauses mixed with unwanted results. This problem is not present in ListViews where brackets are put (look at method processSearchForm in include/MVC/View/views/view.list.php)

Possible Fix

Easy fix is in include/Popups/PopupSmarty.php in method _get_where_clause().

Change:

        if (count($where_clauses) > 0) {
            $where = '( ' . implode(' and ', $where_clauses) . ' )';
        }

to

        if (count($where_clauses) > 0) {
            $where = '( ' . implode(' ) and ( ', $where_clauses) . ' )';
        }

Steps to Reproduce the Issue

1. Put a MultiEnum field in a Popup
2. Select more values in the MultiEum
3. Set some other search filter
4. Launch the search
5. You will get unwanted results for the lack of brackets in query

Context

No response

Version

Bug still present in actual GitHub code (7.14.4)

What browser are you currently using?

Chrome

Browser Version

not significant

Environment Information

not significant

Operating System and Version

not significant

[johnM2401]

Hey @piciuriello !

Thank you for getting in touch and raising this.

I've been trying to replicate this locally on a 7.14.3 environment, but I'm not sure I've been able to do so Perhaps I've just misunderstood your issue?

I've got multiple conditions set on the "Accounts"->"Member of" popup, with one of them being a MultiEnum field with 2 options selected:

and my results appear to be accurate:

Would you perhaps have a screenshot/example of "unwanted results" given by the popup?

Is it that the MultiEnum field is returning records that contain "Average", in this example? Or perhaps something else?

Thanks again!

[piciuriello]

The unwanted results depends on the data set you have in your module.

The query created by method _get_where_clause() mix OR and AND clauses without brackets, but in SQL AND operator has precedence over OR operator, so in some situations you will have unwanted results. In you example the where clauses will be build something like this:

name like '%API%' AND testmulti like '%^count^%' OR testmulti like "%^sum^%" AND ...

This is semantically different from this (that should be the right syntax):

name like '%API%' AND (testmulti like '%^count^%' OR testmulti like "%^sum^%") AND ...

Because in first where clauses your results will have "API" in name AND "count" in testmulti, OR simply "sum" in testmulti (with or without "API" in name). In the correct where clauses your results will have "API" in name AND one of "count" OR "sum" in testmulti.

I hope I have clarified the point.