protected function SearchApiDbService::createKeysQuery in Search API Database Search 7
Creates a SELECT query for given search keys.
Used as a helper method in createDbQuery() and createFilterCondition().
Parameters
$keys: The search keys, formatted like the return value of SearchApiQueryInterface::getKeys(), but preprocessed according to internal requirements.
array $fields: The fulltext fields on which to search, with their names as keys mapped to internal information about them.
array $all_fields: Internal information about all indexed fields on the index.
Return value
SelectQueryInterface A SELECT query returning item_id and score (or only item_id, if $keys['#negation'] is set).
2 calls to SearchApiDbService::createKeysQuery()
- SearchApiDbService::createDbQuery in ./
service.inc - Creates a database query for a search.
- SearchApiDbService::createFilterCondition in ./
service.inc - Creates a database query condition for a given search filter.
File
- ./
service.inc, line 1497 - Contains SearchApiDbService.
Class
- SearchApiDbService
- Indexes and searches items using the database.
Code
protected function createKeysQuery($keys, array $fields, array $all_fields) {
if (!is_array($keys)) {
$keys = array(
'#conjunction' => 'AND',
$keys,
);
}
$neg = !empty($keys['#negation']);
$conj = $keys['#conjunction'];
$words = array();
$nested = array();
$negated = array();
$db_query = NULL;
$word_hits = array();
$neg_nested = $neg && $conj == 'AND';
foreach ($keys as $i => $key) {
if (!element_child($i)) {
continue;
}
if (is_scalar($key)) {
$words[] = $key;
}
elseif (empty($key['#negation'])) {
if ($neg) {
// If this query is negated, we also only need item_ids from
// subqueries.
$key['#negation'] = TRUE;
}
$nested[] = $key;
}
else {
$negated[] = $key;
}
}
$subs = count($words) + count($nested);
$mul_words = count($words) > 1;
$not_nested = $subs <= 1 && !$mul_words || $neg && $conj == 'OR' && !$negated;
if ($words) {
// All text fields in the index share a table. Get name from the first.
$field = reset($fields);
$db_query = $this->connection
->select($field['table'], 't');
if ($neg_nested) {
$db_query
->fields('t', array(
'item_id',
'word',
));
}
elseif ($neg) {
$db_query
->fields('t', array(
'item_id',
));
}
elseif ($not_nested) {
$db_query
->fields('t', array(
'item_id',
));
$db_query
->addExpression('SUM(score)', 'score');
$db_query
->groupBy('t.item_id');
}
else {
$db_query
->fields('t', array(
'item_id',
'word',
));
$db_query
->addExpression('SUM(score)', 'score');
$db_query
->groupBy('t.item_id');
$db_query
->groupBy('t.word');
}
if (empty($this->options['partial_matches'])) {
$db_query
->condition('word', $words, 'IN');
}
else {
$db_or = db_or();
// GROUP BY all existing non-grouped, non-aggregated columns – except
// "word", which we remove since it will be useless to us in this case.
$columns =& $db_query
->getFields();
unset($columns['word']);
foreach ($columns as $column => $info) {
$db_query
->groupBy($info['table'] . '.' . $column);
}
foreach ($words as $i => $word) {
$like = '%' . $this->connection
->escapeLike($word) . '%';
$db_or
->condition('t.word', $like, 'LIKE');
// Add an expression for each keyword that shows whether the indexed
// word matches that particular keyword. That way we don't return a
// result multiple times if a single indexed word (partially) matches
// multiple keywords. We also remember the column name so we can
// afterwards verify that each word matched at least once.
$alias = 'w' . $i;
$alias = $db_query
->addExpression("CASE WHEN t.word LIKE :like_{$alias} THEN 1 ELSE 0 END", $alias, array(
":like_{$alias}" => $like,
));
$db_query
->groupBy($alias);
$word_hits[] = $alias;
}
$db_query
->condition($db_or);
}
$db_query
->condition('field_name', array_map(array(
__CLASS__,
'getTextFieldName',
), array_keys($fields)), 'IN');
}
if ($nested) {
$word = '';
foreach ($nested as $k) {
$query = $this
->createKeysQuery($k, $fields, $all_fields);
if (!$neg) {
$word .= ' ';
$var = ':word' . strlen($word);
$query
->addExpression($var, 'word', array(
$var => $word,
));
}
if (!isset($db_query)) {
$db_query = $query;
}
elseif ($not_nested) {
$db_query
->union($query, 'UNION');
}
else {
$db_query
->union($query, 'UNION ALL');
}
}
}
if (isset($db_query) && !$not_nested) {
$db_query = $this->connection
->select($db_query, 't');
$db_query
->addField('t', 'item_id', 'item_id');
if (!$neg) {
$db_query
->addExpression('SUM(t.score)', 'score');
$db_query
->groupBy('t.item_id');
}
if ($conj == 'AND' && $subs > 1) {
$var = ':subs' . (int) $subs;
if (!$db_query
->getGroupBy()) {
$db_query
->groupBy('t.item_id');
}
if ($word_hits) {
// Simply check whether each word matched at least once.
foreach ($word_hits as $column) {
$db_query
->having("SUM({$column}) >= 1");
}
}
elseif ($mul_words) {
$db_query
->having('COUNT(DISTINCT t.word) >= ' . $var, array(
$var => $subs,
));
}
else {
$db_query
->having('COUNT(t.word) >= ' . $var, array(
$var => $subs,
));
}
}
}
if ($negated) {
if (!isset($db_query) || $conj == 'OR') {
if (isset($db_query)) {
// We are in a rather bizarre case where the keys are something like
// "a OR (NOT b)".
$old_query = $db_query;
}
// We use this table because all items should be contained exactly once.
$db_query = $this->connection
->select($all_fields['search_api_language']['table'], 't');
$db_query
->addField('t', 'item_id', 'item_id');
if (!$neg) {
$db_query
->addExpression(':score', 'score', array(
':score' => self::SCORE_MULTIPLIER,
));
$db_query
->distinct();
}
}
if ($conj == 'AND') {
foreach ($negated as $k) {
$db_query
->condition('t.item_id', $this
->createKeysQuery($k, $fields, $all_fields), 'NOT IN');
}
}
else {
$or = db_or();
foreach ($negated as $k) {
$or
->condition('t.item_id', $this
->createKeysQuery($k, $fields, $all_fields), 'NOT IN');
}
if (isset($old_query)) {
$or
->condition('t.item_id', $old_query, 'NOT IN');
}
$db_query
->condition($or);
}
}
if ($neg_nested) {
$db_query = $this->connection
->select($db_query, 't')
->fields('t', array(
'item_id',
));
}
return $db_query;
}