This tutorial has a new version, check it out!
> Symfony 4 > Doctrine & the Database in Symfony 4
Login to bookmark this video
Buy Access to Course
10.

Custom Queries

Share this awesome video!

|

Keep on Learning!

Start your All-Access Pass

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

How do you write custom queries in Doctrine? Well, you're already familiar with writing SQL, and, yea, it is possible to write raw SQL queries with Doctrine. But, most of the time, you won't do this. Instead, because Doctrine is a library that works with many different database engines, Doctrine has its own SQL-like language called Doctrine query language, or DQL.

Fortunately, DQL looks almost exactly like SQL. Except, instead of table and column names in your query, you'll use class and property names. Again, Doctrine really wants you to pretend like there is no database, tables or columns behind the scenes. It wants you to pretend like you're saving and fetching objects and their properties.

Introducing: The Query Builder

Anyways, to write a custom query, you can either create a DQL string directly, or you can do what I usually do: use the query builder. The query builder is just an object-oriented builder that helps create a DQL string. Nothing fancy.

And there's a pretty good example right here: you can add where statements order by, limits and pretty much anything else:

49 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
->orderBy('a.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
// ... lines 36 - 47
}

One nice thing is that you can do this all in any order - you could put the order by first, and the where statements after. The query builder doesn't care!

Oh, and see this andWhere()?

49 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
// ... lines 29 - 33
;
}
// ... lines 36 - 47
}

There is a normal where() method, but it's safe to use andWhere() even if this is the first WHERE clause. Again the query builder is smart enough to figure it out. I recommend andWhere(), because where() will remove any previous where clauses you may have added... which... can be a gotcha!

DQL - and so, the query builder - also uses prepared statements. If you're not familiar with them, it's a really simple idea: whenever you want to put a dynamic value into a query, instead of hacking it into the string with concatenation, put : and any placeholder name. Then, later, give that placeholder a value with ->setParameter():

49 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
// ... lines 30 - 33
;
}
// ... lines 36 - 47
}

This prevents SQL injection.

Writing our Custom Query

In our case, we won't need any arguments, and I'm going to simplify a bit. Let's say andWhere('a.publishedAt IS NOT NULL'):

47 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
// ... lines 29 - 31
;
}
// ... lines 34 - 45
}

You can totally see how close this is to normal SQL. You can even put OR statements inside the string, like a.publishedAt IS NULL OR a.publishedAt > NOW().

Oh, and what the heck does the a mean? Think of this as the table alias for Article in the query - just like how you can say SELECT a.* FROM article AS a.

It could be anything: if you used article instead, you'd just need to change all the references from a. to article..

Let's also add our orderBy(), with a.publishedAt, DESC:

47 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
// ... lines 30 - 31
;
}
// ... lines 34 - 45
}

Oh, and this is a good example of how we're referencing the property name on the entity. The column name in the database is actually published_at, but we don't use that here.

Finally, let's remove the max result:

47 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
// ... lines 30 - 31
;
}
// ... lines 34 - 45
}

Once you're done building your query, you always call getQuery() and then, to get the array of Article objects, getResult():

47 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}
// ... lines 34 - 45
}

Below this method, there's an example of finding just one object:

47 lines | src/Repository/ArticleRepository.php
// ... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
// ... lines 17 - 34
/*
public function findOneBySomeField($value): ?Article
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
->getQuery()
->getOneOrNullResult()
;
}
*/
}

It's almost the same: build the query, call getQuery(), but then finish with getOneOrNullResult().

So, in all normal situations, you always call getQuery(), then you'll either call getResult() to return many rows of articles, or getOneOrNullResult() to return a single Article object. Got it?

Now that our new findAllPublishedOrderedByNewest() method is done, let's go use it in the controller: $repository->, and there it is!

81 lines | src/Controller/ArticleController.php
// ... lines 1 - 15
class ArticleController extends AbstractController
{
// ... lines 18 - 30
public function homepage(EntityManagerInterface $em)
{
$repository = $em->getRepository(Article::class);
$articles = $repository->findAllPublishedOrderedByNewest();
// ... lines 35 - 38
}
// ... lines 40 - 79
}

Let's give it a try! Move over and, refresh! Perfect! The order is correct and the unpublished articles are gone.

Autowiring ArticleRepository

To make this even cooler, let me show you a trick. Instead of getting the entity manager and then calling getRepository() to get the ArticleRepository, you can take a shortcut: just type ArticleRepository $repository:

81 lines | src/Controller/ArticleController.php
// ... lines 1 - 5
use App\Repository\ArticleRepository;
// ... lines 7 - 16
class ArticleController extends AbstractController
// ... lines 18 - 31
public function homepage(ArticleRepository $repository)
{
$articles = $repository->findAllPublishedOrderedByNewest();
// ... lines 35 - 38
}
// ... lines 40 - 79
}

This works for a simple reason: all of your repositories are automatically registered as services in the container. So you can autowire them like anything else. This is how I actually code when I need a repository.

And when we refresh, no surprise, it works!

Custom queries are a big topic, and we'll continue writing a few more here and there. But if you have something particularly challenging, check out our Go Pro with Doctrine Queries tutorial. That tutorial uses Symfony 3, but the query logic is exactly the same as in Symfony 4.

Next, I want to show you two more tricks: one for re-using query logic between multiple queries, and another super shortcut to fetch any entity with zero work.