Symfony 2.1.4-knpPaginator is not sorting the data on click the <th> elements

Question

My controller is useing doctrine and calling the paginate method of knp pagination bundle, the code is follwing

$em = $this->get('doctrine.orm.entity_manager');

    $query = $em->createQueryBuilder()->add('select', 'a')
                ->add('from', 'Acme\TaskBundle\Entity\Product a')->getQuery()->getResult();
    $paginator = $this->get('knp_paginator');
    $pagination = $paginator->paginate(
                                        $query,
                                        $this->get('request')->query->get('page', 1)/*page number*/,
                                        5/*limit per page*/
                                      );
    return $this->render('AcmeTaskBundle:Default:index.html.twig', array(
    'pagination' => $pagination,
    ));

and view is simply represent the pagination data

<table>
<tr>
  {# sorting of properties based on query components #}
<th>{{ pagination.sortable('Id', 'a.id')|raw }}</th>
<th>{{ pagination.sortable('Name', 'a.name')|raw }}</th>
<th>{{ pagination.sortable('Price', 'a.price')|raw }}</th>
<th>{{ pagination.sortable('Description', 'a.description')|raw }}</th>
</tr>

{# table body #}
{% for Product in pagination %}
 <tr {% if loop.index is odd %}class="color"{% endif %}>
<td>{{ Product.id }}</td>
<td>{{ Product.name }}</td>
<td>{{ Product.price }}</td>
<td>{{ Product.description }}</td>    
 </tr>
 {% endfor %}
 </table>
 {# display navigation #}
<div class="navigation">
   {{ pagination.render()|raw }}
 </div>

and routing is

index_entries:
   pattern: /index/
   defaults: { _controller: AcmeTaskBundle:Default:index }

I am getting the all table data with pagination, but unable to sort data on click the elements. paginator config is

protected $defaultOptions = array(
    'pageParameterName' => 'page',
    'sortFieldParameterName' => 'sort',
    'sortDirectionParameterName' => 'direction',
    'distinct' => true
);

This shows Query Exception "Invalid parameter number: number of bound variables does not match number of tokens" — Ammar Hayder Khan
Hi @aligarian ! I have the same problem. Have you solved it ? Thanks. I also think as kibao that's because we don't pass the $query to paginate but the results. But like you, if I pass only the query to the pagination function I have the same error : 'Invalid parameter number:number of bound ...' — Reveclair

Ammar Hayder Khan Ammar Hayder Khan · Accepted Answer · 2013-01-29T07:16:19

I have got the answer. First,I get the the post data from paginator view like this

$postData = $request->query->all();

and then pass the data in query string like

$dql= 'select a from AcmeTaskBundle:Product a order by ORDER BY $postData[sort]    $postData[direction]';

Symfony 2.1.4-knpPaginator is not sorting the data on click the <th> elements

1 Answers