knp paginator return 0 result but query return 5 results

Question

i try to do a pagination with knp paginator.

i use it in other place in the same website, and he work fine.

But, now i try use it on other query, and i have a problem.

    $query = $src->getQuery();
    //die(print_r($query->getDQL()));
    //die(print_r($query->getParameters()));
    $paginator = $this->get('knp_paginator');
    $paginator = $paginator->paginate(
        $query,
        $this->get('request')->query->get('page', 1),
        15
    );

$src is a custom service for create my query. The die line "getDQL" return this DQL :

SELECT p FROM YOUProductBundle:Product p INNER JOIN p.boutique b INNER JOIN p.material m INNER JOIN p.cat1 cat WHERE p.stock > 0 AND p.type = :type AND cat.id = :cat AND b.latitude >= :latN AND b.latitude <= :latP AND b.longitude >= :lonN AND b.longitude <= :lonP AND distance(b.latitude,b.longitude,48.583148,7.747882000000004) <= :rayon ORDER BY m.prix ASC

The die line "getParameters" return this datas :

Doctrine\Common\Collections\ArrayCollection Object
(
[_elements:Doctrine\Common\Collections\ArrayCollection:private] => Array
    (
        [0] => Doctrine\ORM\Query\Parameter Object
            (
                [name:Doctrine\ORM\Query\Parameter:private] => type
                [value:Doctrine\ORM\Query\Parameter:private] => material
                [type:Doctrine\ORM\Query\Parameter:private] => 2
            )

        [1] => Doctrine\ORM\Query\Parameter Object
            (
                [name:Doctrine\ORM\Query\Parameter:private] => cat
                [value:Doctrine\ORM\Query\Parameter:private] => 21
                [type:Doctrine\ORM\Query\Parameter:private] => integer
            )

        [2] => Doctrine\ORM\Query\Parameter Object
            (
                [name:Doctrine\ORM\Query\Parameter:private] => latP
                [value:Doctrine\ORM\Query\Parameter:private] => 49.257639204439
                [type:Doctrine\ORM\Query\Parameter:private] => 2
            )

        [3] => Doctrine\ORM\Query\Parameter Object
            (
                [name:Doctrine\ORM\Query\Parameter:private] => latN
                [value:Doctrine\ORM\Query\Parameter:private] => 47.908656795561
                [type:Doctrine\ORM\Query\Parameter:private] => 2
            )

        [4] => Doctrine\ORM\Query\Parameter Object
            (
                [name:Doctrine\ORM\Query\Parameter:private] => lonP
                [value:Doctrine\ORM\Query\Parameter:private] => 8.7674709750602
                [type:Doctrine\ORM\Query\Parameter:private] => 2
            )

        [5] => Doctrine\ORM\Query\Parameter Object
            (
                [name:Doctrine\ORM\Query\Parameter:private] => lonN
                [value:Doctrine\ORM\Query\Parameter:private] => 6.7282930249398
                [type:Doctrine\ORM\Query\Parameter:private] => 2
            )

        [6] => Doctrine\ORM\Query\Parameter Object
            (
                [name:Doctrine\ORM\Query\Parameter:private] => rayon
                [value:Doctrine\ORM\Query\Parameter:private] => 75
                [type:Doctrine\ORM\Query\Parameter:private] => 2
            )

    )

)

I don't have any error but i have 0 result for this query. Now, if i try to do "$query->getArrayResult()", i get 5 results !

Why the paginator don't get the 5 normal results ?

Paul T. Paul T. · Accepted Answer · 2013-10-28T07:44:49

The order of my parameters is not good ... When i set parameter, i don't set it in good order, see :

            ->andWhere('b.latitude >= :latN')
            ->andWhere('b.latitude <= :latP')
            ->andWhere('b.longitude >= :lonN')
            ->andWhere('b.longitude <= :lonP')
            ->andWhere('distance(b.latitude,b.longitude,'.$datas['lat'].','.$datas['lon'].') <= :rayon')
            ->setParameter('latP',$latP)
            ->setParameter('latN',$latN)
            ->setParameter('lonP',$lonP)
            ->setParameter('lonN',$lonN)
            ->setParameter('rayon',$datas['search']);

I inverse "latP" with "latN", and "lonP" with "lonN".

It's not a problem when you use the query builder... but when you export your query for Paginator, the final query and parameter array is not in good order, he can't do a correct query ....

knp paginator return 0 result but query return 5 results

1 Answers