MATCH AGAINST for Doctrine DQL queries

AddFulltextIndexesCommand.php

<?php
# xxx/yyyBundle/Command/AddFulltextIndexesCommand.php
/**
 * AddFulltextIndexesCommand.php
 *
 * @author Jérémy Hubert <[email protected]>
 * @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 <[email protected]>
 * 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;
    }
} 

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