Extending "get" action with custom WHERE clause
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()
  -&gt;where('(start_date &gt;= CURDATE() || end_date &gt;= CURDATE())');