CiviCRM Community Forums (archive)

Hi there,

We had a problem whereby the Rest API wasn't returning the requested number of records.  For example, if we requested 50 contacts, (offset: 0, rowCount: 50), it returned 47, if we requested 100, it returned 96, and so.

After some investigation we discovered that the SQL query being used to generate the results was returning some duplicate records, which were then being stripped before being sent back to the client, which accounted for the missing records in the API response.

To solve this we just edited the file: /administrator/components/com_civicrm/civicrm/CRM/Contact/BAO/Query.php

And added the following line of code before line 3635:

$sql = str_replace("LIMIT", "GROUP BY contact_a.id LIMIT", $sql);

so that the method "apiQuery" now looks like this:

[/b]static
  function apiQuery($params = NULL,
    $returnProperties = NULL,
    $fields           = NULL,
    $sort             = NULL,
    $offset           = 0,
    $row_count        = 25,
    $smartGroupCache  = TRUE
  ) {
    $query = new CRM_Contact_BAO_Query($params, $returnProperties,
      NULL, TRUE, FALSE, 1,
      FALSE, TRUE, $smartGroupCache
    );

    list($select, $from, $where, $having) = $query->query();
    $options = $query->_options;
    $sql = "$select $from $where $having";
    // add group by
    if ($query->_useGroupBy) {
      $sql .= ' GROUP BY contact_a.id';
    }
    if (!empty($sort)) {
      $sql .= " ORDER BY $sort ";
    }
    if ($row_count > 0 && $offset >= 0) {
      $sql .= " LIMIT $offset, $row_count ";
    }
   
    $sql = str_replace("LIMIT", "GROUP BY contact_a.id LIMIT", $sql);

    $dao = CRM_Core_DAO::executeQuery($sql);

    $values = array();
    while ($dao->fetch()) {
      $values[$dao->contact_id] = $query->store($dao);
    }
    $dao->free();
    return array($values, $options);
  }


This isn't the most elegant solution I know, but it seems to have solved our problem anyway.  Someone else might find this useful.


Simon.

Hi Erik,

I posted that in the issue tracker. - CRM-10421

Simon.