How to properly count all the rows from a table with Doctrine in Symfony 4

In this short article, we will explain you how to count how many record are there in a table with a primary key with Doctrine in Symfony 4.

Count all rows from a table (repository)

In this example, we'll assume that you already have tables in your database and you already created the models for them. Here, we will use an Articles model available in the Entity\Articles.php file:

<?php

// src/Entity/Articles.php
namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Articles
 *
 * @ORM\Table(name="articles")
 * @ORM\Entity(repositoryClass="App\Repository\articlesRepository")
 */
class Articles
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="bigint", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=false)
     */
    private $name;

    /**
     * @var string|null
     *
     * @ORM\Column(name="video", type="text", length=65535, nullable=true)
     */
    private $video;

    /**
     * @var string
     *
     * @ORM\Column(name="content", type="text", length=0, nullable=false)
     */
    private $content;
 

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    } 

    public function getContent(): ?string
    {
        return $this->content;
    }

    public function setContent(string $content): self
    {
        $this->content = $content;

        return $this;
    } 
}

The table contains a primary key with the id identifier as the primary. In our controller, we will count how many rows are in the table with the following query:

<?php

namespace App\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;

// Include class of the entity (table) that you want to query
use App\Entity\Articles; 

class AdminController extends AbstractController
{
    public function index()
    {  
        // 1. Obtain doctrine manager
        $em = $this->getDoctrine()->getManager();
        
        // 2. Setup repository of some entity
        $repoArticles = $em->getRepository(Articles::class);
        
        // 3. Query how many rows are there in the Articles table
        $totalArticles = $repoArticles->createQueryBuilder('a')
            // Filter by some parameter if you want
            // ->where('a.published = 1')
            ->select('count(a.id)')
            ->getQuery()
            ->getSingleScalarResult();
        
        // 4. Return a number as response
        // e.g 972
        return new Response($totalArticles);
    }
}

Note that we are not calling absolutely all the information from the table, but just the count instruction that will count how many fields do have the id field. The getSingleScalarResult function retrieves a single scalar value from the result returned by the dbms. If the result contains more than a single scalar value, an exception is thrown. The pure/mixed distinction does not apply.

Happy coding !