We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Problem with QueryBuilder pagination for a full text search

Hi all, I'm trying to implement a full text search for my site, but I have some problem with the QueryBuilder.

That's my search function:

<?php  
    protected function search($language, $q = null, $params)  
    {  

        $builder = new QueryBuilder();

        $builder->columns([
                "id",
                "itemId",
                "title",
                "permalink" => sprintf("CONCAT('//%s/%s/',slug)", $_SERVER['HTTP_HOST'], $language, $language)
            ])
            ->from("App\Models\Ingested")
            ->where(
                "language=:language: AND MATCH(searchable) AGAINST (:q: IN BOOLEAN MODE)",
                [
                    "language" => $language,
                    "q"   => $q."*",
                ]
            );

        $paginator = new PQueryBuilder(
            [
                "builder" => $builder,
                "limit"   => $params['limit'],
                "page"    => $params['page'],
            ]
        );

        return $paginator->getPaginate();
    }  

This return me an error:

Syntax error, unexpected token IDENTIFIER(BOOLEAN), near to ' MODE) LIMIT :APL0:', when parsing: SELECT id, itemId, title, CONCAT('//www.weekendinitaly.lo/it/',slug) AS [permalink] FROM [App\Models\Wizardry] WHERE language=:language: AND MATCH(searchable) AGAINST (:q: IN BOOLEAN MODE) LIMIT :APL0: (201)
#0 [internal function]: Phalcon\Mvc\Model\Query->parse()
#1 [internal function]: Phalcon\Mvc\Model\Query->execute()
#2 /var/www/BraveNewSystem/app/api/controllers/WizardryController.php(78): Phalcon\Paginator\Adapter\QueryBuilder->getPaginate()
#3 /var/www/BraveNewSystem/app/api/controllers/WizardryController.php(35): App\Api\Controllers\WizardryController->search('it', 'firen', Array)

Maybe it's a bug, isn't it?

Hi @cosimo you must use Phalcon Incubator Mysql Dialect sometime some functions or operant are not included in PHQL Dialects then we can create our

Good luck

PHQL just don't know what is IN BOOLEAN. You need to extend dialect and add such a thing(as a function most likely because there is no other way atm).



12.1k

Ok, thank you all. I will try with Phalcon Incubator Mysql Dialect and if needed I'll add IN BOOLEAN