Answers.Body: <p>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:</p>
  
  <p><code>$kk = str_replace($replace, '_', $k);</code></p>
  
  <p><a href="https://github.com/pear/DB_DataObject/blob/trunk/DB/DataObject.php#L653" rel="nofollow noreferrer">https://github.com/pear/DB_DataObject/blob/trunk/DB/DataObject.php#L653</a></p>
  
  <p>Which replaces dots and spaces in the keys of the results. </p>
  
  <p>It seems like the steps it follows are:</p>
  
  <ul>
  <li>Call <code>fetchAll()</code> which loops through the results</li>
  <li>Call <code>fetch()</code> 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.</li>
  <li>Call <code>toArray()</code> to get the output for each result. <code>toArray()</code> 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.</li>
  </ul>
  
  <p>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.</p>
  
  <p>Another alternative is to bypass any call to <code>DB_DataObject::fetch()</code></p>
  
  <pre><code>  $results = array();
    $dbResult = \CRM_Core_DAO::executeQuery($sql)-&gt;getDatabaseResult();
    while ($row = mysqli_fetch_assoc($dbResult-&gt;result)) {
      $results[] = $row;
    }
  </code></pre>
  
  <p>Edit:</p>
  
  <p>Normal usage of <code>SelectQuery::run()</code> gets around this by doing a string replace of <code>.</code> => <code>_</code> on the aliases and checking if a property existing in the BAO result. </p>
  
  <p><a href="https://github.com/civicrm/civicrm-core/blob/4c6cc3647eb7b19a05529bd40b02e26623e8effe/Civi/API/SelectQuery.php#L153" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/blob/4c6cc3647eb7b19a05529bd40b02e26623e8effe/Civi/API/SelectQuery.php#L153</a></p>
  
  <p>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 <code>run()</code>.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-04T10:38:41.490Z
Answers.LastActivityDate: 2017-07-04T11:09:18.683Z
Answers.LastEditDate: 2017-07-04T11:09:18.683Z
Answers.UserDisplayName: mickadoo
Body: <p>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.</p>
  
  <p>This causes the aliased field to be returned as empty, whereas an alias without a dot in it will be fine.</p>
  
  <p><code>\CRM_Core_DAO::executeQuery('SELECT first_name as "Foo.Bar" FROM civicrm_contact;')-&gt;fetchAll()</code></p>
  
  <p>Will return an array of contacts, but with <code>Foo.Bar =&gt; ""</code>.</p>
  
  <p>I've also tried using backticks around the alias, but with the same result.</p>
  
  <p>I've found the cause of it, but am just posting this here for anyone else who might run into it.</p>
  
Tags: <database>
Title: Select alias with dot is not returned from CRM_Core_DAO::executeQuery()
ThreadText: # Select alias with dot is not returned from CRM_Core_DAO::executeQuery()
  
  (Asked by: username_0 on 2017-07-04)
  
  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.
  
  **Tags:** database
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2017-07-04. Score: 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().
  
  
  ---
  
  