If your query where like %my search% does not satisfy what you want in your results of your queries, then you're looking in the right place.
In databases, indexes are usually used to enhance performance when looking for something defined in your where clause. However when it comes to filtering some text, for example using something like whereTextColumn LIKE '%searchstring%'(that's what we can do easily with doctrine) then searches are slow and doesn't fit for more flexible search terms, because the way regular database indexes work are optimized for matches against the 'whole content' of a column and not just a part of it. In specific the LIKE search can not make use of any kind of index.
You can implement fulltext searching in MySQL using match() against() statements. MATCH() takes a comma-separated value that indicates the columns where your value needs to be found. AGAINST() takes a string to search for and an optional modifier that determines type of search to perform (natural,boolean etc). You'll need to add a fulltext index to your field in the database.
A simple match against query in boolean mode in MySQL :
SELECT * FROM myTable WHERE match(fieldName) against('I search this text' IN BOOLEAN MODE) LIMIT 10;To use the match and against statements in doctrine 2 with MySQL, we'll need to :
- Create the MatchAgainst function
- Register the custom function in the symfony configuration (config.yml)
- Add the FULLTEXT indexes to the fields that you need to the database
- Execute some queries !
Note: This tutorial will works for Symfony < 2.x versions too.
MatchAgainst Class
Create a folder named Extensions in your bundle (or the root directory /src), then create a folder inside named Doctrine. Create inside the doctrine folder a new class named MatchAgainst.php and set the following code inside.
Don't forget to change the namespace according to the location inside your project.
<?php
// myBundle/Extensions/Doctrine/MatchAgainst.php
namespace myBundle\Extensions\Doctrine;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
/**
* "MATCH_AGAINST" "(" {StateFieldPathExpression ","}* InParameter {Literal}? ")"
*/
class MatchAgainst extends FunctionNode {
public $columns = array();
public $needle;
public $mode;
public function parse(\Doctrine\ORM\Query\Parser $parser) {
$parser->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;
}
}
Register the function in config.yml
Match against is not the only custom function that you can implement for doctrine, therefore it needs to be easily customizable. Just register the MATCH_AGAINST property with the class path in the dql property of the 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
Add the fulltext indexes to your table fields
To add a fulltext index, you can't use simply the mysql ui to add the index because it doesn't work.
But, why ? If you have only 1 field with fulltext index, then it will work without problem and you don't need to add the indexes with a query.
However if you need to execute queries with more than 1 field, you must add the indexes manually with a query using, for example :
"-- fulltext_index is the alias that we'll give to the fulltext index"
ALTER TABLE yourTable ADD FULLTEXT fulltext_index(fieldName1, fieldName2, fieldName3)
Then you'll be able to use the match_against function in doctrine without problems, otherwise if you try to use it without add the indexes you'll get instead :
Can't find FULLTEXT index matching the column list.
You need to be careful too when associating a new field to an already existent fulltext index, because you'll need to register a new index with a new name (that's right, another index).
"-- Note the newfulltext_index that we're adding "
ALTER TABLE yourTable ADD FULLTEXT newfulltext_index(fieldName4, fieldName5)
If you try to execute a query using match against with fieldName4 or fieldName5, you'll get once again Can't find FULLTEXT index matching the column list.
All the fulltext fields needs to be related in the same table to 1 fulltext index, therefore remove the previous fulltext index and add the new one with the old and new fields.
"-- 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)Creating queries
Natural mode fulltext search example :
$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)"]]The previous example will match all the rows that contains "Test word" and the records will be sorted decreasingly according to the score (as the rows can contain only test or only word).
Boolean mode fulltext search example :
$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 :) "]]The previous query will find rows that contain the word “microsoft” but not “windows”.
In both mode there isn't any change in scoring. Your decision on which to use should be based on whether or not you need the Boolean mode features, read more about boolean mode in fulltext search here and more about natural mode here.
You can find more custom functions implementations in DoctrineExtensionsRepository by Beberlei here and use the classes from /master/src/Query/Mysql directory in order to include only the functions that you need (like Soundex,Ceil,Day etc). Remember to enable them correctly, also checkout this yml file to see how to register the custom functions.
