edit

API Joins

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 API 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",
));

Tip

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 xml schema identifies a foreign key or a pseudoconstant. The getfields action identifies fields that are eligible for an API join.

Warning

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. This limitation will be removed in APIv4.