/**
* 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;
}
