APIv3 Joins

APIv3 Deprecation

API version 3 is now deprecated. Version 4 is recommended.

An API "get" action will typically return only the values of the entity requested. However, there are times when it is advantageous to returned data from a related entity. For instance, when querying the API for email addresses, you may want to return the name of the associated contact from the Contact entity.

The CiviCRM API supports two methods of returning data from associated entities; API Joins and APIv3 Chaining. API joins provide higher performance by making a single SQL query with a SQL join, and are generally preferable to API chaining where available.

Using an API Join

To use a join in an API call, specify the name of the field on which the join happens, a period, and the name of the field to reference.

For instance, to search for all primary emails, returning the email and joining to also return the contact's display name:

$result = civicrm_api3('Email', 'get', array(
  'return' => array("email", "contact_id.display_name"),
  'is_primary' => 1,
));

Alternatively, to return email addresses of everyone whose last name is Smith by joining to the Contact entity:

$result = civicrm_api3('Email', 'get', array(
  'contact_id.last_name' => "Smith",
));

Supported actions

Joins are available only with the get, getsingle, and getcount actions.

Identifying fields eligible for a join

It is possible to join an entity to any other entity if the schema identifies a foreign key or a pseudoconstant. The getfields action identifies fields that are eligible for an API join.

Not supported for every entity

For historical reasons, some entities have a non-standard API in APIv3 in order to handle more complicated operations. Those entities - 'Contact', 'Contribution', 'Pledge', and 'Participant' - can be joined to from another table, but you can not join to other tables from them. APIv4 does not have this limitation.