Sorting joined table with KnpPaginatorBundle

Question

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?

Answer 1

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>