Symfony2|Doctrine2 - SQL to DQL: create a queryBuilder correctly in a repository, then call the query in a controller

Question

I have this SQl request I use for a table in my Symfony project:

SELECT u.name, count(distinct o.id), count(distinct co.id)
            FROM `users` u
            LEFT OUTER JOIN orders o ON u.id = o.user_id
            LEFT OUTER JOIN cancelledOrders co ON o.id = co.order_id
            GROUP BY u.id

I modified this SQl request in order to have it in DQL like this:

SELECT u.name,
count(distinct o.id) AS ocnt,
count(distinct co.id) AS cocnt
FROM MyBundle:Users u
LEFT OUTER JOIN  MyBundle:Orders o WITH u.id = o.user
LEFT OUTER JOIN MyBundle:CancelledOrders co WITH o.id = co.order
GROUP BY u.id

The request works well. But I know it is not advisable to directly make a DQL request like this in a controller, according to the symfonyBook. I would like to make a QueryBuilder in a repository and called my method in my controller correctly. Do you know how to do this exactly ?

I try this in my UsersRepository.php :

public function resumeUsersOrders () {
    return $this->createQueryBuilder('u')
                ->select('count(o.id)')
                ->addSelect('count(co.id)')
                ->from('MyBundle:Users', 'u')
                ->addFrom('MyBundle:Orders', 'o')
                ->addFrom('MyBundle:CancelledOrders', 'co')
                ->leftJoin('o.user','WITH', 'u.id = o.user')
                ->leftJoin('co.order', 'WITH', 'o.id = co.order')
                ->groupBy('p.id')
                ->getQuery()
                ->getResult();
}

For now I do not know if it works really because i have an error from my controller when I try to launch my webpage:

Undefined method 'resumeUsersOrders'. The method name must start with either findBy or findOneBy! 500 Internal Server Error - BadMethodCallException

And in my controller, I called the method like this:

public function indexUsersAction() {
    $user = $em->getRepository('MyBundle:Users');
               ->resumeUsersorders();

return $this->render('MyBundle:MyFolder:indexUsers.html.twig', array('user' => $user ));
}

In my twig I display the result in an html like this:

<table id="dataTablesUsers" class="table table-bordered table-hover" cellspacing="0">
     <thead>
         <tr>
             <th>Idt</th>
             <th>Name</th>
             <th>Orders'number</th>
             <th>Cancelled Orders'number</th>
             <th>Edit Action </th>
         </tr>
     </thead>
     <tbody>
          {% for user in users%}
              <tr>
                  <td>{{ user.id }}</td>
                  <td>{{ user.name}}</td>
                  <td>{{ user.ocnt }}</td>
                  <td>{{ user.cocnt }}</td>
                  <td><a href="{{ path('edit', {'nom': users.name}) }}"><button class="btn btn-warning btn-xs">Edit</button></a></td>
              </tr>
          {% endfor %}
     </tbody> 
  </table>

---

UPDATE

In fact the problem was that I wrote my annotations like this:

<?php

namespace My\Entity\Namespace;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="My\Repo\Namespace\RepoClass")
 * @ORM\Entity
 */
class MyEntity
{
    ...
}

Or the line * @ORM\Entity under @ORM\Entity(repositoryClass="My\Repo\Namespace\RepoClass") erase all before if I keep this annotation. I remove that, and the method is found by Symfony.

Now I have another error:

[Semantical Error] line 0, col 210 near 'WITH GROUP BY': Error: 'WITH' is already defined. 500 Internal Server Error - QueryException 1 linked Exception: QueryException »

This is my repository function in my repository class:

public function resumeUsersOrders () {
    return $this->createQueryBuilder('u')
                ->select('count(o.id)')
                ->addSelect('count(co.id)')
                ->from('MyBundle:Orders', 'o')
                ->from('MyBundle:CancelledOrders', 'co')
                ->leftJoin('o.user','WITH', 'u.id = o.user')
                ->leftJoin('co.order', 'WITH', 'o.id = co.order')
                ->groupBy('p.id')
                ->getQuery()
                ->getResult();
}

And my controller in order to call the methods:

public function indexUsersAction() {
    $user = $em->getRepository('MyBundle:Users');
               ->resumeUsersorders();

return $this->render('MyBundle:MyFolder:indexUsers.html.twig', array('user' => $user ));
}

Someone could help?

Thank you.

Answer 1

The error you're seeing is because you didn't tell Doctrine that UsersRepository is actually your repository class.

So it uses it's default EntityRepository class which doesn't have your method defined.

Assuming you're using annotation mapping you can do that like so:

<?php

namespace My\Entity\Namespace;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="My\Repo\Namespace\RepoClass")
 */
class MyEntity
{
}

If you're using some other mapping format like YAML or XML you can see how to do it in the docs.