4

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.

3
  • The slip was on the word 'engine', not 'storage'. The right word should have been 'backend' - Drupal allows different storage backends for fields, and these are not necessarily SQL based. See api.drupal.org/api/drupal/modules!field!field.api.php/function/… for instance. (question edited accordingly) Jun 28, 2013 at 10:59
  • I didn't want to be mean of course, I commented for precision's sake, and only after answering.
    – Mołot
    Jun 28, 2013 at 11:05
  • Comments are welcome, and now the question is clearer thanks to you :) Jun 28, 2013 at 11:08

2 Answers 2

4

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.

3
  • @Alice I was just writing code like that, and couldn't save it due to your edit pending ;)
    – Mołot
    Jun 28, 2013 at 11:23
  • As a small addition: the uf8 general case sensitive collation is 'ut8_general_cs', however that is not always enabled (depending on version of MySql) so an alternative is 'utf8_bin'. Jun 28, 2013 at 11:32
  • @AliceHeaton Corrected. Where was my mind?
    – Mołot
    Jun 28, 2013 at 11:34
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.