*
* This is a simple get function, but it should be usable for any kind of
* entity. I created it to work around CRM-16036.
- *
+ *
* @param string $dao_name
* Name of DAO
* @param array $params
- * As passed into api get function.
+ * As passed into api get function.
+ * @param bool $isFillUniqueFields
+ * Do we need to ensure unique fields continue to be populated for this api? (backward compatibility).
+ * @param array $extraMysql
+ *
* @return array
*/
-function _civicrm_api3_get_using_query_object_simple($dao_name, $params) {
+function _civicrm_api3_get_using_utils_sql($dao_name, $params, $isFillUniqueFields, $extraMysql) {
+
$dao = new $dao_name();
$entity = _civicrm_api_get_entity_name_from_dao($dao);
- $entity_fields = _civicrm_api3_build_fields_array($dao, TRUE);
- $where_fields = array_intersect(array_keys($entity_fields), array_keys($params));
+ $custom_fields = _civicrm_api3_custom_fields_for_entity($entity);
+ $options = _civicrm_api3_get_options_from_params($params);
+ // Unset $params['options'] if they are api parameters (not options as a fieldname).
+ if (!empty($params['options']) && is_array($params['options'])&& array_intersect(array_keys($params['options']), array_keys($options))) {
+ unset ($params['options']);
+ }
+
+ $entity_field_names = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($dao));
+ $custom_field_names = array();
+ $getFieldsResult = civicrm_api3($entity, 'getfields', array('action' => 'get'));
+ $getFieldsResult = $getFieldsResult['values'];
+ // $select_fields maps column names to the field names of the result
+ // values.
+ $select_fields = array();
- // TODO: find out which fields to select
- $select_fields = "title,id";
- $quantified_select_fields = "a.title,a.id";
+ // array with elements array('column', 'operator', 'value');
+ $where_clauses = array();
- $select = "SELECT $quantified_select_fields ";
- $from = "FROM " . $dao->tableName() . " a ";
- $where = "WHERE 1=1 ";
+ // Tables we need to join with to retrieve the custom values.
+ $custom_value_tables = array();
- $query_params = array();
- $custom_field_wheres = array();
+ // ID's of custom fields that refer to a contact.
+ $contact_reference_field_ids = array();
- foreach($params as $key => $value) {
- // TODO: values of the form array("op" => "value")
- if (in_array($key, $where_fields)) {
- $param_nr = count($query_params) + 1;
- // TODO: handle e.g. DateTime, null, ...
- $query_params[$param_nr] = array($value, 'String');
- $where .= "AND a.$key = %$param_nr ";
+ // populate $select_fields
+ $return_all_fields = (empty($options['return']) || !is_array($options['return']));
+
+ // default fields
+ foreach ($entity_field_names as $field_name) {
+ if ($return_all_fields || !empty($options['return'][$field_name])) {
+ // 'a.' is an alias for the entity table.
+ $select_fields["a.$field_name"] = $field_name;
}
- else {
- $cf_id = CRM_Core_BAO_CustomField::getKeyID($key);
- if ($cf_id) {
- $custom_field_where[$cf_id] = $value;
+ }
+
+ // custom fields
+ foreach ($custom_fields as $cf_id => $custom_field) {
+ $field_name = "custom_$cf_id";
+ $custom_field_names[] = $field_name;
+ if ($return_all_fields || !empty($options['return'][$field_name])
+ ||
+ // This is a tested format so we support it.
+ !empty($options['return']['custom'])
+ ) {
+ $table_name = $custom_field["table_name"];
+ $column_name = $custom_field["column_name"];
+ // remember that we will need to join the correct table.
+ if (!in_array($table_name, $custom_value_tables)) {
+ $custom_value_tables[] = $table_name;
+ }
+ if ($custom_field["data_type"] != "ContactReference") {
+ // 'ordinary' custom field. We will select the value as custom_XX.
+ $select_fields["$table_name.$column_name"] = $field_name;
+ }
+ else {
+ // contact reference custom field. The ID will be stored in
+ // custom_XX_id. custom_XX will contain the sort name of the
+ // contact.
+ $contact_reference_field_ids[] = $cf_id;
+ $select_fields["$table_name.$column_name"] = $field_name . "_id";
+ // We will call the contact table for the join c_XX.
+ $select_fields["c_$cf_id.sort_name"] = $field_name;
}
}
- };
+ }
+ if (!in_array("a.id", $select_fields)) {
+ // Always select the ID.
+ $select_fields["a.id"] = "id";
+ }
+ // build query
+ $query = CRM_Utils_SQL_Select::from($dao->tableName() . " a");
- // find details of the relevant custom fields
- $id_string = implode(',', array_keys($custom_field_where));
- $cf_query = "
-SELECT f.id, f.label, f.data_type,
- f.html_type, f.is_search_range,
- f.option_group_id, f.custom_group_id,
- f.column_name, g.table_name,
- f.date_format,f.time_format
- FROM civicrm_custom_field f,
- civicrm_custom_group g
- WHERE f.custom_group_id = g.id
- AND g.is_active = 1
- AND f.is_active = 1
- AND f.id IN ( $id_string )";
+ // populate $where_clauses
+ foreach ($params as $key => $value) {
+ $type = 'String';
+ if (array_key_exists($key, $getFieldsResult)) {
+ $type = $getFieldsResult[$key]['type'];
+ $key = $getFieldsResult[$key]['name'];
+ }
+ if (in_array($key, $entity_field_names)) {
+ $table_name = 'a';
+ $column_name = $key;
+ }
+ elseif (($cf_id = CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
+ $table_name = $custom_fields[$cf_id]["table_name"];
+ $column_name = $custom_fields[$cf_id]["column_name"];
- $tables_to_join = array();
- $cf_dao = CRM_Core_DAO::executeQuery($cf_query);
- while ($cf_dao->fetch()) {
- $column_name = $cf_dao->column_name;
- if (!in_array($cf_dao->table_name, $tables_to_join)) {
- $tables_to_join[] = $cf_dao->table_name;
+ if (!in_array($table_name, $custom_value_tables)) {
+ $custom_value_tables[] = $table_name;
+ }
+ }
+ // I don't know why I had to specifically exclude 0 as a key - wouldn't the others have caught it?
+ // We normally silently ignore null values passed in - if people want IS_NULL they can use acceptedSqlOperator syntax.
+ if ((!in_array($key, $entity_field_names) && !in_array($key, $custom_field_names)) || empty($key) || is_null($value)) {
+ // No valid filter field. This might be a chained call or something.
+ // Just ignore this for the $where_clause.
+ continue;
+ }
+ if (!is_array($value)) {
+ $query->where(array("{$table_name}.{$column_name} = @value"), array(
+ "@value" => $value,
+ ));
+ }
+ else {
+ // We expect only one element in the array, of the form
+ // "operator" => "rhs".
+ $operator = CRM_Utils_Array::first(array_keys($value));
+ if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
+ $query->where(array(
+ "{$table_name}.{$column_name} = @value"), array("@value" => $value)
+ );
+ }
+ else {
+ $query->where(CRM_Core_DAO::createSQLFilter('a.' . $column_name, $value, $type));
+ }
}
- $param_nr = count($query_params) + 1;
- // TODO: handle e.g. DateTimes, null, ...
- $query_params[$param_nr] = array($custom_field_where[$cf_dao->id], 'String');
- $where .= "AND $cf_dao->table_name.$cf_dao->column_name = %$param_nr ";
}
- foreach($tables_to_join as $table_name) {
- $from .= "LEFT OUTER JOIN $table_name ON $table_name.entity_id = a.id ";
+ $i = 0;
+ if (!$options['is_count']) {
+ foreach ($select_fields as $column => $alias) {
+ ++$i;
+ $query = $query->select("!column_$i as !alias_$i", array(
+ "!column_$i" => $column,
+ "!alias_$i" => $alias,
+ ));
+ }
+ }
+ else {
+ $query->select("count(*) as c");
}
- $query = "$select $from $where";
+ // join with custom value tables
+ foreach ($custom_value_tables as $table_name) {
+ ++$i;
+ $query = $query->join(
+ "!table_name_$i",
+ "LEFT OUTER JOIN !table_name_$i ON !table_name_$i.entity_id = a.id",
+ array("!table_name_$i" => $table_name)
+ );
+ }
+
+ // join with contact for contact reference fields
+ foreach ($contact_reference_field_ids as $field_id) {
+ ++$i;
+ $query = $query->join(
+ "!contact_table_name$i",
+ "LEFT OUTER JOIN civicrm_contact !contact_table_name_$i ON !contact_table_name_$i.id = !values_table_name_$i.!column_name_$i",
+ array(
+ "!contact_table_name_$i" => "c_$field_id",
+ "!values_table_name_$i" => $custom_fields[$field_id]["table_name"],
+ "!column_name_$i" => $custom_fields[$field_id]["column_name"],
+ ));
+ };
+
+ foreach ($where_clauses as $clause) {
+ ++$i;
+ if (substr($clause[1], -4) == "NULL") {
+ $query->where("!columnName_$i !nullThing_$i", array(
+ "!columnName_$i" => $clause[0],
+ "!nullThing_$i" => $clause[1],
+ ));
+ }
+ else {
+ $query->where("!columnName_$i !operator_$i @value_$i", array(
+ "!columnName_$i" => $clause[0],
+ "!operator_$i" => $clause[1],
+ "@value_$i" => $clause[2],
+ ));
+ }
+ };
+ if (!empty($extraMysql['where'])) {
+ foreach ($extraMysql['where'] as $extraWhere) {
+ $query->where($extraWhere);
+ }
+ }
+
+ // order by
+ if (!empty($options['sort'])) {
+ $sort_fields = array();
+ foreach (explode(',', $options['sort']) as $sort_option) {
+ $words = preg_split("/[\s]+/", $sort_option);
+ if (count($words) > 0 && in_array($words[0], array_values($select_fields))) {
+ $tmp = $words[0];
+ if (strtoupper($words[1]) == 'DESC') {
+ $tmp .= " DESC";
+ }
+ $sort_fields[] = $tmp;
+ }
+ }
+ if (count($sort_fields) > 0) {
+ $query->orderBy(implode(",", $sort_fields));
+ }
+ }
- // TODO: limit, sort,...
+ // limit
+ if (!empty($options['limit']) || !empty($options['offset'])) {
+ $query->limit($options['limit'], $options['offset']);
+ }
$result_entities = array();
+ $result_dao = CRM_Core_DAO::executeQuery($query->toSQL());
- $result_dao = CRM_Core_DAO::executeQuery($query, $query_params);
while ($result_dao->fetch()) {
+ if ($options['is_count']) {
+ $result_dao->free();
+ return (int) $result_dao->c;
+ }
$result_entities[$result_dao->id] = array();
- foreach (explode(",", $select_fields) as $field) {
- $result_entities[$result_dao->id][$field] = $result_dao->$field;
+ foreach ($select_fields as $column => $alias) {
+ if (property_exists($result_dao, $alias)) {
+ $result_entities[$result_dao->id][$alias] = $result_dao->$alias;
+ }
+ // Backward compatibility on fields names.
+ if ($isFillUniqueFields && !empty($getFieldsResult['values'][$column]['uniqueName'])) {
+ $result_entities[$result_dao->id][$getFieldsResult['values'][$column]['uniqueName']] = $result_dao->$alias;
+ }
+ foreach ($getFieldsResult as $returnName => $spec) {
+ $castToInt = FALSE;
+ if (!empty($spec['type']) && $spec['type'] == CRM_Utils_Type::T_INT) {
+ $castToInt = TRUE;
+ }
+ if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) {
+ $result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']];
+ }
+ }
};
}
+ $result_dao->free();
+ return $result_entities;
+}
- return civicrm_api3_create_success($result_entities, $params, $entity, 'get', $dao);
+/**
+ * Returns field names of the given entity fields.
+ *
+ * @param array $fields
+ * Fields array to retrieve the field names for.
+ * @return array
+ */
+function _civicrm_api3_field_names($fields) {
+ $result = array();
+ foreach ($fields as $key => $value) {
+ if (!empty($value['name'])) {
+ $result[] = $value['name'];
+ }
+ }
+ return $result;
+}
+
+/**
+ * Returns an array with database information for the custom fields of an
+ * entity.
+ *
+ * Something similar might already exist in CiviCRM. But I was not
+ * able to find it.
+ *
+ * @param string $entity
+ *
+ * @return array
+ * an array that maps the custom field ID's to table name and
+ * column name. E.g.:
+ * {
+ * '1' => array {
+ * 'table_name' => 'table_name_1',
+ * 'column_name' => 'column_name_1',
+ * 'data_type' => 'data_type_1',
+ * },
+ * }
+ */
+function _civicrm_api3_custom_fields_for_entity($entity) {
+ $result = array();
+
+ $query = "
+SELECT f.id, f.label, f.data_type,
+ f.html_type, f.is_search_range,
+ f.option_group_id, f.custom_group_id,
+ f.column_name, g.table_name,
+ f.date_format,f.time_format
+ FROM civicrm_custom_field f
+ JOIN civicrm_custom_group g ON f.custom_group_id = g.id
+ WHERE g.is_active = 1
+ AND f.is_active = 1
+ AND g.extends = %1";
+
+ $params = array(
+ '1' => array($entity, 'String'),
+ );
+
+ $dao = CRM_Core_DAO::executeQuery($query, $params);
+ while ($dao->fetch()) {
+ $result[$dao->id] = array(
+ 'table_name' => $dao->table_name,
+ 'column_name' => $dao->column_name,
+ 'data_type' => $dao->data_type,
+ );
+ }
+ $dao->free();
+
+ return $result;
}
/**
* @param CRM_Core_DAO $dao
* @param array $params
* @param bool $unique
+ * @param array $extraSql
+ * API specific queries eg for event isCurrent would be converted to
+ * $extraSql['where'] = array('civicrm_event' => array('(start_date >= CURDATE() || end_date >= CURDATE())'));
*
* @throws API_Exception
* @throws Exception
*/
-function _civicrm_api3_dao_set_filter(&$dao, $params, $unique = TRUE) {
+function _civicrm_api3_dao_set_filter(&$dao, $params, $unique = TRUE, $extraSql = array()) {
$entity = _civicrm_api_get_entity_name_from_dao($dao);
$lowercase_entity = _civicrm_api_get_entity_name_from_camel($entity);
if (!empty($params[$lowercase_entity . "_id"]) && empty($params['id'])) {
}
}
}
+ if (!empty($extraSql['where'])) {
+ foreach ($extraSql['where'] as $table => $sqlWhere) {
+ foreach ($sqlWhere as $where) {
+ $dao->whereAdd($where);
+ }
+ }
+ }
if (!empty($options['return']) && is_array($options['return']) && empty($options['is_count'])) {
$dao->selectAdd();
// Ensure 'id' is included.
$dao->setApiFilter($params);
}
-
/**
* Apply filters (e.g. high, low) to DAO object (prior to find).
*
return $options;
}
//here comes the legacy support for $returnProperties, $inputParams e.g for contat_get
- // if the queryobject is being used this should be used
+ // if the query object is being used this should be used
$inputParams = array();
$legacyreturnProperties = array();
$otherVars = array(
return $dao->count;
}
- $fields = array_keys(_civicrm_api3_build_fields_array($dao, $uniqueFields));
-
+ $fields = array_keys(_civicrm_api3_build_fields_array($dao, FALSE));
while ($dao->fetch()) {
$tmp = array();
foreach ($fields as $key) {
return TRUE;
}
}
+
/**
* Converts an object to an array.
*
* @param bool $returnAsSuccess
* Return in api success format.
* @param string $entity
+ * @param array $extraSql
+ * API specific queries eg for event isCurrent would be converted to
+ * $extraSql['where'] = array('civicrm_event' => array('(start_date >= CURDATE() || end_date >= CURDATE())'));
+ * @param bool $uniqueFields
+ * Should unique field names be returned (for backward compatibility)
*
* @return array
*/
-function _civicrm_api3_basic_get($bao_name, &$params, $returnAsSuccess = TRUE, $entity = "") {
- $bao = new $bao_name();
- _civicrm_api3_dao_set_filter($bao, $params, TRUE);
+function _civicrm_api3_basic_get($bao_name, &$params, $returnAsSuccess = TRUE, $entity = "", $extraSql = array(), $uniqueFields = FALSE) {
+
if ($returnAsSuccess) {
- return civicrm_api3_create_success(_civicrm_api3_dao_to_array($bao, $params, FALSE, $entity), $params, $entity, 'get');
+ return civicrm_api3_create_success(_civicrm_api3_get_using_utils_sql($bao_name, $params, $uniqueFields, $extraSql), $params, $entity, 'get');
}
else {
- return _civicrm_api3_dao_to_array($bao, $params, FALSE, $entity, 'get');
+ return _civicrm_api3_get_using_utils_sql($bao_name, $params, $uniqueFields, $extraSql);
}
}
* @param $fieldName
* any variation of a field's name (name, unique_name, api.alias).
*
+ * @param string $action
+ *
* @return bool|string
- * fieldName or FALSE if the field does not exist
+ * FieldName or FALSE if the field does not exist
*/
function _civicrm_api3_api_resolve_alias($entity, $fieldName, $action = 'create') {
if (!$fieldName) {
* _civicrm_api3_basic_get but does not use DAO/BAO. This is useful for
* small/mid-size data loaded from external JSON or XML documents.
*
+ * @param $entity
* @param array $params
* API parameters.
* @param array $records
* The property which defines the ID of a record
* @param array $fields
* List of filterable fields.
+ *
* @return array
+ * @throws \API_Exception
*/
function _civicrm_api3_basic_array_get($entity, $params, $records, $idCol, $fields) {
$options = _civicrm_api3_get_options_from_params($params, TRUE, $entity, 'get');
projects / civicrm-core.git / blobdiff