Symfony2, createQueryBuilder count one to many relationship

Question

I have 2 entities linked by a one to many relation. Recruitment and Candidat You may have many candidats for one recruitment.

I want to list all recruitment and count how many candidat each recruitment has.

I use the recruitment repository and put the code:

public function myFindAllRecruitment() { $qb = $this->createQueryBuilder('r'); $qb->select('r'); $qb->Join('r.candidat', 'c'); $qb->addSelect("COUNT(c.id) as candidatCount"); $qb->groupBy('r.id'); $qb->orderBy('r.id', 'DESC'); return $qb ->getQuery() ->getResult() ; }

In my RecruitmentController I have:

$listRecruitment = $repository->myFindAllRecruitment();

In my TWIG view something like:

{% for recruitment in listRecruitment %} <tr> {#(this is line 48)#}<td>{{ recruitment.id }}</td> <td>{{ recruitment.titleFr }}</td> <td>{{ recruitment.locationFr }}</td>......

And I get this error: "Key "id" for array with keys "0, candidatCount" does not exist in MyBundle:Recruitment:index.html.twig at line 48"

If someone knows what wrong with my query it will be nice. Thank you

Notice that I tried join and leftJoin in the query

sim100
– sim100

2017-06-18 02:46:28 +00:00
Commented Jun 18, 2017 at 2:46 — sim100
– sim100, Commented Jun 18, 2017 at 2:46

Petro Popelyshko · Accepted Answer · 2017-06-18 09:51:35Z

So you have this Candidat entity like this

...... /** * @ORM\ManyToOne(targetEntity="Recruitment", inversedBy="candidates") */ $recruitment; ....

Then your query should look like this:

 public function myFindAllRecruitment() { $qb = $this->createQueryBuilder('r'); $qb->select('r'); ->addSelect('(SELECT count(c) FROM PATHTO\Bundle\Entity\Candidat as c WHERE c.rectuitment = r.id group by c.rectuitment) as count)'); ->orderBy('r.id', 'DESC'); return $qb->getQuery()->getResult(); }

you will get output like this:

[ 0 => [ 'rectuitment' => ...Object, 'count'=> ..., ... ]

sim100 · Accepted Answer · 2017-06-19 06:43:45Z

Ok, thank you, I found the solution. It was in the TWIG render:

Objects were accessible with [0] and [1] or ['candidatCount'] for the the count

Code exemple:

{% for recruitment in listRecruitment %} <tr> <td>{{ recruitment[0].id }}</td> <td>{{ recruitment[0].titleFr }}</td> <td>{{ recruitment[0].locationFr }}</td> <td>{{ recruitment[0].typePoste }}</td> <td>{{ recruitment[0].dateins|date('Y-m-d') | localizeddate('full', 'none') }}</td> <td> {{ recruitment['candidatCount'] }}........

And the query was good. Thank you

Collectives™ on Stack Overflow

Symfony2, createQueryBuilder count one to many relationship

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related