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

This comment has been minimized.

Show comment Hide comment
@anthonytison

anthonytison Apr 19, 2013

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

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

@nealio82

This comment has been minimized.

Show comment Hide comment
@nealio82

nealio82 May 19, 2013

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/

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/

@fernandopg

This comment has been minimized.

Show comment Hide comment
@fernandopg

fernandopg Feb 6, 2014

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!

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!

@fernandopg

This comment has been minimized.

Show comment Hide comment
@fernandopg

fernandopg Feb 7, 2014

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...

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...

@makerlabs

This comment has been minimized.

Show comment Hide comment
@makerlabs

makerlabs Apr 13, 2014

To correct the issue with search mode change:

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

to:

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

To correct the issue with search mode change:

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

to:

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

This comment has been minimized.

Show comment Hide comment
@richsage

richsage 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")
// ...

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")
// ...
@jmauerhan

This comment has been minimized.

Show comment Hide comment
@jmauerhan

jmauerhan Feb 9, 2015

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

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

@gabrielgisbert

This comment has been minimized.

Show comment Hide comment
@gabrielgisbert

gabrielgisbert Feb 9, 2016

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!

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

This comment has been minimized.

Show comment Hide comment
@Gblue87

Gblue87 Jun 27, 2016

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.

Gblue87 commented Jun 27, 2016

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.

@viunyk

This comment has been minimized.

Show comment Hide comment
@viunyk

viunyk Feb 13, 2018

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?

viunyk commented Feb 13, 2018

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?

@viunyk

This comment has been minimized.

Show comment Hide comment
@viunyk

viunyk Feb 13, 2018

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

viunyk commented Feb 13, 2018

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

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