KNP Paginator Bundle Query Issue

Question

I am using the KNP Paginator bundle and everything works fine until I try to sort on field with a one to many relationship association. I have a filed in the purchaseOrder table called "supplier_id" and I am trying to sort on that, but I keep getting a "There is no such field..." error. The "supplier_id" field just holds an ID number from "Supplier" table.

Here is my code.

        // Set the up the pagination statement...
        $em = $this->getDoctrine()->getManager();
        $dql   = "SELECT p FROM WIC\PurchaseOrderBundle\Entity\PurchaseOrder p WHERE p.account=:account_id ORDER BY p.id desc";
        $query = $em->createQuery($dql);
        $query->setParameters(array(
            'account_id' => $account->getId(),
        ));;

        $paginator  = $this->get('knp_paginator');
        $paginatorObject = $paginator->paginate(
            $query,
            $this->get('request')->query->get('page', 1),25
        );

So basically on the first load of the page it is grabbing all purchase orders.

I then set up this to be able to sort by supplier:

 <th>{{ knp_pagination_sortable(purchaseOrders, 'Supplier', 'p.supplier') }}</th>

When I click it to sort it gives me this error:

 There is no such field [supplier_id] in the given Query component, aliased by [p]

My question is how do I sort when the field I am sorting on is part of a one to many relationship. The ID is stored in a field called "supplier_id" but it is a OtoM relationship.

Thanks!

Derick F Derick F · Accepted Answer · 2014-01-28T12:05:42

long story short, you need to do a work around.

you basically need to have a normal query get the ids of all the objects sorted in the way you want. With those ids you then construct a new query where you just look for those ids and paginate that query.

KNP Paginator Bundle Query Issue

2 Answers