$component = 'membership';
}
if (isset($component)) {
+ // @todo should be if (isset($component && !$this->_skipPermission)
CRM_Financial_BAO_FinancialType::buildPermissionedClause($this->_whereClause, $component);
}
if (
!empty($this->_paramLookup[$name])
|| !empty($this->_returnProperties[$name])
- || $this->pseudoConstantNameIsInReturnProperties($field)
+ || $this->pseudoConstantNameIsInReturnProperties($field, $name)
|| $makeException
) {
if ($cfID) {
$this->_select[$name] = "{$field['where']} as `$name`";
}
}
+ elseif ($this->pseudoConstantNameIsInReturnProperties($field, $name)) {
+ $this->addPseudoconstantFieldToSelect($name);
+ }
else {
- // If we have an option group defined then rather than joining the option value table in
- // (which is an unindexed join) we render the option value on output.
- // @todo - extend this to other pseudoconstants.
- if ($this->pseudoConstantNameIsInReturnProperties($field, $name)) {
- $pseudoFieldName = $field['pseudoconstant']['optionGroupName'];
- $this->_pseudoConstantsSelect[$pseudoFieldName] = array(
- 'pseudoField' => $field['name'],
- 'idCol' => $name,
- 'field_name' => $field['name'],
- 'bao' => $field['bao'],
- 'pseudoconstant' => $field['pseudoconstant'],
- );
- $this->_tables[$tableName] = 1;
- $this->_element[$pseudoFieldName] = 1;
- }
$this->_select[$name] = str_replace('civicrm_contact.', 'contact_a.', "{$field['where']} as `$name`");
}
if (!in_array($tName, array('state_province', 'country', 'county'))) {
}
}
elseif ($sortByChar) {
- $select = 'SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name';
+ $select = 'SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name';
$from = $this->_simpleFromClause;
}
elseif ($groupContacts) {
if (count($regularGroupIDs) > 1) {
$op = strpos($op, 'IN') ? $op : ($op == '!=') ? 'NOT IN' : 'IN';
}
- $groupIds = CRM_Utils_Type::validate(
- implode(',', (array) $regularGroupIDs),
- 'CommaSeparatedIntegers'
- );
+ $groupIds = '';
+ if (!empty($regularGroupIDs)) {
+ $groupIds = CRM_Utils_Type::validate(
+ implode(',', (array) $regularGroupIDs),
+ 'CommaSeparatedIntegers'
+ );
+ }
$gcTable = "`civicrm_group_contact-" . uniqid() . "`";
$joinClause = array("contact_a.id = {$gcTable}.contact_id");
}
}
- // implode array, then remove all spaces and validate CommaSeparatedIntegers
- $value = CRM_Utils_Type::validate(
- str_replace(' ', '', implode(',', (array) $value)),
- 'CommaSeparatedIntegers'
- );
+ // implode array, then remove all spaces
+ $value = str_replace(' ', '', implode(',', (array) $value));
+ if (!empty($value)) {
+ $value = CRM_Utils_Type::validate(
+ $value,
+ 'CommaSeparatedIntegers'
+ );
+ }
$useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
$tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
$name = $targetName[4] ? "%$name%" : $name;
$this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'";
$this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1;
- $this->_qill[$grouping][] = ts('Modified By') . " $name";
+ $fieldTitle = ts('Added By');
+ foreach ($this->_params as $params) {
+ if ($params[0] == 'log_date') {
+ if ($params[2] == 2) {
+ $fieldTitle = ts('Modified By');
+ }
+ break;
+ }
+ }
+ list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, 'changed_by', $name, 'LIKE');
+ $this->_qill[$grouping][] = ts("%1 %2 '%3'", [
+ 1 => $fieldTitle,
+ 2 => $qillop,
+ 3 => $qillVal,
+ ]);
}
/**
return $dao;
}
+ /**
+ * Create and query the db for a contact search.
+ *
+ * @return CRM_Core_DAO
+ */
+ public function alphabetQuery() {
+ $query = $this->getSearchSQL(NULL, NULL, NULL, FALSE, FALSE, TRUE);
+
+ $dao = CRM_Core_DAO::executeQuery($query);
+
+ // We can always call this - it will only re-enable if it was originally enabled.
+ CRM_Core_DAO::reenableFullGroupByMode();
+
+ return $dao;
+ }
+
/**
* Fetch a list of contacts for displaying a search results page
*
}
}
elseif ($sortByChar) {
- $orderByArray = array("UPPER(LEFT(contact_a.sort_name, 1)) asc");
+ $orderBy = " sort_name asc";
}
else {
$orderBy = " contact_a.sort_name ASC, contact_a.id";
}
}
- if (!$orderBy && empty($orderByArray)) {
+ if (!$orderBy) {
return [NULL, $additionalFromClause];
}
// Remove this here & add it at the end for simplicity.
$order = trim($orderBy);
+ $orderByArray = explode(',', $order);
- // hack for order clause
- if (!empty($orderByArray)) {
- $order = implode(', ', $orderByArray);
- }
- else {
- $orderByArray = explode(',', $order);
- }
foreach ($orderByArray as $orderByClause) {
$orderByClauseParts = explode(' ', trim($orderByClause));
$field = $orderByClauseParts[0];
$this->_select = array_merge($this->_select, $this->_customQuery->_select);
$this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
}
- foreach ($this->_pseudoConstantsSelect as $key => $pseudoConstantMetadata) {
- // By replacing the join to the option value table with the mysql construct
- // ORDER BY field('contribution_status_id', 2,1,4)
- // we can remove a join. In the case of the option value join it is
- /// a join known to cause slow queries.
- // @todo cover other pseudoconstant types. Limited to option group ones in the
- // first instance for scope reasons. They require slightly different handling as the column (label)
- // is not declared for them.
- // @todo so far only integer fields are being handled. If we add string fields we need to look at
- // escaping.
- if (isset($pseudoConstantMetadata['pseudoconstant'])
- && isset($pseudoConstantMetadata['pseudoconstant']['optionGroupName'])
- && $field === CRM_Utils_Array::value('optionGroupName', $pseudoConstantMetadata['pseudoconstant'])
+
+ // By replacing the join to the option value table with the mysql construct
+ // ORDER BY field('contribution_status_id', 2,1,4)
+ // we can remove a join. In the case of the option value join it is
+ /// a join known to cause slow queries.
+ // @todo cover other pseudoconstant types. Limited to option group ones & Foreign keys
+ // matching an id+name parrern in the
+ // first instance for scope reasons. They require slightly different handling as the column (label)
+ // is not declared for them.
+ // @todo so far only integer fields are being handled. If we add string fields we need to look at
+ // escaping.
+ $fieldSpec = $this->getMetadataForRealField($field);
+ $pseudoConstantMetadata = CRM_Utils_Array::value('pseudoconstant', $fieldSpec, FALSE);
+ if (!empty($pseudoConstantMetadata)
+ ) {
+ if (!empty($pseudoConstantMetadata['optionGroupName'])
+ || $this->isPseudoFieldAnFK($fieldSpec)
) {
- $sortedOptions = $pseudoConstantMetadata['bao']::buildOptions($pseudoConstantMetadata['pseudoField'], NULL, array(
- 'orderColumn' => 'label',
- ));
- $order = str_replace("$field $direction", "field({$pseudoConstantMetadata['pseudoField']}," . implode(',', array_keys($sortedOptions)) . ") $direction", $order);
+ $sortedOptions = $fieldSpec['bao']::buildOptions($fieldSpec['name'], NULL, [
+ 'orderColumn' => CRM_Utils_Array::value('labelColumn', $pseudoConstantMetadata, 'label'),
+ ]);
+ $fieldIDsInOrder = implode(',', array_keys($sortedOptions));
+ // Pretty sure this validation ALSO happens in the order clause & this can't be reached but...
+ // this might give some early warning.
+ CRM_Utils_Type::validate($fieldIDsInOrder, 'CommaSeparatedIntegers');
+ $order = str_replace("$field", "field({$fieldSpec['name']},$fieldIDsInOrder)", $order);
}
//CRM-12565 add "`" around $field if it is a pseudo constant
// This appears to be for 'special' fields like locations with appended numbers or hyphens .. maybe.
* @return bool
*/
private function pseudoConstantNameIsInReturnProperties($field, $fieldName = NULL) {
- if (!isset($field['pseudoconstant']['optionGroupName'])) {
+ $realField = $this->getMetadataForRealField($fieldName);
+ if (!isset($realField['pseudoconstant'])) {
+ return FALSE;
+ }
+ $pseudoConstant = $realField['pseudoconstant'];
+ if (empty($pseudoConstant['optionGroupName']) &&
+ CRM_Utils_Array::value('labelColumn', $pseudoConstant) !== 'name') {
+ // We are increasing our pseudoconstant handling - but still very cautiously,
+ // hence the check for labelColumn === name
return FALSE;
}
- if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) {
+ if (!empty($pseudoConstant['optionGroupName']) && CRM_Utils_Array::value($pseudoConstant['optionGroupName'], $this->_returnProperties)) {
return TRUE;
}
if (CRM_Utils_Array::value($fieldName, $this->_returnProperties)) {
return TRUE;
}
// Is this still required - the above goes off the unique name. Test with things like
- // communication_prefferences & prefix_id.
+ // communication_preferences & prefix_id.
if (CRM_Utils_Array::value($field['name'], $this->_returnProperties)) {
return TRUE;
}
}
/**
- * /**
* Create the sql query for an contact search.
*
* @param int $offset
$sortByChar = FALSE, $groupContacts = FALSE,
$additionalWhereClause = NULL, $sortOrder = NULL,
$additionalFromClause = NULL, $skipOrderAndLimit = FALSE) {
+
+ $sqlParts = $this->getSearchSQLParts($offset, $rowCount, $sort, $count, $includeContactIds, $sortByChar, $groupContacts, $additionalWhereClause, $sortOrder, $additionalFromClause);
+
+ if ($skipOrderAndLimit) {
+ CRM_Core_Error::deprecatedFunctionWarning('skipOrderAndLimit is deprected - call getSearchSQLParts & construct it in the calling function');
+ $query = "{$sqlParts['select']} {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']}";
+ }
+ else {
+ $query = "{$sqlParts['select']} {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']} {$sqlParts['order_by']} {$sqlParts['limit']}";
+ }
+ return $query;
+ }
+
+ /**
+ * Get the component parts of the search query as an array.
+ *
+ * @param int $offset
+ * The offset for the query.
+ * @param int $rowCount
+ * The number of rows to return.
+ * @param string|CRM_Utils_Sort $sort
+ * The order by string.
+ * @param bool $count
+ * Is this a count only query ?.
+ * @param bool $includeContactIds
+ * Should we include contact ids?.
+ * @param bool $sortByChar
+ * If true returns the distinct array of first characters for search results.
+ * @param bool $groupContacts
+ * If true, return only the contact ids.
+ * @param string $additionalWhereClause
+ * If the caller wants to further restrict the search (used for components).
+ * @param null $sortOrder
+ * @param string $additionalFromClause
+ * Should be clause with proper joins, effective to reduce where clause load.
+ *
+ * @return array
+ */
+ public function getSearchSQLParts($offset = 0, $rowCount = 0, $sort = NULL,
+ $count = FALSE, $includeContactIds = FALSE,
+ $sortByChar = FALSE, $groupContacts = FALSE,
+ $additionalWhereClause = NULL, $sortOrder = NULL,
+ $additionalFromClause = NULL) {
if ($includeContactIds) {
$this->_includeContactIds = TRUE;
$this->_whereClause = $this->whereClause();
}
-
$onlyDeleted = in_array([
'deleted_contacts',
'=',
$groupBy = " GROUP BY " . implode(', ', $groupByCols);
}
- $order = $orderBy = $limit = '';
+ $order = $orderBy = '';
if (!$count) {
list($order, $additionalFromClause) = $this->prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause);
-
- if ($rowCount > 0 && $offset >= 0) {
- $offset = CRM_Utils_Type::escape($offset, 'Int');
- $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
- $limit = " LIMIT $offset, $rowCount ";
- }
}
// Two cases where we are disabling FGB (FULL_GROUP_BY_MODE):
// 1. Expecting the search query to return all the first single letter characters of contacts ONLY, but when FGB is enabled
if ($this->_displayRelationshipType) {
$this->filterRelatedContacts($from, $where, $having);
}
+ $limit = (!$count && $rowCount) ? " LIMIT " . CRM_Utils_Type::escape($offset, 'Int') . ", " . CRM_Utils_Type::escape($rowCount, 'Int') : '';
+
+ return [
+ 'select' => $select,
+ 'from' => $from,
+ 'where' => $where,
+ 'order_by' => $order,
+ 'group_by' => $groupBy,
+ 'having' => $having,
+ 'limit' => $limit,
+ ];
+ }
- if ($skipOrderAndLimit) {
- $query = "$select $from $where $having $groupBy";
+ /**
+ * Get the metadata for a given field.
+ *
+ * @param string $fieldName
+ *
+ * @return array
+ */
+ protected function getMetadataForField($fieldName) {
+ if ($fieldName === 'contact_a.id') {
+ // This seems to be the only anomaly.
+ $fieldName = 'id';
}
- else {
- $query = "$select $from $where $having $groupBy $order $limit";
+ $pseudoField = isset($this->_pseudoConstantsSelect[$fieldName]) ? $this->_pseudoConstantsSelect[$fieldName] : [];
+ $field = isset($this->_fields[$fieldName]) ? $this->_fields[$fieldName] : $pseudoField;
+ $field = array_merge($field, $pseudoField);
+ if (!empty($field) && empty($field['name'])) {
+ // standardising field formatting here - over time we can phase out variants.
+ // all paths using this currently unit tested
+ $field['name'] = CRM_Utils_Array::value('field_name', $field, CRM_Utils_Array::value('idCol', $field, $fieldName));
}
- return $query;
+ return $field;
+ }
+
+ /**
+ * Get the metadata for a given field, returning the 'real field' if it is a pseudofield.
+ *
+ * @param string $fieldName
+ *
+ * @return array
+ */
+ protected function getMetadataForRealField($fieldName) {
+ $field = $this->getMetadataForField($fieldName);
+ if (!empty($field['is_pseudofield_for'])) {
+ $field = $this->getMetadataForField($field['is_pseudofield_for']);
+ $field['pseudofield_name'] = $fieldName;
+ }
+ elseif (!empty($field['pseudoconstant'])) {
+ if (!empty($field['pseudoconstant']['optionGroupName'])) {
+ $field['pseudofield_name'] = $field['pseudoconstant']['optionGroupName'];
+ if (empty($field['table_name'])) {
+ if (!empty($field['where'])) {
+ $field['table_name'] = explode('.', $field['where'])[0];
+ }
+ else {
+ $field['table_name'] = 'civicrm_contact';
+ }
+ }
+ }
+ }
+ return $field;
+ }
+
+ /**
+ * If we have a field that is better rendered via the pseudoconstant handled them here.
+ *
+ * Rather than joining in the additional table we render the option value on output.
+ *
+ * @todo - so far this applies to a narrow range of pseudocontants. We are adding them
+ * carefully with test coverage but aim to extend.
+ *
+ * @param string $name
+ */
+ protected function addPseudoconstantFieldToSelect($name) {
+ $field = $this->getMetadataForRealField($name);
+ $realFieldName = $field['name'];
+ $pseudoFieldName = CRM_Utils_Array::value('pseudofield_name', $field);
+ if ($pseudoFieldName) {
+ // @todo - we don't really need to build this array now we have metadata more available with getMetadataForField fn.
+ $this->_pseudoConstantsSelect[$pseudoFieldName] = [
+ 'pseudoField' => $pseudoFieldName,
+ 'idCol' => $realFieldName,
+ 'field_name' => $field['name'],
+ 'bao' => $field['bao'],
+ 'pseudoconstant' => $field['pseudoconstant'],
+ ];
+ }
+
+ $this->_tables[$field['table_name']] = 1;
+ $this->_element[$realFieldName] = 1;
+ $this->_select[$field['name']] = str_replace('civicrm_contact.', 'contact_a.', "{$field['where']} as `$realFieldName`");
+ }
+
+ /**
+ * Is this pseudofield a foreign key constraint.
+ *
+ * We are trying to cautiously expand our pseudoconstant handling. This check allows us
+ * to extend to a narrowly defined type (and then only if the pseudofield is in the fields
+ * array which is done for contributions which are mostly handled as pseudoconstants.
+ *
+ * @param $fieldSpec
+ *
+ * @return bool
+ */
+ protected function isPseudoFieldAnFK($fieldSpec) {
+ if (empty($fieldSpec['FKClassName'])
+ || CRM_Utils_Array::value('keyColumn', $fieldSpec['pseudoconstant']) !== 'id'
+ || CRM_Utils_Array::value('labelColumn', $fieldSpec['pseudoconstant']) !== 'name') {
+ return FALSE;
+ }
+ return TRUE;
}
}
projects / civicrm-core.git / blobdiff