we encountered a problem when building searches using the "in" operator. While this should work as if making multiple where statements connected with or, we noticed that on one of our custom fields which is a drop down with multiple values (all text only) we get results as if the query was using AND instead of the OR which is normally used with IN.

Example: Field "X" contains a lot of text-only options, multivalue dropdown. I build a search like this: Contacts where X IN A,B Now i expect to get results where my contact has either A OR B in X. But i only get thos who have A AND B in Field X.

So we checked the logs to find the exact query that CiviCRM is using and this is the WHERE clause we found:

  WHERE  (  (  CAST(civicrm_value_fachrichtung_11.fachrichtung_45 AS BINARY) RLIKE BINARY 'Allgemeinmedizin'  AND  CAST(civicrm_value_fachrichtung_11.fachrichtung_45 AS BINARY) RLIKE BINARY 'Angiologie'  )  )  AND ( 1 ) AND (contact_a.is_deleted = 0)

here we can clearly see that our values are connected with AND and that some CAST AS BINARY function is being used followd by RLIKE.

We also did the same kind of search on another custom field, which is non multivalue and everything worked properly and the sql query was as expected.

So why is CiviCRM building the query like in the example above? Is this intended for some reason im missing or a bug?

we are using civicrm 5.40.1 with drupal 7.80

  • Thanks for your message. If you're sure (and a brief check let's me assume our installation is behaving the same way) this is a bug in my opinion. You should file a bug ticket like in civicrm.org/bug-reporting. As expectable, the API (use /civicrm/api3 to explore) seems to be affected to.
    – nielo
    Oct 26 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.