CODEWITHSUNDEEP
symfonydoctrine-ormcountdoctrine
Reico Cannella
Reico Cannella

Reputation: 3

Symfony2 + Doctrine, trying to COUNT the join-table results

I created this query in Symfony

$qb = $em->createQueryBuilder('b')
        ->from('AppBundle:Blueprint', 'b')
        ->select('b, COUNT(v.id) as votecount')
        ->leftJoin('AppBundle:Vote', 'v', 'WITH', 'b.id = v.blueprint')
        ->groupBy('b.id')
        ->orderBy('votecount', 'desc')
    ;

This is the Query i'm trying to achieve

select *, COUNT(v.blueprint_id) as votecount from blueprint b
left join vote v on b.id = v.blueprint_id
where b.name like '%%'
group by b.id
order by votecount desc

when i'm trying to access the Blueprint.name in twig i get the following error:

Key "name" for array with keys "0, votecount" does not exist.

so i set 'b.name as name' and then it keeps going for each variable i try to access in twig. Do i really have to set an alias for each variable i try to render in twig?

I'm working on that problem since like 2 hours already, used many online ressources but i just cant find a solution for this very simple problem.

Thanks in advance for your help!

Upvotes: 0

Views: 1557

Answers (1)

Kep
Kep

Reputation: 5857

This is dry-coded but I hope it helps you get on your track:

In your controller, for example:

/**
* @Route("/", name="my_route")
* @Template("some_template.html.twig")
*/
public function myAction(Request $request)
{
    $em = $this->getDoctrine()->getManager();

    $qb = $em->createQueryBuilder('b')
            ->from('AppBundle:Blueprint', 'b')
            ->select('b, COUNT(v.id) as votecount')
            ->leftJoin('AppBundle:Vote', 'v', 'WITH', 'b.id = v.blueprint')
            ->groupBy('b.id')
            ->orderBy('votecount', 'desc')
        ;

    $blueprint_votes = $qb->getQuery()->getResult();

    return array(
        "blueprint_votes" => $blueprint_votes
    );
}

In your template (some_template.html.twig):

<table>
    <thead>
        <tr>
            <th>Blueprint ID</th>
            <th>Blueprint Name</th>
            <th>Votes</th>
        </tr>
    </thead>
    <tbody>
        {%- for bv in blueprint_votes -%}
        <tr>
            <td>{{ bv[0].id }}</td>
            <td>{{ bv[0].name }}</td>
            <td>{{ bv['votecount'] }}</td>
        </tr>
        {%- endfor -%}
    </tbody>
</table>

Upvotes: 2

Related Questions