Symfony 3 Doctrine Order By Annotation doesn't work

Question

I have 3 Entities in Symfony. I am trying to fetch data, however I can't order the results

Island.php

namespace MyGameBundle\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * Island
 *
 *
 * @ORM\Table(name="islands")
 * @ORM\Entity(repositoryClass="MyGameBundle\Repository\IslandRepository")
 */
class Island
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var int
 *
 * @ORM\Column(name="x", type="integer")
 */
private $x;

/**
 * @var int
 *
 * @ORM\Column(name="y", type="integer")
 */
private $y;

/**
 * @var Player
 *
 * @ORM\ManyToOne(targetEntity="MyGameBundle\Entity\Player", inversedBy="islands")
 * @ORM\JoinColumn(name="player_id", nullable=false)
 */
private $player;

/**
 * @var IslandResource[]
 *
 * @ORM\OneToMany(targetEntity="MyGameBundle\Entity\IslandResource", mappedBy="island")
 */
private $resources;

/**
 * @var IslandBuilding[]
 *
 * @ORM\OneToMany(targetEntity="MyGameBundle\Entity\IslandBuilding", mappedBy="island")
 */
private $buildings;

/**
 * @var IslandTroop[]
 *
 * @ORM\OneToMany(targetEntity="MyGameBundle\Entity\IslandTroop", mappedBy="island")
 */
private $troops;

/**
 * @var TroopProcess[]
 *
 * @ORM\OneToMany(targetEntity="MyGameBundle\Entity\TroopProcess", mappedBy="island")
 */
private $process;


public function __construct()
{
    $this->resources = new ArrayCollection();
    $this->buildings = new ArrayCollection();
    $this->troops = new ArrayCollection();
    $this->process = new ArrayCollection();
}

/**
 * Get id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

/**
 * Set x
 *
 * @param integer $x
 *
 * @return Island
 */
public function setX($x)
{
    $this->x = $x;

    return $this;
}

/**
 * Get x
 *
 * @return int
 */
public function getX()
{
    return $this->x;
}

/**
 * Set y
 *
 * @param integer $y
 *
 * @return Island
 */
public function setY($y)
{
    $this->y = $y;

    return $this;
}

/**
 * Get y
 *
 * @return int
 */
public function getY()
{
    return $this->y;
}

/**
 * @return Player
 */
public function getPlayer()
{
    return $this->player;
}

/**
 * @param Player $player
 */
public function setPlayer(Player $player)
{
    $this->player = $player;
}

/**
 * @return IslandResource[]
 */
public function getResources()
{
    return $this->resources;
}

/**
 * @param IslandResource[] $resources
 */
public function setResources(array $resources)
{
    $this->resources = $resources;
}

/**
 * @return IslandBuilding[]
 */
public function getBuildings()
{
    return $this->buildings;
}

/**
 * @param IslandBuilding[] $buildings
 */
public function setBuildings(array $buildings)
{
    $this->buildings = $buildings;
}

/**
 * @return IslandTroop[]
 */
public function getTroops()
{
    return $this->troops;
}

/**
 * @param IslandTroop[] $troops
 */
public function setTroops(array $troops)
{
    $this->troops = $troops;
}

/**
 * @return TroopProcess[]
 */
public function getProcess()
{
    return $this->process;
}

/**
 * @param TroopProcess[] $process
 */
public function setProcess(array $process)
{
    $this->process = $process;
}
}

Building.php

<?php

namespace MyGameBundle\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * Building
 *
 * @ORM\Table(name="buildings")
 * @ORM\Entity(repositoryClass="MyGameBundle\Repository\BuildingRepository")
 */
class Building
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var BuildingCostResource[]
 *
 * @ORM\OneToMany(targetEntity="MyGameBundle\Entity\BuildingCostResource", mappedBy="building")
 */
private $costs;

/**
 * @var BuildingCostTime
 *
 * @ORM\OneToOne(targetEntity="MyGameBundle\Entity\BuildingCostTime", mappedBy="building")
 */
private $timeCost;

/**
 * @var IslandBuilding[]
 *
 * @ORM\OneToMany(targetEntity="MyGameBundle\Entity\IslandBuilding", mappedBy="building")
 * @ORM\OrderBy({"id" = "DESC"})
 */
private $islandBuildings;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=255, unique=true)
 */
private $name;

public function __construct()
{
    $this->costs = new ArrayCollection();
    $this->islandBuildings = new ArrayCollection();
}


/**
 * Get id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

/**
 * Set name
 *
 * @param string $name
 *
 * @return Building
 */
public function setName($name)
{
    $this->name = $name;

    return $this;
}

/**
 * Get name
 *
 * @return string
 */
public function getName()
{
    return $this->name;
}

/**
 * @return BuildingCostResource[]
 */
public function getCosts()
{
    return $this->costs;
}

/**
 * @param BuildingCostResource[] $costs
 */
public function setCosts(array $costs)
{
    $this->costs = $costs;
}

/**
 * @return BuildingCostTime
 */
public function getTimeCost()
{
    return $this->timeCost;
}

/**
 * @param BuildingCostTime $timeCost
 */
public function setTimeCost(BuildingCostTime $timeCost)
{
    $this->timeCost = $timeCost;
}

/**
 * @return IslandBuilding[]
 */
public function getIslandBuildings()
{
    return $this->islandBuildings;
}

/**
 * @param IslandBuilding[] $islandBuildings
 */
public function setIslandBuildings(array $islandBuildings)
{
    $this->islandBuildings = $islandBuildings;
}
}

IslandBuilding.php

<?php

namespace MyGameBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * IslandBuilding
 *
 * @ORM\Table(name="island_buildings")
 * @ORM\Entity(repositoryClass="MyGameBundle\Repository\IslandBuildingRepository")
*/
class IslandBuilding
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var int
 *
 * @ORM\Column(name="level", type="integer")
 */
private $level;

/**
 * @var Island
 *
 * @ORM\ManyToOne(targetEntity="MyGameBundle\Entity\Island", inversedBy="buildings")
 * @ORM\JoinColumn(name="island_id", nullable=false)
 */
private $island;

/**
 * @var Building
 *
 * @ORM\ManyToOne(targetEntity="MyGameBundle\Entity\Building", inversedBy="islandBuildings")
 * @ORM\JoinColumn(name="building_id", nullable=false)
 */
private $building;

/**
 * @var BuildingProcess
 *
 * @ORM\OneToOne(targetEntity="MyGameBundle\Entity\BuildingProcess", mappedBy="islandBuilding")
 */
private $process;


/**
 * Get id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

/**
 * Set level
 *
 * @param integer $level
 *
 * @return IslandBuilding
 */
public function setLevel($level)
{
    $this->level = $level;

    return $this;
}

/**
 * Get level
 *
 * @return int
 */
public function getLevel()
{
    return $this->level;
}

/**
 * @return Island
 */
public function getIsland()
{
    return $this->island;
}

/**
 * @param Island $island
 */
public function setIsland(Island $island)
{
    $this->island = $island;
}

/**
 * @return Building
 */
public function getBuilding()
{
    return $this->building;
}

/**
 * @param Building $building
 */
public function setBuilding(Building $building)
{
    $this->building = $building;
}

/**
 * @return BuildingProcess
 */
public function getProcess()
{
    return $this->process;
}

/**
 * @param BuildingProcess $process
 */
public function setProcess(BuildingProcess $process)
{
    $this->process = $process;
}
}

In my method the code is

$island = $this->getDoctrine()->getRepository(Island::class)->find($this->getIslandId());
return $this->render('buildings/index.html.twig', [
    'buildings' => $island->getBuildings()
]);

I want to get all building names sorted in descending order on island given the island id. This returns an unordered building names.

Answer 1

On your example the param buildings passed to twig contain IslandBuilding objects.

To get IslandBuilding objects you can add a function to your IslandBuilding repository MyGameBundle\Repository\IslandBuildingRepository.

You can use JOIN to get Buildings informations, and orderBy to sort Buildings by thier names :

public function findByIsland( $island ) {
    $qb = $this->createQueryBuilder( 'ib' )
               ->join( 'ib.building', 'b', 'WITH', 'ib.building = b.id' )
               ->where( 'ib.island = :island' )
               ->orderBy( 'b.name', 'ASC' )
               ->setParameter( 'island', $island );

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

And on your method call that function using :

$islandBuildings = $this->getDoctrine()->getRepository(IslandBuilding::class)->findByIsland($this->getIslandId());
return $this->render('buildings/index.html.twig', [
   'islandBuildings' => $islandBuildings
]);

Answer 2

You could create a function in the BuildingRepository called findByIsland which contains the following code:

public function findByIsland($island) {
    $qb = $this->createQueryBuilder('b');
    $qb->where('b.island = :island')
        ->orderBy('b.name', 'DESC')
        ->setParameter('island', $island);

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

Then in your code you could call the new repo method like this:

return $this->render('buildings/index.html.twig', [
     'buildings' => $this->getDoctrine()->getRepository(Building::class)->findByIsland($island)
]);