0

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. 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

2 Answers 2

2

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)

2
  • 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. Apr 15, 2021 at 5:36
  • It may be possible via the bridge entity but I don't know how! May need @coleman for that.
    – Aidan
    Apr 15, 2021 at 9:02
1

Following up on the EntityBridge part of the question, this is now documented in the APIv4 section 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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.