1

I've been playing around with the idea of using the custom group and field name as an alias when fetching data using the API. Because of this I've ended up with a situation where an alias can have a dot in it.

This causes the aliased field to be returned as empty, whereas an alias without a dot in it will be fine.

\CRM_Core_DAO::executeQuery('SELECT first_name as "Foo.Bar" FROM civicrm_contact;')->fetchAll()

Will return an array of contacts, but with Foo.Bar => "".

I've also tried using backticks around the alias, but with the same result.

I've found the cause of it, but am just posting this here for anyone else who might run into it.

2

Depending on the version you're using you may have a different version of the pear DB_DataObject, but they all seem to have something similar to this line:

$kk = str_replace($replace, '_', $k);

https://github.com/pear/DB_DataObject/blob/trunk/DB/DataObject.php#L653

Which replaces dots and spaces in the keys of the results.

It seems like the steps it follows are:

  • Call fetchAll() which loops through the results
  • Call fetch() on each result. This will change the original keys, replacing any dots with underscores and store the value as a property using the changed key.
  • Call toArray() to get the output for each result. toArray() will loop through the original keys and return an associative array, mapping the original keys to their value. However since the key used to store the value has changed the value will be empty.

I'm not sure why they do this, but if you want to avoid it you can either do some sort of hacky substitution on the select aliases before and after running the query.

Another alternative is to bypass any call to DB_DataObject::fetch()

  $results = array();
  $dbResult = \CRM_Core_DAO::executeQuery($sql)->getDatabaseResult();
  while ($row = mysqli_fetch_assoc($dbResult->result)) {
    $results[] = $row;
  }

Edit:

Normal usage of SelectQuery::run() gets around this by doing a string replace of . => _ on the aliases and checking if a property existing in the BAO result.

https://github.com/civicrm/civicrm-core/blob/4c6cc3647eb7b19a05529bd40b02e26623e8effe/Civi/API/SelectQuery.php#L153

This should fix the problem, although in the later version of DB_DataObject they're also replacing spaces, which would cause problems even if using run().

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.