/** * Create a SQL expression for matching against a list of * text columns. * * @param string $table eg "civicrm_note" or "civicrm_note mynote" * @param array|string $fullTextFields list of field names * @param string $queryText * @return string SQL, eg "MATCH (col1) AGAINST (queryText)" or "col1 LIKE '%queryText%'" */ public function matchText($table, $fullTextFields, $queryText) { $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; if (strpos($table, ' ') === FALSE) { $tableName = $tableAlias = $table; } else { list($tableName, $tableAlias) = explode(' ', $table); } if (is_scalar($fullTextFields)) { $fullTextFields = array($fullTextFields); } $clauses = array(); if (CRM_Core_InnoDBIndexer::singleton()->hasDeclaredIndex($tableName, $fullTextFields)) { $formattedQuery = CRM_Utils_QueryFormatter::singleton()->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL); $prefixedFieldNames = array(); foreach ($fullTextFields as $fieldName) { $prefixedFieldNames[] = "{$tableAlias}.{$fieldName}"; } $clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)", implode(',', $prefixedFieldNames), $strtolower(CRM_Core_DAO::escapeString($formattedQuery))); } else { //CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array( // 1 => $table, // 2 => implode(', ', $fullTextFields), //))); $formattedQuery = CRM_Utils_QueryFormatter::singleton()->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_LIKE); $escapedText = $strtolower(CRM_Core_DAO::escapeString($formattedQuery)); foreach ($fullTextFields as $fieldName) { $clauses[] = "{$tableAlias}.{$fieldName} LIKE '{$escapedText}'"; } } return implode(' OR ', $clauses); }
/** * Test format. * * @param string $text * @param string $language * @param string $mode * @param string $expectedText * * @dataProvider dataProvider */ public function testFormat($text, $language, $mode, $expectedText) { $formatter = new CRM_Utils_QueryFormatter($mode); $actualText = $formatter->format($text, $language); $this->assertEquals($expectedText, $actualText); }
/** * @param $text * * Ex: drush eval 'civicrm_initialize(); CRM_Utils_QueryFormatter::dumpExampleTable("firstword secondword");' */ public static function dumpExampleTable($text) { $width = strlen($text) + 8; $buf = ''; $buf .= sprintf("%-{$width}s", 'mode'); foreach (self::getLanguages() as $lang) { $buf .= sprintf("%-{$width}s", $lang); } $buf .= "\n"; foreach (self::getModes() as $mode) { $formatter = new CRM_Utils_QueryFormatter($mode); $buf .= sprintf("%-{$width}s", $mode); foreach (self::getLanguages() as $lang) { $buf .= sprintf("%-{$width}s", $formatter->format($text, $lang)); } $buf .= "\n"; } echo $buf; }