DEV Community

Hanane Kacemi
Hanane Kacemi

Posted on

3 3

Symfony & Doctrine: Part 5

Doctrine has built-in methods like findAll, findBy…. For specific needs these functions may not be sufficient, that’s why we can write custom queries with multiples criteria (with join, where,order by … clauses).

Doctrine has its own language, like SQL, to write queries. It's called DQL (Doctrine Query Language), the difference is that in SQL we use Tables & columns and in DQL we use classes and properties.

QueryBuilder object is used to create DQL query, to have a list of jobs that are not expired, we can write a custom query in JobRepository like this :

JobRepository getActiveJobs
DQL uses prepared statement which prevents from SQL injection. Instead of concatenating values to the query we use Placeholder (->andWhere('j.exampleField = :val')) and we pass the value via setParameter (->setParameter('val', $value))

To get result(s) we always call getQuery() _function and then we either call _getResult() to return many rows or getOneOrNullResult() to have a single object.

Now, let's assume that we want to get the category of each job. We have a ManyToOne relationship between Job and Category (Many jobs belong to one Category). We can easily have the category using this syntax in twig :
{{ job.category.name }}
But, check the profiler -> Doctrine, we have multiple queries !

n+1 performance problem

We expected to have the 1st one to query all jobs that are not expired, but once we have added the code to have the category in twig, Doctrine had to make other queries to get the name of each category related to a single job, this is known as N+1 performance problem.

This is a good example to see the power of DQL, we can change our previous query as below in order to have one query instead of multiple ones :

 public function findNoneExpiredJobs()
    {
        return $this->createQueryBuilder('j')
            ->innerJoin('j.category', 'cat')
            ->addSelect('cat')
            ->andWhere('j.expiresAt > :today')
            ->setParameter('today', new \DateTime())
            ->orderBy('j.createdAt', 'DESC')
            ->getQuery()
            ->getResult()
        ;
    }
Enter fullscreen mode Exit fullscreen mode

And if we check again the profiler, we have only one query :

resolve N+1 performance issue

That's all, thanks for reading :)

Image of Datadog

Create and maintain end-to-end frontend tests

Learn best practices on creating frontend tests, testing on-premise apps, integrating tests into your CI/CD pipeline, and using Datadog’s testing tunnel.

Download The Guide

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

AWS GenAI LIVE!

GenAI LIVE! is a dynamic live-streamed show exploring how AWS and our partners are helping organizations unlock real value with generative AI.

Tune in to the full event

DEV is partnering to bring live events to the community. Join us or dismiss this billboard if you're not interested. ❤️