hook_civicrm_selectWhereClause

Summary

This hook allows the permission (ACL) clause of a SELECT query to be altered. It is called for all API-based queries (e.g. SearchKit) but not all legacy queries (e.g. search, report).

Not for Contacts

For Contact ACLs use hook_civicrm_aclWhereClause.

Background

Every SELECT query consists of a base entity (the FROM clause) plus additional entities (JOIN clauses). This hook fires once for each of these entities during query composition, collecting entity-specific SQL snippets to control ALCs. Implementors do not need to know the structure or the context of the query, they are simply given an entity name and an array of system-generated ACL snippets which they may append to or alter.

Definition

hook_civicrm_selectWhereClause(string $entity, array &$clauses, int $userId, array $conditions)

Parameters

• string $entity - name of entity being selected (Contribution, EntityTag, etc.).
• array $clauses - (reference) arrays of clauses keyed by field. Uses the format ['field_name' => ['operator condition', 'operator condition']] Arrays are joined by AND, sub-arrays joined by OR and then AND with the main array.
• int $userId - contact id of logged-in user. Useful to check permissions before adding restrictions to the query.
• array $conditions - values from the WHERE or ON clause which can be used for advanced query optimization, e.g. to avoid adding ACL clauses that are redundant with constraints already added by a WHERE clause. (take a look at these if your SQL query is slow and you need a way to optimize it, otherwise they are safe to ignore).

Example

Consider the following simplified query which fetches participants with their notes and contribution records:

civicrm_api4('Participant', 'get')
  ->addJoin('Note' ...)
  ->addJoin('Contribution' ...)

For each of the 3 entities, the query builder will make an array of standard ACL clauses, pass them to this hook, and then insert them into the WHERE or ON clause as appropriate. Hook implementors will not know or need to know which part of the query they are dealing with, as they are only concerned with restricting access to the given entity type.

A hook implementation might look like this:

function example_civicrm_selectWhereClause($entity, &$clauses, $userId, $conditions) {
  switch ($entity) {
    case 'Participant':
      // Typically you want to check for some permissions before applying restrictions
      if (!CRM_Core_Permission::check('administer CiviCRM', $userId)) {
        // To restrict participants based on the event type, we are not allowed to perform a join
        // but we can use a subquery.
        $clauses['event_id'][] = 'IN (SELECT id FROM civicrm_event WHERE event_type_id IN ())';
      }
      break;

    case 'Note':
      // Let's say we have added a new option to the note_privacy option group
      // to allow more nuanced privacy settings. The standard ACL array already includes
      // the first 2 built-in options, so we just need to add a sub-clause for our 3rd.
      // This super-permission short-circuits everything so only run if the user lacks it:
      if (!CRM_Core_Permission::check('view all notes', $userId)) {
        // What's going on here is that `$clauses['privacy']` already contains an array of arrays
        // (which means OR).
        // @see CRM_Core_BAO_Note::addSelectWhereClause()
        // The existing values are `"= 0" (OR) "= 1 AND {contact_id} = $currentUser"`
        // So here we are adding a 3rd condition IF the above permission check passes, to allow
        // our privileged users to see our special privacy type 2.
        if (CRM_Core_Permission::check('view note type two', $userId)) { 
          $clauses['privacy'][0][] = '= 2';
        }
      }
      break;

    // Another example: restrict access to cases by type
    case 'Case':
      if (!CRM_Core_Permission::check('access all cases and activities', $userId)) {
        // Let's say we have a particularly sensitive case type (4) that should only be visible if status is open (1).
        // For that we'll need an OR clause, and to select multiple fields (case_type_id and status_id).
        // OR clauses can be achieved with an array within the array, and additional fields can be added within
        // {curly_braces} (so the query builder can properly prefix them with the table name).
        $clauses['case_type_id'][] = [
          '!= 4',
          // OR
          '= 4 AND {status_id} = 1',
        ];
      }
      break;
  }
}

Notes

Not every query in CiviCRM calls this hook. All APIv3 and APIv4 queries do, so a good way to refactor a legacy query to ensure this hook is called is to switch it to use the API.

Each item in $clauses array must be an array. Each item in that array should either be a string or an array (syntax for OR). Use the $arrayAppend[] notation to avoid accidentally deleting existing clauses:

// Safe
$clauses['id'][] = 'IN (1, 2, 3)';
// Safe (items in this sub-array will be joined with OR, then combined with other clauses using AND)
$clauses['id'][] = ['IN (1, 2, 3)', 'IN (4, 5, 6)'];

// Danger: overwrites any existing clauses!
$clauses['id'] = ['IN (1, 2, 3)'];

// Error: not an array
$clauses['id'] = 'IN (1, 2, 3)';

Changelog

• $userId and $conditions added in 5.67.
• $clauses support for {braces_notation} and OR operator added in 5.67.