From 7296606d4db30663d75f90b4b592cda88b1af968 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Fri, 30 May 2014 16:47:40 -0700 Subject: [PATCH] CRM-14765 - FullText - Retain limits as numbers so that they can be used with non-SQL searches. Only convert to SQL as-needed. --- CRM/Contact/Form/Search/Custom/FullText.php | 40 ++++++++++++++++--- .../Custom/FullText/AbstractPartialQuery.php | 31 ++++++++++++-- .../Form/Search/Custom/FullText/Activity.php | 2 +- .../Form/Search/Custom/FullText/Case.php | 2 +- .../Form/Search/Custom/FullText/Contact.php | 2 +- .../Search/Custom/FullText/Contribution.php | 2 +- .../Search/Custom/FullText/Membership.php | 2 +- .../Search/Custom/FullText/Participant.php | 2 +- 8 files changed, 67 insertions(+), 16 deletions(-) diff --git a/CRM/Contact/Form/Search/Custom/FullText.php b/CRM/Contact/Form/Search/Custom/FullText.php index 4631aa7c85..9135b82831 100644 --- a/CRM/Contact/Form/Search/Custom/FullText.php +++ b/CRM/Contact/Form/Search/Custom/FullText.php @@ -55,10 +55,19 @@ class CRM_Contact_Form_Search_Custom_FullText implements CRM_Contact_Form_Search protected $_tableFields = NULL; + /** + * @var array|null NULL if no limit; or array(0 => $limit, 1 => $offset) + */ protected $_limitClause = NULL; + /** + * @var array|null NULL if no limit; or array(0 => $limit, 1 => $offset) + */ protected $_limitRowClause = NULL; + /** + * @var array|null NULL if no limit; or array(0 => $limit, 1 => $offset) + */ protected $_limitDetailClause = NULL; protected $_limitNumber = 10; @@ -86,8 +95,8 @@ class CRM_Contact_Form_Search_Custom_FullText implements CRM_Contact_Form_Search $this->_text = $formValues['text']; if (!$this->_table) { - $this->_limitClause = " LIMIT {$this->_limitNumberPlus1}"; - $this->_limitRowClause = $this->_limitDetailClause = " LIMIT {$this->_limitNumber}"; + $this->_limitClause = array($this->_limitNumberPlus1, NULL); + $this->_limitRowClause = $this->_limitDetailClause = array($this->_limitNumber, NULL); } else { // when there is table specified, we would like to use the pager. But since @@ -98,8 +107,8 @@ class CRM_Contact_Form_Search_Custom_FullText implements CRM_Contact_Form_Search $pageId = CRM_Utils_Array::value('crmPID', $_REQUEST, 1); $offset = ($pageId - 1) * $rowCount; $this->_limitClause = NULL; - $this->_limitRowClause = " LIMIT $rowCount"; - $this->_limitDetailClause = " LIMIT $offset, $rowCount"; + $this->_limitRowClause = array($rowCount, NULL); + $this->_limitDetailClause = array($rowCount, $offset); } $this->_formValues = $formValues; @@ -351,7 +360,7 @@ WHERE t.table_name = 'Activity' AND // now iterate through the table and add entries to the relevant section $sql = "SELECT * FROM {$this->_tableName}"; if ($this->_table) { - $sql .= " {$this->_limitRowClause} "; + $sql .= " {$this->toLimit($this->_limitRowClause)} "; } $dao = CRM_Core_DAO::executeQuery($sql); @@ -450,7 +459,7 @@ WHERE t.table_name = 'Activity' AND $sql = " SELECT $select FROM {$this->_tableName} contact_a - {$this->_limitRowClause} + {$this->toLimit($this->_limitRowClause)} "; return $sql; } @@ -499,4 +508,23 @@ FROM {$this->_tableName} contact_a CRM_Utils_System::setTitle($title); } } + + /** + * @param int|array $limit + * @return string SQL + * @see CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery::toLimit + */ + public function toLimit($limit) { + if (is_array($limit)) { + list ($limit, $offset) = $limit; + } + if (empty($limit)) { + return ''; + } + $result = "LIMIT {$limit}"; + if ($offset) { + $result .= " OFFSET {$offset}"; + } + return $result; + } } diff --git a/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php index 2a81bf9e16..df216da965 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php +++ b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php @@ -67,11 +67,15 @@ abstract class CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery { * TODO: Consider removing $entityIDTableName from the function-signature. Each implementation could be * responsible for its own temp tables. * + * TODO: Understand why $queryLimit and $detailLimit are different + * * @param string $queryText a string of text to search for * @param string $entityIDTableName a temporary table into which we can write a list of all matching IDs * @param string $detailTable a table into which we can write details about a page worth of matches - * @param string $queryLimit overall limit (applied when building $entityIDTableName) - * @param string $detailLimit final limit (applied when building $detailTable) + * @param array|NULL $queryLimit overall limit (applied when building $entityIDTableName) + * NULL if no limit; or array(0 => $limit, 1 => $offset) + * @param array|NULL $detailLimit final limit (applied when building $detailTable) + * NULL if no limit; or array(0 => $limit, 1 => $offset) * @return int number of matches */ public abstract function fillTempTable($queryText, $entityIDTableName, $detailTable, $queryLimit, $detailLimit); @@ -131,7 +135,7 @@ AND cf.html_type IN ( 'Text', 'TextArea', 'RichTextEditor' ) $sql = " REPLACE INTO {$entityIDTableName} ( entity_id ) $sqlStatement -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } @@ -170,7 +174,7 @@ FROM $tableName WHERE ( $whereClause ) AND {$tableValues['id']} IS NOT NULL GROUP BY {$tableValues['id']} -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } @@ -212,4 +216,23 @@ GROUP BY {$tableValues['id']} } } + /** + * @param int|array $limit + * @return string SQL + * @see CRM_Contact_Form_Search_Custom_FullText::toLimit + */ + public function toLimit($limit) { + if (is_array($limit)) { + list ($limit, $offset) = $limit; + } + if (empty($limit)) { + return ''; + } + $result = "LIMIT {$limit}"; + if ($offset) { + $result .= " OFFSET {$offset}"; + } + return $result; + } + } \ No newline at end of file diff --git a/CRM/Contact/Form/Search/Custom/FullText/Activity.php b/CRM/Contact/Form/Search/Custom/FullText/Activity.php index 86ea35455f..0e2d90ec50 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Activity.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Activity.php @@ -123,7 +123,7 @@ LEFT JOIN civicrm_case_activity cca ON cca.activity_id = ca.id LEFT JOIN civicrm_case_contact ccc ON ccc.case_id = cca.case_id WHERE (ca.is_deleted = 0 OR ca.is_deleted IS NULL) GROUP BY ca.id -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } diff --git a/CRM/Contact/Form/Search/Custom/FullText/Case.php b/CRM/Contact/Form/Search/Custom/FullText/Case.php index 475593f409..a99ac6cb66 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Case.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Case.php @@ -106,7 +106,7 @@ FROM {$fromTable} ct INNER JOIN civicrm_case cc ON cc.id = ct.entity_id LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } diff --git a/CRM/Contact/Form/Search/Custom/FullText/Contact.php b/CRM/Contact/Form/Search/Custom/FullText/Contact.php index 176486ba21..6839a596b1 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Contact.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Contact.php @@ -125,7 +125,7 @@ INSERT INTO {$toTable} SELECT c.id, ct.entity_id, c.sort_name, c.display_name, 'Contact' FROM {$fromTable} ct INNER JOIN civicrm_contact c ON ct.entity_id = c.id -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } diff --git a/CRM/Contact/Form/Search/Custom/FullText/Contribution.php b/CRM/Contact/Form/Search/Custom/FullText/Contribution.php index 7233e0928a..219ea65a85 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Contribution.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Contribution.php @@ -111,7 +111,7 @@ LEFT JOIN civicrm_contribution_page ccp ON ccp.id = cc.contribution_page_id LEFT JOIN civicrm_option_group option_group_contributionStatus ON option_group_contributionStatus.name = 'contribution_status' LEFT JOIN civicrm_option_value contribution_status ON ( contribution_status.option_group_id = option_group_contributionStatus.id AND contribution_status.value = cc.contribution_status_id ) -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } diff --git a/CRM/Contact/Form/Search/Custom/FullText/Membership.php b/CRM/Contact/Form/Search/Custom/FullText/Membership.php index 4d289e39e2..1cebe0a93f 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Membership.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Membership.php @@ -93,7 +93,7 @@ LEFT JOIN civicrm_membership_type cmt ON cmt.id = cm.membership_type_id LEFT JOIN civicrm_membership_payment cmp ON cmp.membership_id = cm.id LEFT JOIN civicrm_contribution cc ON cc.id = cmp.contribution_id LEFT JOIN civicrm_membership_status cms ON cms.id = cm.status_id -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } diff --git a/CRM/Contact/Form/Search/Custom/FullText/Participant.php b/CRM/Contact/Form/Search/Custom/FullText/Participant.php index 2ed343bf62..b1b623ae8b 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Participant.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Participant.php @@ -104,7 +104,7 @@ INNER JOIN civicrm_participant cp ON cp.id = ct.entity_id LEFT JOIN civicrm_contact c ON cp.contact_id = c.id LEFT JOIN civicrm_event ce ON ce.id = cp.event_id LEFT JOIN civicrm_participant_status_type participantStatus ON participantStatus.id = cp.status_id -{$limit} +{$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); } -- 2.25.1