Sorting a join table using KnpPaginatorBundle

Question

I installed the test to get to know Symfony2 and KnpPaginatorBundle better. I have a pet table that refers to the type of pet ( Dog , Cat , Etc). I can sort by id , name , but when I try to sort by animal type , I get an error:

The specified Query component does not have such a [animalkind] field superimposed by the symbol [a]

I tried different field names, but nothing works.

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; } } 

Essence: 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 (cut off)

 <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 someone give me an idea about the problem?

Answer 1

The first problem is the lack of reference to ORM relationships; basically in your DB YourPet cannot be tied to AnimalKind .

You can find Entity and Association documentation on the Symfony website .

From an entity's YourPet point, you probably want to use ManyToOne relationships.

The second problem, once you fix the problem above, is that you are not joining the AnimalKind object in your request.

The third problem is that you cannot sort the result of a query by an entity.

The first potential solution to the problem:

 // 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; 

The solution to the second problem:

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

The solution to the third problem:

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

Answer 2

To display animals without animals, you will have to use LEFT JOIN in your query

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