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