Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
MATCH AGAINST for Doctrine DQL queries
<?php
# xxx/yyyBundle/Command/AddFulltextIndexesCommand.php
/**
* AddFulltextIndexesCommand.php
*
* @author Jérémy Hubert <jeremy.hubert@infogroom.fr>
* @since lun. 26 sept. 2011 09:23:53
*/
namespace xxx\yyyBundle\Command;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\ConsoleInput\InputArgument;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Output\Output;
use Sensio\Bundle\GeneratorBundle\Command\Helper\DialogHelper;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
class AddFulltextIndexesCommand extends ContainerAwareCommand
{
public function configure()
{
$this->setName('project:build-fulltext');
$this->setDescription('Crée les champs fulltext pour rétablir la recherche');
}
public function execute(InputInterface $input, OutputInterface $output)
{
$connection = $this->getContainer()->get('doctrine')->getConnection();
$dialog = $this->getDialogHelper();
$dialog->writeSection($output, 'Création des liens pour la table Address');
$connection->query("ALTER TABLE `xxx`.`Address` ADD FULLTEXT `search_indexes` ( `name` , `street` , `postal` , `city` , `state` , `country` )");
$dialog->writeSection($output, 'Terminé.');
}
protected function getDialogHelper()
{
$dialog = $this->getHelperSet()->get('dialog');
if (!$dialog || get_class($dialog) !== 'Sensio\Bundle\GeneratorBundle\Command\Helper\DialogHelper') {
$this->getHelperSet()->set($dialog = new DialogHelper());
}
return $dialog;
}
}
<?php
# xxx/yyyBundle/Entity/Address.php
/**
* Get name
*
* @return string
*/
public function getName()
{
return str_replace('##FTS_FIX##', '', $this->name);
}
/**
* fixFts Fonction qui permet de changer systématiquement le nom de l'addresse,
* pour parer le problème de DOC ID
*
* @ORM\preUpdate
*/
public function fixFts()
{
if (substr($this->name, -11, 11) == '##FTS_FIX##') {
$this->name = substr($this->name, 0, -11);
} else {
$this->name = $this->name . '##FTS_FIX##';
}
}
<?php
# xxx/yyyBundle/Repository/AddressRepository.php
public function search($address) {
return $this->createQueryBuilder('p')
->addSelect("MATCH_AGAINST (p.name, p.country, p.street, p.postal, p.city, p.state, :address 'IN NATURAL MODE') as score")
->add('where', 'MATCH_AGAINST(p.name, p.country, p.street, p.postal, p.city, p.state, :address) > 0.8')
->setParameter('address', $address)
->orderBy('score', 'desc')
->setMaxResults(5)
->getQuery()
->getResult();
}
# xxx/yyyBundle/Resources/config/config.yml
doctrine:
orm:
entity_managers:
default:
dql:
string_functions:
MATCH_AGAINST: xxx\yyyBundle\Extension\Doctrine\MatchAgainst
<?php
# xxx/yyyBundle/Extension/Doctrine/MatchAgainst.php
/**
* MatchAgainst
*
* Definition for MATCH AGAINST MySQL instruction to be used in DQL Queries
*
* Usage: MATCH_AGAINST(column[, column, ...], :text ['SEARCH MODE'])
*
* @author Jérémy Hubert <jeremy.hubert@infogroom.fr>
* using work of http://groups.google.com/group/doctrine-user/browse_thread/thread/69d1f293e8000a27
*/
namespace xxx\yyyBundle\Extension\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;
}
}

Salut,

je tente depuis plus d'1h de mettre en place ce DQL et je suis confronté en permanence à cette erreur :
Error: Expected Literal, got ':title'

Aurais-tu une idée de la provenance de cette erreur ? Pour info, j'ai juste fait un copier/coller de ta requête en adaptant les champs et le paramètre.

Merci

Excellent script, thanks. Upgraded MySQL from 5.5 to 5.6 to make use of the new InnoDB fulltext searching with Doctrine 2 and got this working nicely.

References:
http://xlab.pl/en/full-text-searching/
http://www.peterchen.net/2013/02/20/en-how-to-install-mysql-5-6-on-ubuntu-12-04-precise/

Hi! Excellent script!
I have a problem when I tried to use wildcards and 'IN BOOLEAN MODE':

    $query = $this->createQueryBuilder('us')
            ->where("MATCH_AGAINST (us.fullName, :search 'IN BOOLEAN MODE') > 0.0")
            ->setParameter('search', 'mike*');

Never returns results... if I use the query in mysql it works:

SELECT * FROM fos_user WHERE MATCH (fullName) AGAINST ('mike*' IN BOOLEAN MODE); 

Thanks in advance!

I know why its not working but I dont know how to solve, its because Doctrine transform the query to this:

SELECT * FROM fos_user WHERE MATCH (fullName) AGAINST ('mike*' 'IN BOOLEAN MODE');

The problem is the quotes in 'IN BOOLEAN MODE' string...

To correct the issue with search mode change:

$query .= " " . $this->mode->dispatch($sqlWalker) . " )";

to:

$query .= " " . trim($this->mode->dispatch($sqlWalker), "'") . " )";

richsage commented Oct 9, 2014

We had to make a change as follows, to allow parameters, in the MatchAgainst class:

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_INPUT_PARAMETER));

    // Got an input parameter
    $this->needle = $parser->InputParameter();

    $parser->match(Lexer::T_CLOSE_PARENTHESIS);
}

This allows the following QueryBuilder code (inside an EntityRepository):

$qb = $this->createQueryBuilder("c")
    ->select("MATCH_AGAINST (c.firstName, c.lastName, c.username, :searchTerms) AS result")
// ...

Thanks @richsage I was having an issue with using a parameter and that fixed it.

Thanks!! It runs perfect!
In my case I was unable to run 'IN BOOLEAN MODE' option, so I have added it to getSql function like this:
$query .= " IN BOOLEAN MODE)";
and it runs ok!

Gblue87 commented Jun 27, 2016 edited

Hi i'm trying to use it like this way:

$qb = $this->getPublishWorkFlowQueryBuilder() ->leftJoin('c.translations', 't') ->andWhere('MATCH_AGAINST (t.title, t.description, :search)') ->setParameter('locale', $locale) ->setParameter('now', new \DateTime()) ->setParameter('search', $searchPhrase);
and got an error

SELECT c FROM ......\Product c LEFT JOIN c.publishWorkflow w LEFT JOIN c.translations t WHERE (w.isActive = 1 AND w.isHidden = :hidden AND ((w.fromDate IS NULL AND w.toDate IS NULL) OR (w.fromDate <= :now AND w.toDate >= :now) OR (w.fromDate IS NOT NULL AND w.fromDate <= :now) OR (w.toDate IS NOT NULL AND w.toDate >= :now))) AND MATCH_AGAINST (t.title, t.description, :search)
Because it is expect something after MATCH_AGAINST function

[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment