SQL View APIv4 Entities

SQL View Entities allow you to expose read-only APIv4 entities backed by database views. This is particularly useful for combining data from multiple tables, filtering records, or providing flattened views of complex schemas without the performance penalty of custom PHP getters.

To-date, this capability is provided primarily for extensions to declare custom read-only views.

---

Defining an SQL View Entity

To create an SQL View Entity, create a PHP class inside your extension's /Civi/Api4 directory. The class name should match your desired entity name and must inherit from \Civi\Api4\Generic\SqlView.

Because it inherits from SqlView (which implements AutoServiceInterface), CiviCRM will automatically scan, register, and manage the lifecycle of your entity.

Required Methods

Any class extending SqlView must implement the following two abstract methods:

1. viewSelect()

Defines the SELECT clause of the database view and provides metadata for the APIv4 fields.

It must return an array of associative arrays, where each array describes a field in the view: * select (string, required): The SQL select expression (e.g. 'c.id' or 'CONCAT(c.first_name, " ", c.last_name)'). * name (string, required): The field name alias in the view and the name of the APIv4 field. * data_type (string, optional if using original_field): The type of data (e.g. 'String', 'Integer', 'Boolean'). * original_field (string, optional): If selecting an existing database column, specify the original entity and field name concatenated with a dot (e.g. 'Contact.id').

Inheriting Metadata and Relationships via original_field

Specifying an original_field is highly recommended. It allows the API to automatically reuse the existing field metadata, including option lists (pseudoconstants) and foreign key (FK) relationships. This enables implicit join syntax (e.g. my_view_field.display_name) to work automatically.

2. viewFrom()

Defines the body of the SQL view. It must return a string containing the SQL query starting with the word FROM.

---

Optional Customizations

• viewName(): Customizes the table name of the view in the database. By default, this is generated as civicrm_view_{entity_name_in_snake_case}.
• getEntityTitle(): Returns a human-friendly singular title for the entity.

Class Annotations (Metadata)

You can decorate your class with standard APIv4 docblock annotations to customize the entity metadata: * @description: A description of what the view does. * @icon: CSS class for the icon (e.g. fa-envelope). * @labelField: The field that represents the label of each record. * @orderBy: Default column to sort results by. * @searchable: Controls whether this entity is searchable (e.g. none, secondary, bridge). * @searchFields: Array of searchable fields. * @since: The version of your extension/CiviCRM this view was introduced in.

---

Code Example

Below is a complete example of an SQL View Entity combining Individual contacts with their primary email addresses.

<?php

namespace Civi\Api4;

/**
 * Individual Primary Email View.
 *
 * @description Combined view of Individuals and their primary email addresses.
 * @icon fa-envelope
 * @since 6.14
 */
class IndividualPrimaryEmail extends Generic\SqlView {

  /**
   * Defines the SELECT clause and APIv4 field definitions.
   */
  protected static function viewSelect(): array {
    return [
      [
        'select' => 'c.id',
        'name' => 'contact_id',
        'original_field' => 'Contact.id',
      ],
      [
        'select' => 'CONCAT(c.first_name, " ", c.last_name)',
        'name' => 'full_name',
        'data_type' => 'String',
      ],
      [
        'select' => 'e.email',
        'name' => 'email',
        'original_field' => 'Email.email',
      ],
      [
        'select' => 'e.id',
        'name' => 'email_id',
        'original_field' => 'Email.id',
      ],
      [
        'select' => 'e.location_type_id',
        'name' => 'email_location_type_id',
        'original_field' => 'Email.location_type_id',
      ],
    ];
  }

  /**
   * Defines the FROM clause and query body of the view.
   */
  protected static function viewFrom(): string {
    return <<<SQL
FROM civicrm_contact c
LEFT JOIN civicrm_email e ON e.contact_id = c.id AND e.is_primary = 1
WHERE c.contact_type = "Individual" AND c.is_deleted = 0
SQL;
  }

}

---

Lifecycle & Automated Management

CiviCRM automatically manages the creation, drop, and schema integration of the view via Symfony events:

1. View Creation (civi.api4.entityTypes event): CiviCRM drops and recreates the database view using the definitions returned by viewSelect() and viewFrom() whenever the APIv4 entityTypes cache is rebuilt.
2. Schema Integration (api.schema_map.build event): During metadata compilation, CiviCRM registers foreign key relationships to enable implicit joins in API calls.

---

Usage in APIv4

Once registered, you can query your SQL View Entity like any other APIv4 entity:

// Fetch records from the view
$results = \Civi\Api4\IndividualPrimaryEmail::get()
  ->addSelect('contact_id', 'full_name', 'email', 'email_location_type_id:label')
  ->addWhere('email', 'LIKE', '%@example.com')
  ->execute();

// Perform implicit joins using inherited FK metadata
$results = \Civi\Api4\IndividualPrimaryEmail::get()
  ->addSelect('contact_id.display_name', 'email_id.email')
  ->execute();