joins.md

# 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; APIv4 Joins and [APIv4 Chaining](/api/v4/chaining.md).  API joins provide higher performance by making a single SQL query with a [SQL join](https://dev.mysql.com/doc/refman/5.7/en/join.html), 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:

Object Oriented way:

```php
$result \Civi\Api4\Email::get()
  ->setSelect([
    'contact.display_name', 
    'email',
  ])
  ->addWhere('is_primary', '=', 1)
  ->setLimit(25)
  ->setCheckPermissions(FALSE)
  ->execute();
```

Traditional:

```php
$result = civicrm_api4('Email', 'get', [
  'select' => ["email", "contact.display_name"],
  'where' => [
    ['is_primary', '=', 1],
  ],
  'limit' => 25,
]);
```

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

Object Oriented way:

```php
$result \Civi\Api4\Email::get()
  ->addWhere('contact.last_name', '=', 'Smith')
  ->setLimit(25)
  ->setCheckPermissions(FALSE)
  ->execute();
```

Traditional:

```php
$result = civicrm_api4('Email', 'get', [
  'where' => [
    ['contact.last_name', '=', "Smith"],
  ],
  'limit' => 25,
]);
```

You can join multiple times in one query.  For instance, to return a list of events, displaying their name, the name of the related campaign, and that campaign's type:

Object Oriented way:

```php
$result \Civi\Api4\Email::get()
  ->setSelect(['title', 'campaign.name', 'campaign.campaign_type_id'])
  ->setLimit(25)
  ->setCheckPermissions(FALSE)
  ->execute();
```

Traditional:

```php
$result = civicrm_api4('Event', 'get', [
  'select' => ['title', 'campaign.name', 'campaign.campaign_type_id'],
));

```

!!! tip
    Joins are available only with the [get](/api/v4/actions.md#get) action

## Identifying fields eligible for a join

It is possible to join an entity to any other entity if the [xml schema](/framework/database/schema-definition.md) identifies a [foreign key](/framework/database/schema-definition.md#table-foreignKey) or a [pseudoconstant](/framework/database/schema-definition.md#table-field-pseudoconstant).  The [getfields](/api/v3/actions.md#getfields) action identifies
fields that are eligible for an API join.