MATCH AGAINST for Doctrine DQL queries

AddFulltextIndexesCommand.php

<?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;
    }
}

Address.php

<?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##';
        }
    }

AddressRepository.php

<?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();
    }

config.yml

# xxx/yyyBundle/Resources/config/config.yml
doctrine:
    orm:
        entity_managers:
            default:
                dql:
                    string_functions:
                        MATCH_AGAINST: xxx\yyyBundle\Extension\Doctrine\MatchAgainst

MatchAgainst.php

<?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), "'") . " )";

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!

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.

Hello, i'm trying to use this code on symfony 4, but i have this error
'Cannot autowire service "XXXBundle\Extensions\Doctrine\MatchAgainst": argument "$name" of method "__construct()" must have a type-hint or be given a value explicitly. '
Some one know how to fix it?

I'm sore. it is problem auto injects servises. Just need add to services.yml
exclude: '../../src/AppBundle/{Entity,Repository,Tests,Extensions}'

Take a look at the Yaml setup here Stackoverflow. The edited version should work. It seems like your Yaml tabs are not correct.

Sorry, but, after hours, unable to get a functional query... QueryException trigger an error : Expected known function, got 'MATCH_AGAINST'... Symfony 3 and just copy and paste the code above... Any idea ?

Sorry, but, after hours, unable to get a functional query... QueryException trigger an error : Expected known function, got 'MATCH_AGAINST'... Symfony 3 and just copy and paste the code above... Any idea ?

Most probably it is with the setup of the configuration.. Doctrine cant get function MATCH_AGAINST

trying to figure out what i did wrong:

        $subDivRes = $em->createQueryBuilder()
        ->select('o')
        ->from('App\Entity\Divisions','o')
        ->where("MATCH_AGAINST (o.name, :search) > 0.0")
        ->setParameter('search', $keyword)
        ->getQuery()
        ->getResult();

but what im getting is:

An exception occurred while executing 'SELECT d0_.ID AS ID_0, d0_.NAME AS NAME_1, d0_.NAMEAR AS NAMEAR_2, d0_.CODE AS CODE_3 FROM DIVISIONS d0_ WHERE MATCH(d0_.NAME) AGAINST (? ) > 0.0' with params ["test"]:

ORA-00920: invalid relational operator

For some reason the AGAINST is taking the parameter as '?' instead of the provided one

PS: using symfony 4

Hey @wbelhomsi,

Did you ever figure out how to have it working with on Symfony 4?

Or, just use the well-maintained beberlei/doctrineextensions library.

Usage:

WHERE MATCH (p.brand, p.name) AGAINST (:searchString) > 0

Installation with composer:

composer require beberlei/doctrineextensions

Setup with Symfony 4.3 (@darol100 😉):

doctrine:
    orm:
        dql:
            string_functions:
                MATCH: DoctrineExtensions\Query\Mysql\MatchAgainst