I want to display names and count its related values in multiple related join. I want to loop the result like this
$datas = array();//initialise array
foreach ($island as $is) {
$datas[] = array($is->getName(),$is->getNums() );
}
where $is->getNums() is the retrieved total count of voters for each island name
so far I tried this
$island=$this->em->createQueryBuilder();
$island->select('count(v.id) as getNums')
->from('DuterteBundle:Voters','v')
->join('v.city','c')
->join('c.province','p')
->join('p.region','r')
->join('r.island','i')
$count = $island->getQuery()->getSingleScalarResult();
return $count;
voters is related to city, city is related to province, province is related to region, region is related to island.I want to display each island's name as well count and display all voters in each island's name
My table structures looks like this
id name city_id //voters table
id name province_id //city
id name region_id // province
id name island_id // region
id name //island
I can display all island name by simply using this function
$island = $em->getRepository('Bundle:Island')->findAll();
which results in
array(4) {
[0]=>
array(1) {
["name"]=>
string(5) "Luzon"
}
[1]=>
array(1) {
["name"]=>
string(8) "Mindanao"
}
But I also want to count and get all voters in each Island.Any Ideas?
Update
I decided to create a service for this since this project is using Doctrine query 'heavily'..I tested this to create a custom filter in my twig.This method will count all voters per island name, just what I wanted above.
my custome twig filter
<?php
namespace Project\Bundle\DuterteBundle\Twig;
class AppExtension extends \Twig_Extension
{
protected $em;
public function __construct($em)
{
$this->em = $em;
}
public function getFunctions()
{
return array(
//this is the name of the function you will use in twig
new \Twig_SimpleFunction('number_votes', array($this, 'a'))
);
}
public function getName()
{
return 'app.extension';
}
public function a($id)
{
$qb=$this->em->createQueryBuilder();
$qb->select('count(v.id)')
->from('DuterteBundle:Voters','v')
->join('v.city','c')
->join('c.province','p')
->join('p.region','r')
->join('r.island','i')
->where('i.id = :x')
->setParameter('x',$id);
$count = $qb->getQuery()->getSingleScalarResult();
return $count;
}
}
In services.yml
duterte.twig.app_extension:
class: Project\Bundle\DuterteBundle\Twig\AppExtension
tags:
- { name: twig.extension }
arguments:
em: "@doctrine.orm.entity_manager"
now in template
{% for island in island %}
{{ number_votes(island.id) }}
{% endfor %}
outputs
mindanao= 500,
visayas = 670;
so on....
Perfect..This is what I really wanted in the controller.To display island names as well retrieve counts per island name.Now Is it posible to use this in controller?
I tried this in controller
$no = $this->container->get('duterte.twig.app_extension');
datas = array();//initialise array
foreach ($island as $is) {
//$no = $this->container->get('duterte.twig.app_extension');
$datas[] = array($is->getName(),$no->number_votes($is->getId()));
}
But i cant use the method
Attempted to call an undefined method named "number_votes" of class "Project\Bundle\DuterteBundle\Twig\AppExtension".
Got it finally..After spending hours trying to make this code to work, I finally got the solution.
First, I haven't found a solution in counting values a multiple join in 5 tables in Doctrine2. So creating a service is my final option.I created a Twig extension and call it inside the controller.
$island = $em->getRepository('DuterteBundle:Island')->createQueryBuilder('i')
->orderBy('i.name', 'ASC')
->getQuery()
->getResult();
My Twig extension as services looks exactly like this
public function a($id)
{
$em = $this->getDoctrine()->getManager();
$qb=$em->createQueryBuilder('i');
$qb->select('count(v.id)')
->from('DuterteBundle:Voters','v')
->join('v.city','c')
->join('c.province','p')
->join('p.region','r')
->join('r.island','i')
->where('i.id = :x')
->setParameter('x',$id);
$count = $qb->getQuery()->getSingleScalarResult();
return $count;
}
Now inside the controller
$dat = $this->container->get('app_extension');
$datas = array();//initialise array
foreach ($island as $is) {
$datas[] = array($is->getName(),$dat->a($is->getId()));
}
Whoops, no errors but does not display any result.
So casting as integer will make this finally work , so
$dat = $this->container->get('app_extension');
$datas = array();//initialise array
foreach ($island as $is) {
$datas[] = array($is->getName(),(int)$dat->a($is->getId()));//here
}