This tutorial has a new version, check it out!
> Symfony 5 > Go Pro with Doctrine Queries (Legacy)
Buy Access to Course
01.

Doctrine DQL

Share this awesome video!

|

Look, I know you already understand how to do queries in SQL - maybe you dream of JOINs, orders and sub-queries. That's really dorky, but I get it. But when you look at Doctrine, it's totally different - with its DQL and its query builder, with their own ways of doing joins, and this hydration of objects thing.

But did you know you can write native SQL queries in Doctrine? Yep! And you can opt in or out of any of its features. Use more of them in one place, where life is easier or when you're feeling like a Doctrine pro. Then go simpler and use less when things get tough or you need to squeeze out ever ounce of performance.

We'll learn about all of that. And don't worry - if you're good at SQL, you're going to be great at writing queries in Doctrine.

Query for Fortune Cookies

Our app is a Fortune Cookie inventory system. Yep, we've finally hit the big time: working for a company that can tell you your future, wrapped up inside a cheap cookie shell.

There are six different fortune categories that are loaded from the database. And if you click on any of these, we see all of the fortunes for the category and how many have been printed.

The project is a small Symfony app - but all the Doctrine stuff translates to any app using the Doctrine ORM. We have 2 entities: Category and FortuneCookie:

113 lines | src/AppBundle/Entity/Category.php
// ... lines 1 - 7
/**
* Category
*
* @ORM\Table(name="category")
* @ORM\Entity(repositoryClass="AppBundle\Entity\CategoryRepository")
*/
class Category
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @var string
*
* @ORM\Column(name="name", type="string", length=255)
*/
private $name;
/**
* @var string
*
* @ORM\Column(name="iconKey", type="string", length=20)
*/
private $iconKey;
/**
* @ORM\OneToMany(targetEntity="FortuneCookie", mappedBy="category")
*/
private $fortuneCookies;
// ... lines 43 - 111
}

176 lines | src/AppBundle/Entity/FortuneCookie.php
// ... lines 1 - 12
class FortuneCookie
{
/**
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;
/**
* @var Category
*
* @ORM\ManyToOne(targetEntity="Category", inversedBy="fortuneCookies")
* @ORM\JoinColumn(nullable=false)
*/
private $category;
/**
* @var string
*
* @ORM\Column(name="fortune", type="string", length=255)
*/
private $fortune;
// ... lines 38 - 174
}

With a ManyToOne relation from FortuneCookie to the Category:

176 lines | src/AppBundle/Entity/FortuneCookie.php
// ... lines 1 - 12
class FortuneCookie
{
// ... lines 15 - 23
/**
* @var Category
*
* @ORM\ManyToOne(targetEntity="Category", inversedBy="fortuneCookies")
* @ORM\JoinColumn(nullable=false)
*/
private $category;
// ... lines 31 - 174
}

For our homepage, we're using the entity manager to fetch the Category's repository and call the built-in findAll function:

47 lines | src/AppBundle/Controller/FortuneController.php
// ... lines 1 - 10
/**
* @Route("/", name="homepage")
*/
public function homepageAction()
{
$categoryRepository = $this->getDoctrine()
->getManager()
->getRepository('AppBundle:Category');
$categories = $categoryRepository->findAll();
return $this->render('fortune/homepage.html.twig',[
'categories' => $categories
]);
}
// ... lines 26 - 47

This returns every Category, and so far, it lets us be lazy and avoid writing a custom query. The template loops over these and prints them out. AMAZING.

Doctrine Query Language (DQL)

Time to write a query! One that will order the categories alphabetically. Call a new method called findAllOrdered():

47 lines | src/AppBundle/Controller/FortuneController.php
// ... lines 1 - 13
public function homepageAction()
{
$categoryRepository = $this->getDoctrine()
->getManager()
->getRepository('AppBundle:Category');
$categories = $categoryRepository->findAllOrdered();
// ... lines 21 - 24
}
// ... lines 26 - 47

This needs to live inside the CategoryRepository class. So create a public function findAllOrdered(). To prove things are wired up, put a die statement:

20 lines | src/AppBundle/Entity/CategoryRepository.php
// ... lines 1 - 12
class CategoryRepository extends EntityRepository
{
public function findAllOrdered()
{
die('this query will blow your mind...');
}
}

Refresh! Sweet, ugly black text - we're hooked up!

Ok, so you're used to writing SQL, maybe MySQL queries. Well, Doctrine speaks a different language: DQL, or Doctrine Query Language. Don't worry though, it's so close to SQL, most of the time you won't notice the difference.

Let's see some DQL. So: $dql = 'SELECT cat FROM AppBundle\Entity\Category cat';:

24 lines | src/AppBundle/Entity/CategoryRepository.php
// ... lines 1 - 12
class CategoryRepository extends EntityRepository
{
public function findAllOrdered()
{
$dql = 'SELECT cat FROM AppBundle\Entity\Category cat';
// ... lines 18 - 21
}
}

The big DQL difference is that instead of working with tables, you're working with PHP classes. And that's why we're selecting from the full class name of our entity. Symfony users are used to saying AppBundle:Category, but that's just a shortcut alias - internally it always turns into the full class name.

The cat part is an alias, just like SQL. And instead of SELECT *, you write the alias - SELECT cat. This will query for every column. Later, I'll show you how to query for only some fields.

Executing DQL

To run this, we'll create a Query object. Get the EntityManager, call createQuery() and pass it in the DQL. And once we have the Query object, we can call execute() on it:

24 lines | src/AppBundle/Entity/CategoryRepository.php
// ... lines 1 - 12
class CategoryRepository extends EntityRepository
{
public function findAllOrdered()
{
$dql = 'SELECT cat FROM AppBundle\Entity\Category cat';
$query = $this->getEntityManager()->createQuery($dql);
return $query->execute();
}
}

This will return an array of Category objects. Doctrine's normal mode is to always return objects, not an array of data. But we'll change that later.

Let's query for some fortunes! Refresh the page. Nice - we see the exact same results - this is what findAll() was doing in the background.

Adding the ORDER BY

To add the ORDER BY, it looks just like SQL. Add ORDER BY, then cat.name DESC:

24 lines | src/AppBundle/Entity/CategoryRepository.php
// ... lines 1 - 14
public function findAllOrdered()
{
$dql = 'SELECT cat FROM AppBundle\Entity\Category cat ORDER BY cat.name DESC';
$query = $this->getEntityManager()->createQuery($dql);
return $query->execute();
}
// ... lines 23 - 24

Refresh! Alphabetical categories! So that's DQL: SQL where you mention class names instead of table names. If you Google for "Doctrine DQL", you can find a lot more in the Doctrine docs, including stuff like joins.

Show me the SQL!

Of course ultimately, Doctrine takes that DQL and turns it into a real MySQL query, or PostgreSQL of whatever your engine is. Hmm, so could we see this SQL? Well sure! And it might be useful for debugging. Just var_dump $query->getSQL():

25 lines | src/AppBundle/Entity/CategoryRepository.php
// ... lines 1 - 14
public function findAllOrdered()
{
$dql = 'SELECT cat FROM AppBundle\Entity\Category cat ORDER BY cat.name DESC';
$query = $this->getEntityManager()->createQuery($dql);
var_dump($query->getSQL());die;
return $query->execute();
}
// ... lines 24 - 25

Refresh! It's not terribly pretty, but there it is. For all the coolness, tried-and-true SQL lives behind the scenes. Remove that debug code.