Error when searching (server side)

[Minishlink]

Hello,

do any of you get a "Invalid parameter number: number of bound variables does not match number of tokens" error when using the search field ? I'm in serverside mode. It happens for all my datatables, even the simpliest.

Thanks,
Minishlink

[stwe]

Is this still a problem?

[Minishlink]

Unfortunately yes.
I can use the datatable well if I only use the sort function or change the page length, or use the paginator.
But as soon as I use the search function once, any interaction (sort, page length, search etc..) throws me a 500 error "Invalid parameter number: number of bound variables does not match number of tokens"...

[stwe]

Do you have the option "searchable" or "orderable" always set to false? Then the bundle throws an error. Just try at least one field with "searchable" = true or/and "orderable" = true. Or try the default settings.

Please post your DataTables class.

[Minishlink]

Thanks for your help.
My settings are the default settings. Unfortunately, adding one field searchable or orderable still throws the error... I also tried forcing searching and ordering in the features with no success.

Here is my DataTables class:

namespace PJM\AppBundle\Datatables;

use Sg\DatatablesBundle\Datatable\View\AbstractDatatableView;
use PJM\AppBundle\Twig\IntranetExtension;

/**
 * Class HistoriqueDatatable
 *
 * @package PJM\AppBundle\Datatables
 */
class HistoriqueDatatable extends AbstractDatatableView
{
    /**
     * {@inheritdoc}
     */
    public function buildDatatableView()
    {
        $this->getFeatures()
            ->setServerSide(true)
            ->setProcessing(true);

        $this->getOptions()
            ->setOrder(array("column" => 0, "direction" => "desc"))
        ;

        $this->getAjax()->setUrl($this->getRouter()->generate('pjm_app_consos_historiqueResults'));

        $this->setStyle(self::BOOTSTRAP_3_STYLE);

        $this->getColumnBuilder()
            ->add('date', 'datetime', array(
                'title' => 'Date',
                'format' => 'll'
            ))
            ->add('nombre', 'column', array('title' => 'Nombre',))
            ->add('item.libelle', 'column', array('title' => 'Item',))
            ->add('item.prix', 'column', array('title' => 'Prix',))
            ->add('item.boquette.nom', 'column', array('title' => 'Boquette',))
        ;
    }

    /**
     * {@inheritdoc}
     */
    public function getLineFormatter()
    {
        $ext = new IntranetExtension();
        $formatter = function($line) use($ext) {
            $line["nombre"] = $ext->nombreFilter($line["nombre"]);
            $line["item"]["prix"] = $ext->prixFilter($line["nombre"]*$line["item"]["prix"]);
            return $line;
        };

        return $formatter;
    }

    /**
     * {@inheritdoc}
     */
    public function getEntity()
    {
        return 'PJM\AppBundle\Entity\Historique';
    }

    /**
     * {@inheritdoc}
     */
    public function getName()
    {
        return 'historique_datatable';
    }
}

[stwe]

I can not reproduce the issue. Where does the problem occur (filename and line number)?

Just try only the "date" field in your class.

[Minishlink]

It occurs
at Paginator ->getIterator () in vendor/sg/datatablesbundle/Sg/DatatablesBundle/Datatable/Data/DatatableData.php at line 407

The error is still thrown with only the "date" field in my class.

Here is the full stack trace :

[1] Doctrine\ORM\Query\QueryException: Invalid parameter number: number of bound variables does not match number of tokens
    at n/a
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\vendor\doctrine\orm\lib\Doctrine\ORM\Query\QueryException.php line 100

    at Doctrine\ORM\Query\QueryException::invalidParameterNumber()
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\vendor\doctrine\orm\lib\Doctrine\ORM\Query.php line 281

    at Doctrine\ORM\Query->_doExecute()
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\vendor\doctrine\orm\lib\Doctrine\ORM\AbstractQuery.php line 794

    at Doctrine\ORM\AbstractQuery->execute(null, '3')
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\vendor\doctrine\orm\lib\Doctrine\ORM\AbstractQuery.php line 598

    at Doctrine\ORM\AbstractQuery->getScalarResult()
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\vendor\doctrine\orm\lib\Doctrine\ORM\Tools\Pagination\Paginator.php line 151

    at Doctrine\ORM\Tools\Pagination\Paginator->getIterator()
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\vendor\sg\datatablesbundle\Sg\DatatablesBundle\Datatable\Data\DatatableData.php line 407

    at Sg\DatatablesBundle\Datatable\Data\DatatableData->getResponse()
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\src\PJM\AppBundle\Controller\ConsosController.php line 28

    at PJM\AppBundle\Controller\ConsosController->historiqueResultsAction()
        in  line 

    at call_user_func_array(array(object(ConsosController), 'historiqueResultsAction'), array())
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\app\bootstrap.php.cache line 3008

    at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\app\bootstrap.php.cache line 2970

    at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\app\bootstrap.php.cache line 3119

    at Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(object(Request), '1', true)
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\app\bootstrap.php.cache line 2364

    at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
        in C:\Users\Louis\Documents\Programmation\Sites\pjm-intranet\web\app_dev.php line 28

[stwe]

I can't find an issue. Maybe something is in your entity class not mapped correctly. Clear your cache.

[Minishlink]

I found what throws the error ! It is because I am setting parameters on the query builder, but the function setParameters clears the previous set parameters. That's a weird behaviour I didn't know of.

// HistoriqueRepository.php
public function callbackFindByUser($user)
{
    return function($qb) use($user) {
        $qb
            ->join('Historique.user', 'u', 'WITH', 'u = :user')
            ->setParameters(array(
                'user'  => $user,
            ))
        ;
    };
}

setParameters is to be replaced by setParameter

TL;DR : it is not a DataTables bug, but it may be good to notify devs in the doc about this ?