Как реализовать полнотекстовый поиск (MySql) с Doctrine и Symfony 3

Содержание

1 Класс MatchAgainst
2 Зарегистрируйте функцию в config.yml
3 Добавьте полнотекстовые индексы к полям таблицы
4 Создание запросов

Если ваш запрос where like %my search% не соответствует тому, что вы хотите в ваших результатах ваших запросов, то вы ищете в нужном месте.

В базах данных индексы обычно используются для повышения производительности при поиске чего-то определенного в предложении where. Однако, когда дело доходит до фильтрации некоторого текста, например, используя что-то вроде whereTextColumn LIKE '%searchstring%'(это то, что мы можем легко сделать с доктриной), тогда поиск медленен и не подходит для более гибких условий поиска, потому что способ работы индексов обычной базы данных оптимизирован для совпадений со «всем содержимым» столбца, а не только с частью этого В частности, поиск LIKE не может использовать какой-либо индекс.

Вы можете реализовать полнотекстовый поиск в MySQL, используя match() against() заявления. MATCH() принимает значение через запятую, которое указывает столбцы, в которых нужно найти ваше значение. AGAINST() принимает строку для поиска и необязательный модификатор, определяющий тип поиска (натуральный, логический и т. д.). Вам нужно будет добавить полнотекстовый индекс к вашему полю в базе данных.

Простое сопоставление с запросом в логическом режиме в MySQL:

SELECT * FROM myTable WHERE match(fieldName) against('I search this text' IN BOOLEAN MODE) LIMIT 10;

Чтобы использовать операторы сравнения и сравнения в доктрине 2 с MySQL, нам необходимо:

Создайте функцию MatchAgainst
Зарегистрируйте пользовательскую функцию в конфигурации Symfony (config.yml)
Добавьте индексы FULLTEXT в нужные вам поля базы данных
Выполните несколько запросов!

Примечание. Этот учебник будет работать для Symfony. < 2.x versions too.

Класс MatchAgainst

Создайте папку с именем Extensions в вашем комплекте (или корневой каталог /src), затем создайте папку внутри с именем Doctrine. Создайте внутри папки doctrine новый класс с именем MatchAgainst.php и установите следующий код внутри.

Не забудьте изменить пространство имен в соответствии с расположением внутри вашего проекта.

match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
do {
$this->columns[] = $parser->StateFieldPathExpression();
$parser->match(Lexer::T_COMMA);
} while ($parser->getLexer()->isNextToken(Lexer::T_IDENTIFIER));
$this->needle = $parser->InParameter();
while ($parser->getLexer()->isNextToken(Lexer::T_STRING)) {
$this->mode = $parser->Literal();
}
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
$haystack = null;
$first = true;
foreach ($this->columns as $column) {
$first ? $first = false : $haystack .= ', ';
$haystack .= $column->dispatch($sqlWalker);
}
$query = "MATCH(" . $haystack .
") AGAINST (" . $this->needle->dispatch($sqlWalker);
if ($this->mode) {
$query .= " " . $this->mode->dispatch($sqlWalker) . " )";
} else {
$query .= " )";
}
return $query;
}
}

Зарегистрируйте функцию в config.yml

Сопоставление — не единственная пользовательская функция, которую вы можете реализовать для доктрины, поэтому она должна быть легко настраиваемой. Просто зарегистрируйте MATCH_AGAINST свойство с путем к классу в свойстве dql ORM.

# Doctrine Configuration
doctrine:
# Search for the ORM property
orm:
# Those values should be already in your file and this doesn't matter
auto_generate_proxy_classes: "%kernel.debug%"
naming_strategy: doctrine.orm.naming_strategy.underscore
auto_mapping: true
# We need this the dql property to register the custom doctrine functions :
dql:
string_functions:
# Match agains should have the path to the MatchAgainst class created in the previous step
MATCH_AGAINST: myBundle\Extensions\Doctrine\MatchAgainst

Добавьте полнотекстовые индексы к полям таблицы

Чтобы добавить полнотекстовый индекс, вы не можете просто использовать интерфейс mysql для добавления индекса, потому что он не работает.

Но почему ? Если у вас есть только 1 поле с полнотекстовым индексом, то оно будет работать без проблем, и вам не нужно добавлять индексы с запросом.

Однако, если вам нужно выполнить запросы с более чем 1 полем, вы должны вручную добавить индексы с помощью запроса, например:

"-- fulltext_index is the alias that we'll give to the fulltext index"
ALTER TABLE yourTable ADD FULLTEXT fulltext_index(fieldName1, fieldName2, fieldName3)

Тогда вы сможете без проблем использовать функцию match_against в доктрине, иначе, если вы попытаетесь использовать ее без добавления индексов, вы получите вместо этого:

Не удается найти индекс FULLTEXT, соответствующий списку столбцов.

Вы также должны быть осторожны, когда связываете новое поле с уже существующим полнотекстовым индексом, потому что вам нужно зарегистрировать новый индекс с новым именем (это верно, другой индекс).

"-- Note the newfulltext_index that we're adding "
ALTER TABLE yourTable ADD FULLTEXT newfulltext_index(fieldName4, fieldName5)

Если вы попытаетесь выполнить запрос, используя сопоставление с fieldName4 или fieldName5, вы получите еще раз. Не удается найти индекс FULLTEXT, соответствующий списку столбцов.

Все полнотекстовые поля должны быть связаны в одной таблице с 1 полнотекстовым индексом, поэтому удалите предыдущий полнотекстовый индекс и добавьте новый со старыми и новыми полями.

"-- If you don't know the name of the registered indexes you can use the following line to see them"
SHOW INDEX FROM tableName
"-- Then drop the index using the name as parameter"
ALTER TABLE table DROP INDEX fulltext_index
"-- And finally add the new index with all the fields"
ALTER TABLE yourTable ADD FULLTEXT fulltext_index(fieldName1,fieldName2,fieldName3,fieldName4, fieldName5)

Создание запросов

Пример полнотекстового поиска в естественном режиме:

$result = $yourRepository->createQueryBuilder('p')
->addSelect("MATCH_AGAINST (p.fieldName1, p.fieldName2, p.fieldName3, :searchterm 'IN NATURAL MODE') as score")
->add('where', 'MATCH_AGAINST(p.fieldName1, p.fieldName2, p.fieldName3, :searchterm) > 0.8')
->setParameter('searchterm', "Test word")
->orderBy('score', 'desc')
->getQuery()
->getResult();
// with a result structure like :
// [["score" => '0.3123',"0" => "The array with the information of the row (all fields)"]]

Предыдущий пример будет соответствовать всем строкам, содержащим «Тестовое слово», и записи будут отсортированы в порядке убывания в соответствии с оценкой (поскольку строки могут содержать только тест или только слово).

Пример полнотекстового поиска в логическом режиме:

$result = $yourRepository->createQueryBuilder('p')
->addSelect("MATCH_AGAINST (p.fieldName1, p.fieldName2, p.fieldName3, :searchterm 'IN BOOLEAN MODE') as score")
->add('where', 'MATCH_AGAINST(p.fieldName1, p.fieldName2, p.fieldName3, :searchterm) > 0.8')
->setParameter('searchterm', "+microsoft ~windows")
->orderBy('score', 'desc')
->getQuery()
->getResult();
// with a result structure like :
// [["score" => '1.423',"0" => "Only microsoft in this text with fulltext :) "]]

Предыдущий запрос найдет строки, которые содержат слово «Microsoft», но не «Windows».

В обоих режимах нет никаких изменений в выигрыше. Ваше решение об использовании должно основываться на том, нужны ли вам функции логического режима или нет, читайте больше о логический режим в полнотекстовом поиске здесь и больше о естественном режиме здесь.

Ты можешь найти больше пользовательских реализаций функций в DoctrineExtensionsRepository от Beberlei здесь и использовать классы из /master/src/Query/Mysql каталог, чтобы включить только те функции, которые вам нужны (например, Soundex, Ceil, Day и т. д.). Не забудьте включить их правильно, также оформить заказ на этот файл yml чтобы увидеть, как зарегистрировать пользовательские функции.