Sorting joined table with KnpPaginatorBundle

doctrine-orm, pagination, sorting, symfony

Solution

The first issue is the missing ORM relationship declaration; basically in your DB `YourPet` cannot be associated to an `AnimalKind`.

You can find documentation about Entity relationships and associations on the Symfony's website.

From `YourPet` entity's point of view, you probably want to use a ManyToOne relationship.

The second issue, once you fix the problem above, is that you are not joining the `AnimalKind` entity in your query.

The third issue is that you cannot order a query result by an entity.

First issue potential solution:

// In MyPet class
/**
 * @var \Xyz\TestBundle\Entity\AnimalKind
 * @ORM\OneToMany(targetEntity="AnimalKind", inversedBy="MyPets")
 */
private $AnimalKind;

// In AnimalKind class
/**
 * @var \Doctrine\Common\Collections\Collection
 * @ORM\ManyToOne(targetEntity="MyPet", inversedBy="AnimalKind")
 */
private $MyPets;

Second issue potential solution:

$query = $em->createQuery("
    SELECT a, k
    FROM XyzTestBundle:MyPet a
    JOIN a.animalKind k 
");

Third issue potential solution:

<th>{{ knp_pagination_sortable(pagination, 'kind', 'k.type') }}</th>

Problem

I set up a test to get more familiar with Symfony2 and KnpPaginatorBundle. I have a table with pets that references the pet animal type (`Dog`, `Cat`, Etc). I can sort by the `id`, `name`, but when I try to sort by animal `type` I get an error message stating: There is no such field [animalkind] in the given Query component, aliased by [a] I have tried various field names, but nothing seems to work. Entity: `MyPets.php` ``` <?php namespace Xyz\TestBundle\Entity; use Doctrine\ORM\Mapping as ORM; /** * MyPet */ class MyPet { /** * @var integer */ private $id; /** * @var string */ private $name; /** * Set id * * @param integer $id * @return MyPet */ public function setId($id) { $this->id = $id; return $this; } /** * Get id * * @return integer */ public function getId() { return $this->id; } /** * Set name * * @param string $name * @return MyPet */ public function setName($name) { $this->name = $name; return $this; } /** * Get name * * @return string */ public function getName() { return $this->name; } /** * @var \Xyz\TestBundle\Entity\AnimalKind */ private $AnimalKind; /** * Set AnimalKind * * @param \Xyz\TestBundle\Entity\AnimalKind $animalKind * @return MyPet */ public function setAnimalKind(\Xyz\TestBundle\Entity\AnimalKind $animalKind) { $this->AnimalKind = $animalKind; return $this; } /** * Get AnimalKind * * @return \Xyz\TestBundle\Entity\AnimalKind */ public function getAnimalKind() { return $this->AnimalKind; } } ``` Entity: `AnimalKind.php` ``` <?php namespace Xyz\TestBundle\Entity; use Doctrine\ORM\Mapping as ORM; /** * AnimalKind */ class AnimalKind { /** * @var integer */ private $id; /** * @var string */ private $type; /** * Get id * * @return integer */ public function getId() { return $this->id; } /** * Set type * * @param string $type * @return AnimalKind */ public function setType($type) { $this->type = $type; return $this; } /** * Get type * * @return string */ public function getType() { return $this->type; } /** * @var \Doctrine\Common\Collections\Collection */ private $MyPets; /** * Constructor */ public function __construct() { $this->MyPets = new \Doctrine\Common\Collections\ArrayCollection(); } /** * Add MyPets * * @param \Xyz\TestBundle\Entity\MyPet $myPets * @return AnimalKind */ public function addMyPet(\Xyz\TestBundle\Entity\MyPet $myPets) { $this->MyPets[] = $myPets; return $this; } /** * Remove MyPets * * @param \Xyz\TestBundle\Entity\MyPet $myPets */ public function removeMyPet(\Xyz\TestBundle\Entity\MyPet $myPets) { $this->MyPets->removeElement($myPets); } /** * Get MyPets * * @return \Doctrine\Common\Collections\Collection */ public function getMyPets() { return $this->MyPets; } public function __toString() { return $this->getType(); } } ``` Controller: `MyPetController.php` (`IndexAction`) ``` public function indexAction() { $em = $this->getDoctrine()->getManager(); $query = $em->createQuery("SELECT a FROM XyzTestBundle:MyPet a"); $paginator = $this->get('knp_paginator'); $pagination = $paginator->paginate($query,$this->get('request')->query->get('page', 1)/*page number*/,15/*limit per page*/); return $this->render('XyzTestBundle:MyPet:index.html.twig', array( 'pagination' => $pagination, )); } ``` View: `MyPet/index.html.twig` (snipped) ``` <table class="records_list"> <thead> <tr> {# sorting of properties based on query components #} <th>{{ knp_pagination_sortable(pagination, 'Id', 'a.id') }}</th> <th>{{ knp_pagination_sortable(pagination, 'Name', 'a.name') }}</th> <th>{{ knp_pagination_sortable(pagination, 'kind', 'a.animalkind') }}</th> </tr> </thead> <tbody> {% for mypet in pagination %} <tr> <td><a href="{{ path('xyz_mypet_show', { 'id': mypet.id }) }}">{{ mypet.id }}</a></td> <td>{{ mypet.name }}</td> <td>{{ mypet.animalkind }}</td> </tr> {% endfor %} </tbody> </table> ``` Can anyone give me any insight as to the problem?

Original source