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 :

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 !

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()
        ;
    }

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

That's all, thanks for reading :)