public $_pseudoConstantsSelect = array();
+ public $_groupUniqueKey = NULL;
+ public $_groupKeys = [];
+
/**
* Class constructor which also does all the work.
*
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) {
$smartGroupIDs[] = $id;
}
else {
- $regularGroupIDs[] = $id;
+ $regularGroupIDs[] = trim($id);
}
}
if (count($regularGroupIDs) > 1) {
$op = strpos($op, 'IN') ? $op : ($op == '!=') ? 'NOT IN' : 'IN';
}
- $groupIds = implode(',', (array) $regularGroupIDs);
+ $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");
//CRM-19589: contact(s) removed from a Smart Group, resides in civicrm_group_contact table
$groupContactCacheClause = '';
if (count($smartGroupIDs) || empty($value)) {
- $gccTableAlias = "civicrm_group_contact_cache";
+ $this->_groupUniqueKey = uniqid();
+ $this->_groupKeys[] = $this->_groupUniqueKey;
+ $gccTableAlias = "civicrm_group_contact_cache_{$this->_groupUniqueKey}";
$groupContactCacheClause = $this->addGroupContactCache($smartGroupIDs, $gccTableAlias, "contact_a", $op);
if (!empty($groupContactCacheClause)) {
if ($isNotOp) {
$groupIds = implode(',', (array) $smartGroupIDs);
- $gcTable = "civicrm_group_contact";
+ $gcTable = "civicrm_group_contact_{$this->_groupUniqueKey}";
$joinClause = array("contact_a.id = {$gcTable}.contact_id");
$this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
if (strpos($op, 'IN') !== FALSE) {
}
}
+ public function getGroupCacheTableKeys() {
+ return $this->_groupKeys;
+ }
+
/**
* Function translates selection of group type into a list of groups.
* @param $value
$op = "LIKE";
$value = "%{$value}%";
+ $escapedValue = CRM_Utils_Type::escape("%{$value}%", 'String');
$useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
$tagTypesText = $this->getWhereValues('tag_types_text', $grouping);
- $etTable = "`civicrm_entity_tag-" . $value . "`";
- $tTable = "`civicrm_tag-" . $value . "`";
+ $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
+ $tTable = "`civicrm_tag-" . uniqid() . "`";
if ($useAllTagTypes[2]) {
$this->_tables[$etTable] = $this->_whereTables[$etTable]
LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id )";
// search tag in cases
- $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
- $tCaseTable = "`civicrm_case_tag-" . $value . "`";
+ $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
+ $tCaseTable = "`civicrm_case_tag-" . uniqid() . "`";
$this->_tables[$etCaseTable] = $this->_whereTables[$etCaseTable]
= " LEFT JOIN civicrm_case_contact ON civicrm_case_contact.contact_id = contact_a.id
LEFT JOIN civicrm_case
LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id )
LEFT JOIN civicrm_tag {$tCaseTable} ON ( {$etCaseTable}.tag_id = {$tCaseTable}.id )";
// search tag in activities
- $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
- $tActTable = "`civicrm_act_tag-" . $value . "`";
+ $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
+ $tActTable = "`civicrm_act_tag-" . uniqid() . "`";
$activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
LEFT JOIN civicrm_entity_tag as {$etActTable} ON ( {$etActTable}.entity_table = 'civicrm_activity' AND {$etActTable}.entity_id = civicrm_activity.id )
LEFT JOIN civicrm_tag {$tActTable} ON ( {$etActTable}.tag_id = {$tActTable}.id )";
- $this->_where[$grouping][] = "({$tTable}.name $op '" . $value . "' OR {$tCaseTable}.name $op '" . $value . "' OR {$tActTable}.name $op '" . $value . "')";
+ $this->_where[$grouping][] = "({$tTable}.name $op '" . $escapedValue . "' OR {$tCaseTable}.name $op '" . $escapedValue . "' OR {$tActTable}.name $op '" . $escapedValue . "')";
$this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $tagTypesText[2], 2 => $op)) . ' ' . $value;
}
else {
- $etTable = "`civicrm_entity_tag-" . $value . "`";
- $tTable = "`civicrm_tag-" . $value . "`";
+ $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
+ $tTable = "`civicrm_tag-" . uniqid() . "`";
$this->_tables[$etTable] = $this->_whereTables[$etTable] = " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id AND
{$etTable}.entity_table = 'civicrm_contact' )
LEFT JOIN civicrm_tag {$tTable} ON ( {$etTable}.tag_id = {$tTable}.id ) ";
if (count($value) > 1) {
$this->_useDistinct = TRUE;
}
- $value = implode(',', (array) $value);
+ }
+
+ // 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);
- $etTable = "`civicrm_entity_tag-" . $value . "`";
+ $etTable = "`civicrm_entity_tag-" . uniqid() . "`";
if ($useAllTagTypes[2]) {
$this->_tables[$etTable] = $this->_whereTables[$etTable]
= " LEFT JOIN civicrm_entity_tag {$etTable} ON ( {$etTable}.entity_id = contact_a.id AND {$etTable}.entity_table = 'civicrm_contact') ";
// search tag in cases
- $etCaseTable = "`civicrm_entity_case_tag-" . $value . "`";
+ $etCaseTable = "`civicrm_entity_case_tag-" . uniqid() . "`";
$activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
AND civicrm_case.is_deleted = 0 )
LEFT JOIN civicrm_entity_tag {$etCaseTable} ON ( {$etCaseTable}.entity_table = 'civicrm_case' AND {$etCaseTable}.entity_id = civicrm_case.id ) ";
// search tag in activities
- $etActTable = "`civicrm_entity_act_tag-" . $value . "`";
+ $etActTable = "`civicrm_entity_act_tag-" . uniqid() . "`";
$this->_tables[$etActTable] = $this->_whereTables[$etActTable]
= " LEFT JOIN civicrm_activity_contact
ON ( civicrm_activity_contact.contact_id = contact_a.id AND civicrm_activity_contact.record_type_id = {$targetID} )
$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,
+ ]);
}
/**
$additionalFromClause = NULL, $skipOrderAndLimit = FALSE
) {
- if ($includeContactIds) {
- $this->_includeContactIds = TRUE;
- $this->_whereClause = $this->whereClause();
- }
-
- $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params);
-
- // if we’re explicitly looking for a certain contact’s contribs, events, etc.
- // and that contact happens to be deleted, set $onlyDeleted to true
- foreach ($this->_params as $values) {
- $name = CRM_Utils_Array::value(0, $values);
- $op = CRM_Utils_Array::value(1, $values);
- $value = CRM_Utils_Array::value(2, $values);
- if ($name == 'contact_id' and $op == '=') {
- if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
- $onlyDeleted = TRUE;
- }
- break;
- }
- }
-
- // building the query string
- $groupBy = $groupByCols = NULL;
- if (!$count) {
- if (isset($this->_groupByComponentClause)) {
- $groupBy = $this->_groupByComponentClause;
- $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause));
- $groupByCols = explode(', ', $groupByCols);
- }
- elseif ($this->_useGroupBy) {
- $groupByCols = array('contact_a.id');
- }
- }
- if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
- $groupByCols = array('civicrm_activity.id');
- }
-
- $order = $orderBy = $limit = '';
- 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
- // MySQL expect the columns present in GROUP BY, must be present in SELECT clause and that results into error, needless to have other columns.
- // 2. When GROUP BY columns are present then disable FGB otherwise it demands to add ORDER BY columns in GROUP BY and eventually in SELECT
- // clause. This will impact the search query output.
- $disableFullGroupByMode = ($sortByChar || !empty($groupByCols) || $groupContacts);
-
- if ($disableFullGroupByMode) {
- CRM_Core_DAO::disableFullGroupByMode();
- }
-
- // CRM-15231
- $this->_sort = $sort;
-
- //CRM-15967
- $this->includePseudoFieldsJoin($sort);
-
- list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
-
- if (!empty($groupByCols)) {
- $groupBy = " GROUP BY " . implode(', ', $groupByCols);
- }
-
- if ($additionalWhereClause) {
- $where = $where . ' AND ' . $additionalWhereClause;
- }
-
- //additional from clause should be w/ proper joins.
- if ($additionalFromClause) {
- $from .= "\n" . $additionalFromClause;
- }
-
- // if we are doing a transform, do it here
- // use the $from, $where and $having to get the contact ID
- if ($this->_displayRelationshipType) {
- $this->filterRelatedContacts($from, $where, $having);
- }
-
- if ($skipOrderAndLimit) {
- $query = "$select $from $where $having $groupBy";
- }
- else {
- $query = "$select $from $where $having $groupBy $order $limit";
- }
+ $query = $this->getSearchSQL($offset, $rowCount, $sort, $count, $includeContactIds, $sortByChar, $groupContacts, $additionalWhereClause, $sortOrder, $additionalFromClause, $skipOrderAndLimit);
if ($returnQuery) {
return $query;
$dao = CRM_Core_DAO::executeQuery($query);
- if ($disableFullGroupByMode) {
- CRM_Core_DAO::reenableFullGroupByMode();
- }
+ // We can always call this - it will only re-enable if it was originally enabled.
+ CRM_Core_DAO::reenableFullGroupByMode();
if ($groupContacts) {
$ids = array();
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
*
* list(string $orderByClause, string $additionalFromClause).
*/
protected function prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause) {
- $order = NULL;
- $orderByArray = array();
- $config = CRM_Core_Config::singleton();
- if ($config->includeOrderByClause ||
+ $orderByArray = [];
+ $orderBy = '';
+
+ if (CRM_Core_Config::singleton()->includeOrderByClause ||
isset($this->_distinctComponentClause)
) {
if ($sort) {
$orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
}
- $order = " ORDER BY $orderBy";
-
if ($sortOrder) {
- $order .= " $sortOrder";
+ $orderBy .= " $sortOrder";
}
// always add contact_a.id to the ORDER clause
// so the order is deterministic
- if (strpos('contact_a.id', $order) === FALSE) {
- $order .= ", contact_a.id";
+ if (strpos('contact_a.id', $orderBy) === FALSE) {
+ $orderBy .= ", contact_a.id";
}
}
}
elseif ($sortByChar) {
- $orderByArray = array("UPPER(LEFT(contact_a.sort_name, 1)) asc");
+ $orderBy = " sort_name asc";
}
else {
- $order = " ORDER BY contact_a.sort_name ASC, contact_a.id";
+ $orderBy = " contact_a.sort_name ASC, contact_a.id";
}
}
- if (!$order && empty($orderByArray)) {
- return array($order, $additionalFromClause);
+ if (!$orderBy) {
+ return [NULL, $additionalFromClause];
}
// Remove this here & add it at the end for simplicity.
- $order = trim(str_replace('ORDER BY', '', $order));
+ $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
+ * 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.
+ *
+ * @param bool $skipOrderAndLimit
+ * @return string
+ */
+ public function getSearchSQL(
+ $offset = 0, $rowCount = 0, $sort = NULL,
+ $count = FALSE, $includeContactIds = FALSE,
+ $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',
+ '=',
+ '1',
+ '0',
+ '0'
+ ], $this->_params);
+
+ // if we’re explicitly looking for a certain contact’s contribs, events, etc.
+ // and that contact happens to be deleted, set $onlyDeleted to true
+ foreach ($this->_params as $values) {
+ $name = CRM_Utils_Array::value(0, $values);
+ $op = CRM_Utils_Array::value(1, $values);
+ $value = CRM_Utils_Array::value(2, $values);
+ if ($name == 'contact_id' and $op == '=') {
+ if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'is_deleted')) {
+ $onlyDeleted = TRUE;
+ }
+ break;
+ }
+ }
+
+ // building the query string
+ $groupBy = $groupByCols = NULL;
+ if (!$count) {
+ if (isset($this->_groupByComponentClause)) {
+ $groupByCols = preg_replace('/^GROUP BY /', '', trim($this->_groupByComponentClause));
+ $groupByCols = explode(', ', $groupByCols);
+ }
+ elseif ($this->_useGroupBy) {
+ $groupByCols = ['contact_a.id'];
+ }
+ }
+ if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) {
+ $groupByCols = ['civicrm_activity.id'];
+ }
+ if (!empty($groupByCols)) {
+ $groupBy = " GROUP BY " . implode(', ', $groupByCols);
+ }
+
+ $order = $orderBy = '';
+ if (!$count) {
+ list($order, $additionalFromClause) = $this->prepareOrderBy($sort, $sortByChar, $sortOrder, $additionalFromClause);
+ }
+ // 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
+ // MySQL expect the columns present in GROUP BY, must be present in SELECT clause and that results into error, needless to have other columns.
+ // 2. When GROUP BY columns are present then disable FGB otherwise it demands to add ORDER BY columns in GROUP BY and eventually in SELECT
+ // clause. This will impact the search query output.
+ $disableFullGroupByMode = ($sortByChar || !empty($groupBy) || $groupContacts);
+
+ if ($disableFullGroupByMode) {
+ CRM_Core_DAO::disableFullGroupByMode();
+ }
+
+ // CRM-15231
+ $this->_sort = $sort;
+
+ //CRM-15967
+ $this->includePseudoFieldsJoin($sort);
+
+ list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
+
+ if ($additionalWhereClause) {
+ $where = $where . ' AND ' . $additionalWhereClause;
+ }
+
+ //additional from clause should be w/ proper joins.
+ if ($additionalFromClause) {
+ $from .= "\n" . $additionalFromClause;
+ }
+
+ // if we are doing a transform, do it here
+ // use the $from, $where and $having to get the contact ID
+ 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,
+ ];
+ }
+
+ /**
+ * 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';
+ }
+ $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 $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