Understanding problem Doctrine 2 ORM Query Builder in Symfony 4

Question

I hope there are a few helpful and prefer German Symfony experts. For many years I have been working with PHP and now I tryed in a framework. I chose Symfony now because I like the components the most. The QueryBuilder and I stand on a war foot - I just do not understand it. Storing values works very well so far, though I doubt that I'm doing this in the sense of the framework. I'm really helpless. Currently I managing it by chasing everything in raw format but I'm not really happy with it.

How I can implement the following with Doctrine?

use App\Entity\Questions;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Doctrine\ORM\EntityManager;

class QuestionsController extends AbstractController
{
/**
 * @Route("/addquestion", methods={"POST","HEAD"})
 */
public function addQuestion()
{
    $entityManager = $this->getDoctrine()->getManager();

    $RAW_QUERY = 'SELECT COUNT(*) AS c FROM questions WHERE questToID = '.$_POST['u'].' AND  questFrom = '.$this->getUser()->getId().';';


    $statement = $entityManager->getConnection()->prepare($RAW_QUERY);
    $statement->execute();

    $total = $statement->fetchAll();

    if($total[0]['c'] >= 3)
    {
        return new Response('error|Du kannst der selben Person maximal drei Fragen stellen.');  
    }
 [...]

I have already tried to implement this, and many other things (no avail): Count Rows in Doctrine QueryBuilder

Since I speak bad English, I very much hope that you understand me…

Answer 1

One simple way of doing that would be something like this (with the assumption that you have a "Question" entity - and that the fields defined there are matching with the column names from your raw query).

$em = $this->getDoctrine()->getManager();
$userQuestions = $em->getRepository(Question:class)->findAll(['questToID' => $_POST['u'], 'questFrom ' => $this->getUser()->getId()]);

$total = count($userQuestion);

Or if you prefer to have just the count from the query instead of fetching all the matching objects and counting them, you can write the query builder part something like this (in this format this is meant to be written in your controller as you have with your raw query - in general the "correct" place would be QuestionRepository.php class from where this would be just called in controller):

$em = $this->getDoctrine()->getManager();
$qb = $em->createQueryBuilder('q');
$qb->select('count(q)');
$qb->andWhere('q.questToID = :questToID');
$qb->andWhere('q.questFrom = :questFrom');

$qb->setParameter('questToID', $_POST['u']);
$qb->setParameter('questFrom ', $this->getUser()->getId());

$total = $qb->getQuery()->getSingleScalarResult();

Answer 2

so getting the value with $_POST['u'] is not recommended her you can either pass the value with the route an retrieve it as a parameter for the function:

/**
 * @Route("/addquestion/{u}", methods={"POST","HEAD"})
 */
public function addQuestion($u)

or you can get it out from the request it self:

/**
 * @Route("/addquestion", methods={"POST","HEAD"})
 */
public function addQuestion(Request $request)

if you want to use the query builder the best practice would be to create a repository for you entity and make you the query there:

namespace App\Repository;

use App\Entity\Questions;
use Doctrine\ORM\EntityRepository;

class QuestionsRepository extends EntityRepository
{
    public function findUserQuestions($user, $u)
    {
          $queryBuilder = $this->createQueryBuilder('c')
            ->where('c.questToID = :questToID')
            ->andWhere('c.questFrom = :questFrom')
            ->setParameters(['questToID'=>$u,'questFrom'=>$user]);
            return $queryBuilder->getQuery()->getResult();
    } 

and in you controller you can get the result:

$userQuestions = $this->getDoctrine()->getRepository(Questions::class)->findUserQuestions($this->getUser(), $u);

count($userQuestions);

i hope this will be useful in your case and explain you a bit how its done in symfony