Symfony2, Use QueryBuilder with KnpPaginatorBundle

Question

I'm using KnpPaginatorBundle and I'm using QueryBuilder in Repository to select data. I was obliged to put parameters sort and direction in variables $sort and $direction and pass them to the Query to be able to do and orderBy.

This code works but I'd like to show it to you and and have your opinions if it's a good way to do this with KnpPaginatorBundle or there is another better way provided by knpPaginatorBundle.

Can I pass those parameters directly to knp_paginator not in the queryBuilder ?

Action

public function listAction($page, Request $request)
{
    $em = $this->getDoctrine()->getManager();

    $paginator  = $this->get('knp_paginator');


    if($request->query->get('sort') and $request->query->get('direction')) {
        $sort = $request->query->get('sort') ;
        $direction = $request->query->get('direction');
    }
    else{
        $sort = 'id'; // set default sort
        $direction = 'asc';  // set default direction
    }   

    // pass those two variables to query
    $listTravels = $em->getRepository('ProjectTravelBundle:Travel')->getListTravelsFrontend($sort, $direction);

    $pagination = $paginator->paginate(
        $listTravels,
        $this->get('request')->query->get('page', $page)/*page number*/,
        10/*limit per page*/
    );

    return $this->render('ProjectFrontendBundle:Frontend:list.html.twig',array(
        'pagination' => $pagination
    ));
}

TravelRepository:

public function getListTravelsFrontend($sort, $direction) // parameters $sort, $direction
{

    $qb = $this->createQueryBuilder('t')
        ->leftJoin('t.image', 'i')
        ->addSelect('i')
        ->leftJoin('t.agence', 'a')
        ->addSelect('a')
        ->Where('t.enabled = 1')
        ->orderBy('t.'.$sort, $direction); // orderBy with those parameters

    return $qb->getQuery()->getResult();

}

Answer 1

@phpisuber01 is right, his answer helps me understand. Unfortunately, official KnpPaginator site does not give example for QueryBuilder but does mention /* query NOT result */ in https://github.com/KnpLabs/KnpPaginatorBundle.

Here is the example code for QueryBuilder:

First in Repository:

/*
 * GiftDataRepository.php (in Repository/... directory)
 * return all records in table GiftData.
 */
public function getLatestRecords()
{
    $qb = $this->createQueryBuilder('q')->select('q');
    // getQuery() returns query NOT result.
    return $qb->getQuery();
}

Second in Controller:

 /*
  * TabulaRepository.php (in Controller/... directory)
  * return pagination object within template.
  */
 public function giftsDataShowAction(Request $request, $format)
{
    $em             = $this->getDoctrine()->getManager();
    $giftsdataquery = $em->getRepository('AppBundle:GiftData')->getLatestRecords();

    $paginator = $this->get('knp_paginator');
    /* page number 1, limit per page 5 */
    $pagination = $paginator->paginate($giftsdataquery, $request->query->getInt('page', 1), 5);

    // parameters to template
    return $this->render('data/data_gifts.html.twig', array('pagination' => $pagination));
}

Third in View:

<div class="count">Total of: {{ pagination.getTotalItemCount }}</div>
<table class="table table-responsive table-striped">
    <thead>
    <tr>
        {# sorting of properties based on query components #}
        <th class="hidden-xs">{{ knp_pagination_sortable(pagination, 'Id', 'q.id') }}</th>
        <th {% if pagination.isSorted('q.circumstances') %} class="sorted"{% endif %}>{{ knp_pagination_sortable(pagination, 'Circumstances', 'q.circumstances') }}</th>
        <th class="hidden-xs">{{ knp_pagination_sortable(pagination, 'Date Of Report', 'q.dateOfReport') }}</th>
    </tr>
    </thead>
    <tbody>

    {% for row in pagination %}
        <tr {% if loop.index is odd %}class="color"{% endif %}>
            <td id="col" class="hidden-xs">{{ row.id }}</td>
            <td id="col">{{ row.circumstances }}</td>
            <td id="col" class="hidden-xs">{{ row.dateOfReport }}</td>
        </tr>
    {% endfor %}
    </tbody>
</table>

That is it.

Answer 2

knp_paginator, itself work on sort and direction keyword from url, you don't have to pass them. see doc - https://github.com/KnpLabs/KnpPaginatorBundle

$listTravels, it will return all results from database - what if you have too many records.

$pagination = $paginator->paginate()  

only fetch limited results, based on your limit.

You can directly use $paginator = $this->get('knp_paginator'); in repository class after passing $this->container as argument or any other method and can use

$paginator  = $this->container->get('knp_paginator');