/** * Build & execute the query and return results array * * @return array * @throws \API_Exception * @throws \CRM_Core_Exception * @throws \Exception */ public function run() { $this->buildSelectFields(); $this->buildWhereClause(); if (in_array('count_rows', $this->select)) { $this->query->select("count(*) as c"); } else { foreach ($this->selectFields as $column => $alias) { $this->query->select("{$column} as `{$alias}`"); } // Order by $this->buildOrderBy(); } // Limit if (!empty($this->limit) || !empty($this->offset)) { $this->query->limit($this->limit, $this->offset); } $result_entities = array(); $result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL()); while ($result_dao->fetch()) { if (in_array('count_rows', $this->select)) { $result_dao->free(); return (int) $result_dao->c; } $result_entities[$result_dao->id] = array(); foreach ($this->selectFields as $column => $alias) { $returnName = $alias; $alias = str_replace('.', '_', $alias); if (property_exists($result_dao, $alias) && $result_dao->{$alias} != NULL) { $result_entities[$result_dao->id][$returnName] = $result_dao->{$alias}; } // Backward compatibility on fields names. if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) { $result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->{$alias}; } foreach ($this->apiFieldSpec as $returnName => $spec) { 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; }
/** * Build & execute the query and return results array * * @return array * @throws \API_Exception * @throws \CRM_Core_Exception * @throws \Exception */ public function run() { // $select_fields maps column names to the field names of the result values. $select_fields = $custom_fields = array(); // populate $select_fields $return_all_fields = empty($this->options['return']) || !is_array($this->options['return']); $return = $return_all_fields ? array_fill_keys($this->entityFieldNames, 1) : $this->options['return']; // core return fields foreach ($return as $field_name => $include) { if ($include) { $field = $this->getField($field_name); if ($field && in_array($field['name'], $this->entityFieldNames)) { // 'a.' is an alias for the entity table. $select_fields["a.{$field['name']}"] = $field['name']; } elseif ($include && strpos($field_name, '.')) { $fkField = $this->addFkField($field_name); if ($fkField) { $select_fields[implode('.', $fkField)] = $field_name; } } } } // Do custom fields IF the params contain the word "custom" or we are returning * if ($return_all_fields || strpos(json_encode($this->params), 'custom')) { $custom_fields = _civicrm_api3_custom_fields_for_entity($this->entity); foreach ($custom_fields as $cf_id => $custom_field) { $field_name = "custom_{$cf_id}"; if ($return_all_fields || !empty($this->options['return'][$field_name]) || !empty($this->options['return']['custom'])) { list($table_name, $column_name) = $this->addCustomField($custom_field); 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. $this->query->join("c_{$cf_id}", "LEFT JOIN civicrm_contact c_{$cf_id} ON c_{$cf_id}.id = `{$table_name}`.`{$column_name}`"); $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; } } } } // Always select the ID. $select_fields["a.id"] = "id"; // populate where_clauses foreach ($this->params as $key => $value) { $table_name = NULL; $column_name = NULL; if (substr($key, 0, 7) == 'filter.') { // Legacy support for old filter syntax per the test contract. // (Convert the style to the later one & then deal with them). $filterArray = explode('.', $key); $value = array($filterArray[1] => $value); $key = 'filters'; } // Legacy support for 'filter's construct. if ($key == 'filters') { foreach ($value as $filterKey => $filterValue) { if (substr($filterKey, -4, 4) == 'high') { $key = substr($filterKey, 0, -5); $value = array('<=' => $filterValue); } if (substr($filterKey, -3, 3) == 'low') { $key = substr($filterKey, 0, -4); $value = array('>=' => $filterValue); } if ($filterKey == 'is_current' || $filterKey == 'isCurrent') { // Is current is almost worth creating as a 'sql filter' in the DAO function since several entities have the // concept. $todayStart = date('Ymd000000', strtotime('now')); $todayEnd = date('Ymd235959', strtotime('now')); $this->query->where(array("(a.start_date <= '{$todayStart}' OR a.start_date IS NULL) AND (a.end_date >= '{$todayEnd}' OR\n a.end_date IS NULL)\n AND a.is_active = 1\n ")); } } } // Ignore the "options" param if it is referring to api options and not a field in this entity if ($key === 'options' && is_array($value) && !in_array(\CRM_Utils_Array::first(array_keys($value)), \CRM_Core_DAO::acceptedSQLOperators())) { continue; } $field = $this->getField($key); if ($field) { $key = $field['name']; } if (in_array($key, $this->entityFieldNames)) { $table_name = 'a'; $column_name = $key; } elseif (($cf_id = \CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) { list($table_name, $column_name) = $this->addCustomField($custom_fields[$cf_id]); } elseif (strpos($key, '.')) { $fkInfo = $this->addFkField($key); if ($fkInfo) { list($table_name, $column_name) = $fkInfo; $this->validateNestedInput($key, $value); } } // 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 (!$table_name || 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)) { $this->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())) { $this->query->where(array("{$table_name}.{$column_name} = @value"), array("@value" => $value)); } else { $this->query->where(\CRM_Core_DAO::createSQLFilter("{$table_name}.{$column_name}", $value)); } } } if (!$this->options['is_count']) { foreach ($select_fields as $column => $alias) { $this->query->select("{$column} as `{$alias}`"); } } else { $this->query->select("count(*) as c"); } // order by if (!empty($this->options['sort'])) { $sort_fields = array(); foreach (explode(',', $this->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 (!empty($words[1]) && strtoupper($words[1]) == 'DESC') { $tmp .= " DESC"; } $sort_fields[] = $tmp; } } if (count($sort_fields) > 0) { $this->query->orderBy(implode(",", $sort_fields)); } } // limit if (!empty($this->options['limit']) || !empty($this->options['offset'])) { $this->query->limit($this->options['limit'], $this->options['offset']); } // ACLs $this->query->where($this->getAclClause('a')); $this->bao->free(); $result_entities = array(); $result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL()); while ($result_dao->fetch()) { if ($this->options['is_count']) { $result_dao->free(); return (int) $result_dao->c; } $result_entities[$result_dao->id] = array(); foreach ($select_fields as $column => $alias) { $returnName = $alias; $alias = str_replace('.', '_', $alias); if (property_exists($result_dao, $alias) && $result_dao->{$alias} != NULL) { $result_entities[$result_dao->id][$returnName] = $result_dao->{$alias}; } // Backward compatibility on fields names. if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) { $result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->{$alias}; } foreach ($this->apiFieldSpec as $returnName => $spec) { 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; }