Do not use from with getRepository when creating query builder

In my symfony project, I was using the query builder in order to query a table:

namespace App\Infrastructure\Services;

use Doctrine\ORM\EntityManagerInterface;

use App\Entity\MyModel

class FetchResultFromDb 
{

    public function __construct(private EntityManagerInterface $entityManager)
    {
    }


    public function getEarliestResult(int $rowNum)
    {

        $queryBuilder = $this->entityManager->getRepository(MyModel::class)->createQueryBuilder('m');

        return $queryBuilder
            ->from(MyModel::class, 'm')
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery()
            ->getResult();

    }  

}

But I noticed that calling the getEarliestResult on a row containing ~10000 rows took too long:

namespace App\Command;


use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Style\SymfonyStyle;

#[AsCommand(
    name: 'test:query',
    description: 'Add a short description for your command',
)]
class TestQueryCommand extends Command
{
    public function __construct(private readonly FetchResultFromDb $service)
    {
        parent::__construct();
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $results = $this->service->getEarliestResult(5000);

        // Rest of code gows here ommited for simplicity
    }
}

In my case was ~75 second.

In order to see the cause of this delay the first stem was to refactor my method and dump the generated SQL:

namespace App\Infrastructure\Services;

use Doctrine\ORM\EntityManagerInterface;

use App\Entity\MyModel

class FetchResultFromDb 
{

    public function __construct(private EntityManagerInterface $entityManager)
    {
    }


    public function getEarliestResult(int $rowNum)
    {

        $queryBuilder = $this->entityManager->getRepository(MyModel::class)->createQueryBuilder('m');

        $q=$queryBuilder
            ->from(MyModel::class, 'm')
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery();

       dump($q->getSql());
       return $q->getResult();
    }  

}

But the dump resulted retrieving this sql:

SELECT m0_.id AS id_0 FROM my_model m1_, my_model m0_ ORDER BY m0_.sent_timestamp ASC LIMIT 5000;

As you can see, the query does a cartetian product of all entries in database. That means that query iterates 5000X5000=25000000 results for just fetching only the 5000.

That is wasteful of resources. In my case, I just wanted to do a simpler query:

SELECT id from my_model order by sent_timestamp LIMIT 5000

SOLUTION

I managed to solve it this issue using 2 approaches:

Approach 1 Do not use getRepository when creating a query builder:

namespace App\Infrastructure\Services;

use Doctrine\ORM\EntityManagerInterface;

use App\Entity\MyModel

class FetchResultFromDb 
{

    public function __construct(private EntityManagerInterface $entityManager)
    {
    }


    public function getEarliestResult(int $rowNum)
    {

        $queryBuilder = $this->entityManager->createQueryBuilder('m');

        return $queryBuilder
            ->from(MyModel::class, 'm')
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery()
            ->getResult();

    }  

}

As you can see, instead of using $this->entityManager->getRepository(MyModel::class)->createQueryBuilder('m'); I used $this->entityManager->createQueryBuilder('m');

In other words, I removed the call of the method getRepository

Approach 2 Do not use from in the query Builder:

namespace App\Infrastructure\Services;

use Doctrine\ORM\EntityManagerInterface;

use App\Entity\MyModel

class FetchResultFromDb 
{

    public function __construct(private EntityManagerInterface $entityManager)
    {
    }


    public function getEarliestResult(int $rowNum)
    {

        $queryBuilder = $this->entityManager->getRepository(MyModel::class)->createQueryBuilder('m');

        $q=$queryBuilder
            ->select('m.id')
            ->orderBy('m.sentTimestamp', 'ASC')
            ->setMaxResults($rowNum)
            ->getQuery();

       return $q->getResult();
    }  

}

Calling getRepository(MyModel::class) and then createQueryBuilder('m') has a result for the query builder to append on its own the necessary from section in the final sql that qill be executed upon db.

Therefore, placing from does not offer any beneft whatoever.

Conclution

To sum up when creating a query builder do it like this:

$this->entityManager->createQueryBuilder('m');

If you get the repository first avoid using from because it results a cartetian product.