Extending “get” action with custom WHERE clause

Question

Is it possible to define a new api (v3) endpoint which has a specific WHERE clause but that still leverages the flexibility of the "get" action in terms of defining parameters, options, pagination, chained api calls etc?

What I'm trying to achieve is to define an endpoint that returns all records that match a WHERE clause which looks something like this

(entity.field1 = 1 AND entity.field2 = 'a') OR (entity.field1 = 2 AND entity.field2 = 'b') OR (entity.field1 = 3 AND entity.field2 = 'c')"

but at the same time I still want the endpoint to be able to accept all the parameters and options that the "get" options accept, because a consumer of this endpoint would want to further filter the records or to sort them, paginate them, etc.

It doesn't seem that the built-in actions have support for OR clauses, so this endpoint would have to be a mix of api methods and a manually built SQL query (if it's even possible?)

Answer 1

The options depend on the version of Civi.

In v4.5/4.6, most API's correspond to a BAO class (e.g. Activity API ==> CRM_Activity_BAO_Activity). You can define a function setApiFilter() which examines $params and mixes in arbitrary bit of SQL.

The query-builder used there had several weaknesses, so v4.7 uses a different approach. We had some drafts/discussions which would have made this more hookable, but the hook ultimately went a different direction.

As it stands, to do this as an extension in v4.7, I think you'd have to make custom API (Myentity.mycustomget). The API would then pass-through the request to _civicrm_api3_basic_get(). _basic_get() is the engine which drives query-building in the majority of APIs. You can find ~100 examples by grepping for _civicrm_api3_basic_get. Loosely, the code would look like:

function civicrm_api3_myentity_mycustomget($params) {
  return _civicrm_api3_basic_get($bao_name, $params, $returnAsSuccess, $entity,
    $sql, $uniqueFields)
}

Note that one of those parameters, $sql, can be a fragment of arbitrary SQL (WHERE, JOIN, GROUP BY, etc), e.g. let $sql be:

CRM_Utils_SQL_Select::fragment()
  ->where('(start_date >= CURDATE() || end_date >= CURDATE())');