Answers.Body: <p>For joining explicitly by adding an ActivityContact join then a Contact join instead of just a Contact join with &quot;Using ActivityContact&quot; in the explorer, I think it's just the Contact join that is causing you problems.  Either join on <code>contact.id</code> not <code>id</code>, or omit the explicit clause to use the implicit one.</p>
  <p>ie use:</p>
  <pre><code>    ['Contact AS contact', TRUE, NULL, ['contact.id', '=', 'activity_contact.contact_id']],
  </code></pre>
  <p>or</p>
  <pre><code>    ['Contact AS contact', TRUE, NULL],
  </code></pre>
  <p>Both this:</p>
  <pre><code>$activities = \Civi\Api4\Activity::get()
    -&gt;addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    -&gt;setJoin([
      ['ActivityContact AS activity_contact', TRUE, NULL, ['activity_contact.record_type_id', '=', 3]], 
      ['Contact AS contact', TRUE, NULL, ['activity_contact.id', '=', 'contact.id']],
    ])
    -&gt;setLimit(3)
    -&gt;execute();
  </code></pre>
  <p>and this:</p>
  <pre><code>$activities = \Civi\Api4\Activity::get()
    -&gt;addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    -&gt;setJoin([
      ['ActivityContact AS activity_contact', TRUE, NULL, ['activity_contact.record_type_id', '=', 3]], 
      ['Contact AS contact', TRUE, NULL],
    ])
    -&gt;setLimit(3)
    -&gt;execute();
  </code></pre>
  <p>produce the same sql:</p>
  <pre><code>SELECT `a`.`id` AS `id`, `a`.`activity_date_time` AS `activity_date_time`, `a`.`subject` AS `subject`, `a`.`details` AS `details`, `contact`.`display_name` AS `contact.display_name`
  FROM civicrm_activity a
  INNER JOIN `civicrm_activity_contact` `activity_contact` ON `activity_contact`.`activity_id` = `a`.`id` AND `activity_contact`.`record_type_id` = \&quot;3\&quot;
  INNER JOIN `civicrm_contact` `contact` ON `activity_contact`.`id` = `contact`.`id`
  WHERE ((`a`.`activity_type_id` IS NULL OR (`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 17, 19, 22, 28, 29, 30, 31, 32, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56, 57)))) AND (`a`.`is_test` = \&quot;0\&quot;) AND (`a`.`is_deleted` = \&quot;0\&quot;)
  LIMIT 3
  OFFSET 0
  </code></pre>
  <p>(API Explorer 4 on 5.36.alpha1)</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T05:36:26.520Z
Answers.Text: Riiiiight! Got you. Thanks! So you can't do it with the "bridge"/"using" feature; you can do it in the explicit A → AC → C way; there are implicit ON clauses added for you. It's one of those things where the helpers feel like magic which is delightful except that it gets in the way of understanding. I've struggled with API4 for so long because it's like SQL but ... a bit *not* like it. Aside: I wonder what the point of the bridge entity is when it leaves you unable to know *how* the contact and activity are related! Anyway: thanks, very helpful explanation.
Answers.UserDisplayName: artfulrobot
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T09:02:07.420Z
Answers.Text: It _may_ be possible via the bridge entity but I don't know how! May need @coleman for that.
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-14T12:35:36.523Z
Answers.LastActivityDate: 2021-04-15T05:39:04.730Z
Answers.LastEditDate: 2021-04-15T05:39:04.730Z
Answers.UserDisplayName: Aidan
Answers.Body: <p>Following up on the EntityBridge part of the question, this is now documented in the <a href="https://docs.civicrm.org/dev/en/latest/api/v4/explicit-joins/#using-an-entitybridge" rel="nofollow noreferrer">APIv4 section</a> of the developer guide.</p>
  <p>The fields of the EntityBridge are effectively merged into the main entity.  So joining <code>Contact</code> using the <code>ActivityContact</code> bridge makes the <code>ActivityContact</code>'s fields appear on <code>Contact</code> such that the <code>record_type_id</code> of <code>ActivityContact</code> is exposed on the <code>Contact</code> entity as <code>contact.record_type_id</code>.</p>
  <p>Using this, the code becomes:</p>
  <pre><code>$activities = \Civi\Api4\Activity::get()
    -&gt;addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    -&gt;addJoin(
       'Contact AS contact',
       'LEFT',
       'ActivityContact',
       ['id', '=', 'contact.activity_id'],
       ['contact.record_type_id:name', '=', 'Activity Targets'])
    -&gt;setLimit(3)
    -&gt;execute();
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-29T15:35:19.860Z
Answers.LastActivityDate: 2021-06-29T15:35:19.860Z
Answers.UserDisplayName: Aidan
Body: <p>I have an Api4 call that gets a particular set of activities. I'd like the call to return the names of the &quot;target&quot;/&quot;with&quot; contacts.</p>
  <p>I've tried:</p>
  <h2>Joining the Contact directly &quot;using&quot; ActivityContact</h2>
  <pre><code>$activities = \Civi\Api4\Activity::get(FALSE)
    -&gt;addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    -&gt;setJoin([
      ['Contact AS contact', TRUE, 'ActivityContact'],
    ])
    -&gt;addWhere('activity_type_id', '=', 56)
    -&gt;addOrderBy('activity_date_time', 'DESC')
    -&gt;setLimit(3)
    -&gt;execute();
  </code></pre>
  <p>This works but leaves me with no way to limit the results to just the target contacts.</p>
  <h2>Joining the ActivityContact record, then the Contact</h2>
  <p>This is how I know the SQL needs to work, but it doesn't work [as expected] in Api4:</p>
  <pre><code>
  $activities = \Civi\Api4\Activity::get(FALSE)
    -&gt;addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    -&gt;setJoin([
      ['ActivityContact AS activity_contact', TRUE, NULL, ['activity_contact.record_type_id', '=', 3]], 
      ['Contact AS contact', TRUE, NULL, ['id', '=', 'activity_contact.contact_id']],
    ])
    -&gt;addWhere('activity_type_id', '=', 56)
    -&gt;addOrderBy('activity_date_time', 'DESC')
    -&gt;setLimit(3)
    -&gt;execute();
  
  </code></pre>
  <p>This ends up joining the contact ID to the activity ID(!)</p>
  <pre><code>a`.`id` = `activity_contact`.`contact_id`
  </code></pre>
  <p>Perhaps a Join in Api4 <em>always</em> means a join onto the primary entity? Except for hard-coded &quot;using&quot; joins? It's not mentioned in the <a href="https://docs.civicrm.org/dev/en/latest/api/v4/joins/" rel="nofollow noreferrer">documentation</a>. Perhaps it's that you cannot do it from the API explorer? Though I kinda use that to figure out what you <em>can</em> do.</p>
  <p>I'm using CiviCRM 5.35.1</p>
  
Tags: <apiv4>
Title: How to get Target Contacts from Api4 Activity.get call?
ThreadText: # How to get Target Contacts from Api4 Activity.get call?
  
  (Asked by: username_0 on 2021-04-01)
  
  I have an Api4 call that gets a particular set of activities. I'd like the call to return the names of the "target"/"with" contacts.
  
  I've tried:
  
  ## Joining the Contact directly "using" ActivityContact
  
  ```
  $activities = \Civi\Api4\Activity::get(FALSE)
    ->addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    ->setJoin([
      ['Contact AS contact', TRUE, 'ActivityContact'],
    ])
    ->addWhere('activity_type_id', '=', 56)
    ->addOrderBy('activity_date_time', 'DESC')
    ->setLimit(3)
    ->execute();
  
  ```
  
  This works but leaves me with no way to limit the results to just the target contacts.
  
  ## Joining the ActivityContact record, then the Contact
  
  This is how I know the SQL needs to work, but it doesn't work [as expected] in Api4:
  
  ```
  
  $activities = \Civi\Api4\Activity::get(FALSE)
    ->addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    ->setJoin([
      ['ActivityContact AS activity_contact', TRUE, NULL, ['activity_contact.record_type_id', '=', 3]], 
      ['Contact AS contact', TRUE, NULL, ['id', '=', 'activity_contact.contact_id']],
    ])
    ->addWhere('activity_type_id', '=', 56)
    ->addOrderBy('activity_date_time', 'DESC')
    ->setLimit(3)
    ->execute();
  
  ```
  
  This ends up joining the contact ID to the activity ID(!)
  
  ```
  a`.`id` = `activity_contact`.`contact_id`
  
  ```
  
  Perhaps a Join in Api4 *always* means a join onto the primary entity? Except for hard-coded "using" joins? It's not mentioned in the [documentation](https://docs.civicrm.org/dev/en/latest/api/v4/joins/). Perhaps it's that you cannot do it from the API explorer? Though I kinda use that to figure out what you *can* do.
  
  I'm using CiviCRM 5.35.1
  
  **Tags:** apiv4
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2021-04-14. Score: 2 | Accepted answer
  
  For joining explicitly by adding an ActivityContact join then a Contact join instead of just a Contact join with "Using ActivityContact" in the explorer, I think it's just the Contact join that is causing you problems. Either join on `contact.id` not `id`, or omit the explicit clause to use the implicit one.
  
  ie use:
  
  ```
      ['Contact AS contact', TRUE, NULL, ['contact.id', '=', 'activity_contact.contact_id']],
  
  ```
  
  or
  
  ```
      ['Contact AS contact', TRUE, NULL],
  
  ```
  
  Both this:
  
  ```
  $activities = \Civi\Api4\Activity::get()
    ->addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    ->setJoin([
      ['ActivityContact AS activity_contact', TRUE, NULL, ['activity_contact.record_type_id', '=', 3]], 
      ['Contact AS contact', TRUE, NULL, ['activity_contact.id', '=', 'contact.id']],
    ])
    ->setLimit(3)
    ->execute();
  
  ```
  
  and this:
  
  ```
  $activities = \Civi\Api4\Activity::get()
    ->addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    ->setJoin([
      ['ActivityContact AS activity_contact', TRUE, NULL, ['activity_contact.record_type_id', '=', 3]], 
      ['Contact AS contact', TRUE, NULL],
    ])
    ->setLimit(3)
    ->execute();
  
  ```
  
  produce the same sql:
  
  ```
  SELECT `a`.`id` AS `id`, `a`.`activity_date_time` AS `activity_date_time`, `a`.`subject` AS `subject`, `a`.`details` AS `details`, `contact`.`display_name` AS `contact.display_name`
  FROM civicrm_activity a
  INNER JOIN `civicrm_activity_contact` `activity_contact` ON `activity_contact`.`activity_id` = `a`.`id` AND `activity_contact`.`record_type_id` = \"3\"
  INNER JOIN `civicrm_contact` `contact` ON `activity_contact`.`id` = `contact`.`id`
  WHERE ((`a`.`activity_type_id` IS NULL OR (`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 17, 19, 22, 28, 29, 30, 31, 32, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56, 57)))) AND (`a`.`is_test` = \"0\") AND (`a`.`is_deleted` = \"0\")
  LIMIT 3
  OFFSET 0
  
  ```
  
  (API Explorer 4 on 5.36.alpha1)
  
  > **Comments:**
  > **username_0**: Riiiiight! Got you. Thanks! So you can't do it with the "bridge"/"using" feature; you can do it in the explicit A → AC → C way; there are implicit ON clauses added for you. It's one of those things where the helpers feel like magic which is delightful except that it gets in the way of understanding. I've struggled with API4 for so long because it's like SQL but ... a bit *not* like it. Aside: I wonder what the point of the bridge entity is when it leaves you unable to know *how* the contact and activity are related! Anyway: thanks, very helpful explanation.  
  > **username_1**: It _may_ be possible via the bridge entity but I don't know how! May need @coleman for that.  
  
  ---
  
  ### Answer by username_1 on 2021-06-29. Score: 1
  
  Following up on the EntityBridge part of the question, this is now documented in the [APIv4 section](https://docs.civicrm.org/dev/en/latest/api/v4/explicit-joins/#using-an-entitybridge) of the developer guide.
  
  The fields of the EntityBridge are effectively merged into the main entity. So joining `Contact` using the `ActivityContact` bridge makes the `ActivityContact`'s fields appear on `Contact` such that the `record_type_id` of `ActivityContact` is exposed on the `Contact` entity as `contact.record_type_id`.
  
  Using this, the code becomes:
  
  ```
  $activities = \Civi\Api4\Activity::get()
    ->addSelect('activity_date_time', 'subject', 'details', 'contact.display_name')
    ->addJoin(
       'Contact AS contact',
       'LEFT',
       'ActivityContact',
       ['id', '=', 'contact.activity_id'],
       ['contact.record_type_id:name', '=', 'Activity Targets'])
    ->setLimit(3)
    ->execute();
  
  ```
  
  
  ---
  
  