[FIXED] cakephp 3 paginator doesn't work

Issue

I'm curently using this query with Cakephp 3 for a little search engine

$query_tweet = $this->Tweet
    ->find()
    ->select([
        'Users.username',
        'Users.avatarprofil',
        'Tweet.contenu_tweet',
        'Tweet.created',
        'Tweet.nb_commentaire',
        'Tweet.nb_partage',
        'Tweet.nb_like',
    ])
    ->where([
        "MATCH(Tweet.contenu_tweet) AGAINST(:search)" 
    ])
    ->where(['private' => 0]) // on ne cherche que les tweets publics
    ->bind(':search', '$search')
    ->order(['Tweet.created' => 'DESC'])
    ->contain(['Users']);

This query works perfectly but i want to use the paginator like this

$this->set('resultat_tweet', $this->Paginator->paginate($query_tweet, ['limit' => 8]));

i get

Error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

If you are using SQL keywords as table column names, you can enable identifier quoting for your database connection in config/app.php.

SQL Query:

SELECT
    Users.username AS `Users__username`,
    Users.avatarprofil AS `Users__avatarprofil`,
    Tweet.contenu_tweet AS `Tweet__contenu_tweet`,
    Tweet.created AS `Tweet__created`,
    Tweet.nb_commentaire AS `Tweet__nb_commentaire`,
    Tweet.nb_partage AS `Tweet__nb_partage`,
    Tweet.nb_like AS `Tweet__nb_like` 
FROM
    tweet Tweet 
    LEFT JOIN
        users Users ON Users.username = (Tweet.user_id)
WHERE (
    MATCH(Tweet.contenu_tweet) AGAINST(:search) 
    AND private = :c0
)
ORDER BY
    Tweet.created DESC
LIMIT
    8 OFFSET 0

i tried this query in PHPmyadmin and it works, i have many tests to see if i get the search and i have it

i really dont know what's the problem , i 'm using the Paginator on others pages and it work

Solution

finnaly, i get what i want by creating a new route, i don't use anymore str_replace

final code

              <?= $this->Paginator->options([
'url' => ['-'.$search.'']

]);

       echo $this->Paginator->next('Next page'); ?>

and the new route

Router::connect('/search/index/-:string',['controller' => 'Search', 'action' => 'index']);

Thanks a lot to everyone who helped me , especially Mathew Foscarini

Answered By - christ57