I have this request in SQL:
SELECT count(distinct b.id), count(distinct e.id) FROM building, ensembles e, `parcs` p WHERE p.id = "1" AND e.parcs_id = p.id AND e.id = b.ensembles_id
This how I use this request in my Symfony project, in a controller ManageController.php :
class ManageController extends Controller
{
public function indexAction()
{
$em=$this->getDoctrine()->getManager();
$parc = $em->getRepository('GirDatabaseBundle:Parcs');
$ensemble = $em->getRepository('GirDatabaseBundle:Ensembles');
$building = $em->getRepository('GirDatabaseBundle:Buildings');
$query = $em->createQuery(
'SELECT p.name, count(distinct b.id), count(distinct e.id)
FROM GirDatabaseBundle:Buildings b, GirDatabaseBundle:Ensembles e, GirDatabaseBundle:Parcs p
WHERE p.id in (:id)
AND e.parcs = p.id
AND e.id = b.ensembles
GROUP BY p.id')
->setParameter('id', '1');
$parc = $query->getResult();
return $this->render('GirManageParcsBundle:Parcs:manage.html.twig', array('parc' => $parc ));
}
But, when I lauched my html.twig page manage.html.twig, I have this error: Key "name" for array with keys "1, 2" does not exist in GirGestionPatrimoinesBundle:Parcs:manage.html.twig at line 35
So, here's the code for my twig:
{# CONTENT #}
<div class="panel-body">
<p class="text-justify">
<div class="jumbotron">
<h4><u>Situation actuelle</u></h4>
<p>
<h5>PARCS</h5>
{% for parcs in parc %}
<h4> {{ parcs.name}} </h4>
{% endfor %}
</p>
</div>
</p>
I think, I don't use the good way to display the data like I want. Do you know it works ? And I would like to display the name of my parc and his number of ensembles and buildings that belong to him.
This is the Parcs class/entity code: use Doctrine\ORM\Mapping as ORM;
/**
* Parcs
*
* @ORM\Table(name="parcs")
* @ORM\Entity
*/
class Parcs
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* @var string
*
* @ORM\Column(name="name", type="string", length=150, nullable=false)
*/
private $name;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set nom
*
* @param string $name
* @return Parcs
*/
public function setName($name)
{
$this->nom = $name;
return $this;
}
/**
* Get name
*
* @return string
*/
public function getName()
{
return $this->name;
}
This is the Ensembles class/entity code:
/**
* Ensembles
*
* @ORM\Table(name="ensembles")
* @ORM\Entity
*/
class Ensembles
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* @var string
*
* @ORM\Column(name="referenceClient", type="string", length=150, nullable=false)
*/
private $referenceclient;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set referenceclient
*
* @param string $referenceclient
* @return Ensembles
*/
public function setReferenceclient($referenceclient)
{
$this->referenceclient = $referenceclient;
return $this;
}
/**
* Get referenceclient
*
* @return string
*/
public function getReferenceclient()
{
return $this->referenceclient;
}
/**
* @ORM\ManyToOne(targetEntity="Gir\DatabaseBundle\Entity\Parcs")
* @ORM\JoinColumn(nullable=false)
*/
private $parcs;
/**
* Set parcs
*
* @param \Gir\DatabaseBundle\Entity\Parcs $parcs
* @return Ensembles
*/
public function setParcs(\Gir\DatabaseBundle\Entity\Parcs $parcs)
{
$this->parcs = $parcs;
return $this;
}
/**
* Get parcs
*
* @return \Gir\DatabaseBundle\Entity\Parcs
*/
public function getParcs()
{
return $this->parcs
And this the last one, the buildings class/entity code:
class Batiments
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* @var string
*
* @ORM\Column(name="referenceBatiment", type="string", length=150, nullable=false)
*/
private $referencebatiment;
/**
* @ORM\ManyToOne(targetEntity="Gir\DatabaseBundle\Entity\Ensembles")
* @ORM\JoinColumn(nullable=false)
*/
private $ensembles;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set referencebatiment
*
* @param string $referencebatiment
* @return Batiments
*/
public function setReferencebatiment($referencebatiment)
{
$this->referencebatiment = $referencebatiment;
return $this;
}
/**
* Get referencebatiment
*
* @return string
*/
public function getReferencebatiment()
{
return $this->referencebatiment;
}
/**
* Set ensembles
*
* @param \Gir\DatabaseBundle\Entity\Ensembles $ensembles
* @return Batiments
*/
public function setEnsembles(\Gir\DatabaseBundle\Entity\Ensembles $ensembles)
{
$this->ensembles = $ensembles;
return $this;
}
/**
* Get ensembles
*
* @return \Gir\DatabaseBundle\Entity\Ensembles
*/
public function getEnsembles()
{
return $this->ensembles;
}
To answer to riska's comment:
For {{ dump(parc) }}, it returns me:
array (size=1)
0 =>
array (size=2)
'bcnt' => string '307' (length=3)
'ecnt' => string '104' (length=3)
and for {{ dump(parcs) }}, I have:
array (size=2)
'bcnt' => string '307' (length=3)
'ecnt' => string '104' (length=3)
I think you forgot to alias the columns:
SELECT count(distinct b.id) AS bcnt, count(distinct e.id) AS ecnt
...
Now, you should be able to access to these variables:
{% for parcs in parc %}
<h4> {{ parcs.bcnt }} </h4>
<h4> {{ parcs.ecnt }} </h4>
{% endfor %}
To avoid this error, be sure the variable you use in Twig are defined and not null :
{# CONTENT #}
<div class="panel-body">
<p class="text-justify">
<div class="jumbotron">
<h4><u>Situation actuelle</u></h4>
<p>
<h5>PARCS</h5>
{% for parc in parcs %}
{% if parc.name is defined and parc.name is not null %}
<h4> {{ parcs.name}} </h4>
{% else %}
<h4> Parc without name</h4>
{% endif %}
{% endfor %}
</p>
</div>
</p>
BTW, you have a typo in your for loop, look at the code above.