2

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.

  • 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) – Alice Heaton Jun 28 '13 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 '13 at 11:05
  • Comments are welcome, and now the question is clearer thanks to you :) – Alice Heaton Jun 28 '13 at 11:08
2

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.

  • @Alice I was just writing code like that, and couldn't save it due to your edit pending ;) – Mołot Jun 28 '13 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'. – Alice Heaton Jun 28 '13 at 11:32
  • @AliceHeaton Corrected. Where was my mind? – Mołot Jun 28 '13 at 11:34

Your Answer

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

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