APIv4 Implicit Joins

In a relational database, you often need to work with data from multiple entities (tables) at once.

APIv4 provides a simple syntax for automatically reading and writing from entities related by a foreign key:

Read

Example: Get the title of an event's campaign to show to the user

Event::get()
  ->addSelect('campaign_id.title')
  ...

Write

Example: Create an event linked to a campaign without needing to look up the campaign's ID

Event::create()
  ->addValue('campaign_id.name', 'My_Cool_Campaign')
  ...

These are called implicit because you do not need to tell the API to join tables, it does so automatically by looking at the foreign key of the campaign_id field. For more complex use-cases, an explicit join allows a greater flexibility and control for get actions.

Joins or Chaining

There is some overlap between joins and Chaining; in some cases either technique can accomplish the same thing. Because chaining executes a new query for every result, joins are usually more efficient when given a choice between the two.

Identifying fields eligible for a join

Using getFields with the following params will identify fields that are eligible for an API join, in this example for the Contact entity:

Contact::getFields()
  ->addWhere('fk_entity', 'IS NOT EMPTY')
  ...

Multiple joins

Note: multiple joins are only available to get actions

Joining across the same foreign key multiple times:

civicrm_api4('Event', 'get', [
  'select' => ['title', 'campaign_id.name', 'campaign_id.campaign_type_id'],
  ...

Joining multiple levels deep:

Civi\Api4\Email::get()
  ->addSelect('contact_id.employer_id.display_name')
  ...

Combining a join with an option transformation:

Civi\Api4\Email::get()
  ->addSelect('contact_id.contact_type:label')
  ...