Answers.Body: <p>Years later - but GroupBy on multiple fields is now a feature of APIv4.  Check out the API Explorer for details.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-06T13:41:47.373Z
Answers.LastActivityDate: 2020-10-06T13:41:47.373Z
Answers.UserDisplayName: Aidan
Answers.Body: <p>I think this makes sense as an addition - but note that there are 3 types of get api</p>
  
  <p>1) api layer api - e.g anything that uses basic_get
  2) query object api (contact, contribute etc)
  3) random assorted api</p>
  
  <h1>1 is easy &amp; in one place - 2 &amp; 3 are ... not</h1>
  
  <p>BUT #1 changes substantially in 4.7 compared to 4.6 so you would need to fix at minimum in 4.7 &amp; add unit tests and ALSO in 4.6 if you want it to work there too.</p>
  
  <p>(We are open to backporting the 4.7 internals en masse to 4.6 with an self-deprecating opt-in -eg. $params['options']['4.7plusmode'] = TRUE;
  - the 4.7 code also supports custom field filters (e.g on address) and lays the groundwork for more nuanced acls since it permits joins in simple api which is a blocker in 4.6)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-01T22:24:22.100Z
Answers.LastActivityDate: 2015-11-01T22:24:22.100Z
Answers.UserDisplayName: eileen
Body: <p>I'm trying to write an API call returning all contacts that are at one end of one or more active relationships of a certain type, and their names. I note that you can specify limit, offset (LIMIT) and sort (ORDER BY) options. Something similar for GROUP BY, so I don't need to do this in PHP separately after the API call, would be useful. Does it exist?</p>
  
  <p>Like this, for example:</p>
  
  <pre><code>$ao_result = civicrm_api('Relationship', 'get', array(
    'version'    =&gt; 3,
    'sequential' =&gt; 1,
    'relationship_type_id' =&gt; 19,
    'is_active'  =&gt; TRUE,
    'options'    =&gt; array(
      'group_by' =&gt; 'contact_id_b',
    ),
    'api.Contact.getvalue' =&gt; array(
      'id'     =&gt; '$value.contact_id_b',
      'return' =&gt; 'display_name',
    ),
  ));
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-07-23T12:16:20.900Z
Comments.Text: Or even 'group_by' => array('contact_id_b'), because you can have more than one.
Comments.UserDisplayName: DaveFF
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-07-24T08:22:56.947Z
Comments.Text: Could this be implemented solely in api/v3/utils.php or would it need to be all the CRM_*_BAO_Query and other places as well?
Comments.UserDisplayName: DaveFF
Tags: <api><database>
Title: Can I pass a GROUP BY to an API call?
ThreadText: # Can I pass a GROUP BY to an API call?
  
  (Asked by: username_0 on 2015-07-23)
  
  I'm trying to write an API call returning all contacts that are at one end of one or more active relationships of a certain type, and their names. I note that you can specify limit, offset (LIMIT) and sort (ORDER BY) options. Something similar for GROUP BY, so I don't need to do this in PHP separately after the API call, would be useful. Does it exist?
  
  Like this, for example:
  
  ```
  $ao_result = civicrm_api('Relationship', 'get', array(
    'version'    => 3,
    'sequential' => 1,
    'relationship_type_id' => 19,
    'is_active'  => TRUE,
    'options'    => array(
      'group_by' => 'contact_id_b',
    ),
    'api.Contact.getvalue' => array(
      'id'     => '$value.contact_id_b',
      'return' => 'display_name',
    ),
  ));
  
  ```
  
  **Tags:** api, database
  
  > **Comments:**
  > **username_0**: Or even 'group_by' => array('contact_id_b'), because you can have more than one.  
  > **username_0**: Could this be implemented solely in api/v3/utils.php or would it need to be all the CRM_*_BAO_Query and other places as well?  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2020-10-06. Score: 3
  
  Years later - but GroupBy on multiple fields is now a feature of APIv4. Check out the API Explorer for details.
  
  
  ---
  
  ### Answer by username_2 on 2015-11-01. Score: 3
  
  I think this makes sense as an addition - but note that there are 3 types of get api
  
  1) api layer api - e.g anything that uses basic\_get
  2) query object api (contact, contribute etc)
  3) random assorted api
  
  # 1 is easy & in one place - 2 & 3 are ... not
  
  BUT #1 changes substantially in 4.7 compared to 4.6 so you would need to fix at minimum in 4.7 & add unit tests and ALSO in 4.6 if you want it to work there too.
  
  (We are open to backporting the 4.7 internals en masse to 4.6 with an self-deprecating opt-in -eg. $params['options']['4.7plusmode'] = TRUE;
  - the 4.7 code also supports custom field filters (e.g on address) and lays the groundwork for more nuanced acls since it permits joins in simple api which is a blocker in 4.6)
  
  
  ---
  
  