Best way to make an EntityFieldQuery query be case sensitive?

Question

The case-sensitivity of queries created using EntityFieldQuery rely on the underlying database's collation [*]. So if the collation for a particular field is 'utf8_general_ci', then all the queries created with EntityFieldQuery are case insensitive.

Given that it's not always possible to change the schema (say, if you are developing a module that acts on third party fields) how can you ensure case sensitivity when using EntityFieldQuery ?

My solution so far consists of using a tag and a hook_query_TAG_alter hook to cast the value to a BINARY value (and EFQ allows us to create our own operator for this). Here is an example:

function mymodule_query_case_sensitive_alter($query) {
  foreach ($conditions as $index => $condition) {
    if (is_array($condition) && $condition['operator'] == '=c') {
      $placeholder = ':db_condition_placeholder_' . $query->nextPlaceHolder();
      $conditions[$index]['field'] = 'CAST(' . $conditions[$index]['field'] . ' AS BINARY) = ' . $placeholder;
      $conditions[$index]['value'] = array($placeholder => $conditions[$index]['value']);
      $conditions[$index]['operator'] = NULL;
    }
  }
}

Then one could run:

$r = (new EntityFieldQuery())
  ->fieldCondition('field_my_field', 'value', 'ExaMple', '=c')
  ->addTag('case_sensitive')
  ->execute();

Based on this, my questions are:

• It is quite cumbersome. Is there really no simpler way ?
• Does such a tag already exist ?
• This is limited to MySql field storage backend - how could it be adapted to work on other field storage backends ?

[*] I am assuming a MySql field storage backend here.

Answer 1

MySQL documentation shows you can simply define collation at a query level:

SELECT k COLLATE latin1_german2_ci AS k1;

 SELECT *
 FROM t1
 WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;

 SELECT *
 FROM t1
 WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

If collation is only thing that affects case sensitivity for you, that's the way to go, no need to cast anything.

Based on this, you can set the operator in the EntityFieldQuery to be 'COLLATE utf8_general_cs ='. For instance the following will work:

$r = (new EntityFieldQuery())
  ->fieldCondition('field_my_field', 'value', 'eXample', 'COLLATE utf8_general_cs =')
  ->execute();

This remains limited to MySQL field storage backend however.

Answer 2

Drupal 8 core patch documentation https://www.drupal.org/files/issues/entity-query-case-2068655-127_0.patch

• Drupal considers LIKE case insensitive and the following is often used
• to tell the database that case insensitive equivalence is desired:
• @code
• db_select('users')
• ->condition('name', db_like($name), 'LIKE')
• @endcode
• Use 'LIKE BINARY' instead of 'LIKE' for case sensitive queries.