X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=2a65e69528668118eee81a39578cc8bd729d8cc3;hb=fe3416bf598349a6e6c6a64d9c66bfc450198581;hp=837d3dbe24977eadd6b349175893fbc4580be54b;hpb=49f927120ccb2b8bc95ddeb6adfc95eb8e22d3da;p=civicrm-core.git diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 837d3dbe24..2a65e69528 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -412,6 +412,9 @@ class CRM_Contact_BAO_Query { public $_pseudoConstantsSelect = array(); + public $_groupUniqueKey = NULL; + public $_groupKeys = []; + /** * Class constructor which also does all the work. * @@ -726,7 +729,7 @@ class CRM_Contact_BAO_Query { if ( !empty($this->_paramLookup[$name]) || !empty($this->_returnProperties[$name]) - || $this->pseudoConstantNameIsInReturnProperties($field) + || $this->pseudoConstantNameIsInReturnProperties($field, $name) || $makeException ) { if ($cfID) { @@ -867,22 +870,10 @@ class CRM_Contact_BAO_Query { $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'))) { @@ -1393,7 +1384,7 @@ class CRM_Contact_BAO_Query { } } 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) { @@ -2985,7 +2976,7 @@ class CRM_Contact_BAO_Query { $smartGroupIDs[] = $id; } else { - $regularGroupIDs[] = $id; + $regularGroupIDs[] = trim($id); } } @@ -3024,7 +3015,13 @@ class CRM_Contact_BAO_Query { 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"); @@ -3048,12 +3045,14 @@ class CRM_Contact_BAO_Query { //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) { @@ -3079,6 +3078,10 @@ class CRM_Contact_BAO_Query { } } + public function getGroupCacheTableKeys() { + return $this->_groupKeys; + } + /** * Function translates selection of group type into a list of groups. * @param $value @@ -3186,12 +3189,13 @@ WHERE $smartGroupClause $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] @@ -3199,8 +3203,8 @@ WHERE $smartGroupClause 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 @@ -3209,8 +3213,8 @@ WHERE $smartGroupClause 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); @@ -3223,12 +3227,12 @@ WHERE $smartGroupClause 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 ) "; @@ -3256,20 +3260,28 @@ WHERE $smartGroupClause 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); @@ -3280,7 +3292,7 @@ WHERE $smartGroupClause 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} ) @@ -3900,7 +3912,21 @@ WHERE $smartGroupClause $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, + ]); } /** @@ -4865,97 +4891,7 @@ civicrm_relationship.start_date > {$today} $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; @@ -4966,9 +4902,8 @@ civicrm_relationship.start_date > {$today} $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(); @@ -4981,6 +4916,22 @@ civicrm_relationship.start_date > {$today} 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 * @@ -6298,10 +6249,10 @@ AND displayRelType.is_active = 1 * 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) { @@ -6322,39 +6273,31 @@ AND displayRelType.is_active = 1 $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]; @@ -6391,24 +6334,32 @@ AND displayRelType.is_active = 1 $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. @@ -6489,18 +6440,26 @@ AND displayRelType.is_active = 1 * @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; } @@ -6647,4 +6606,275 @@ AND displayRelType.is_active = 1 } } + /** + * 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; + } + }