Buy

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 - 13
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.

Leave a comment!

  • 2016-10-28 Johan

    Ye I noticed :)

    It does not help with DQL queries, but indeed it helps with the query builders which I will probably be using most of the time.

    Thanks Victor!

  • 2016-10-28 Victor Bocharsky

    Hey Johan,

    Use the same Symfony plugin for PhpStorm - it should help with it a bit, at least it helps with doctrine query builders.

    Cheers!

  • 2016-10-27 Johan

    Is there a PHPStorm plugin that helps me with my DQL queries? It looks like it's interpreting it as regular SQL which is not helping very much :)

  • 2016-09-01 weaverryan

    Sweeeeeet! Happy it's working! If you missed any files, I think it'll be obvious when you get there :).

    Good luck!

  • 2016-08-31 Yang Liu

    yeah thanks, I tried to composer update first, but running into some issures and the whole project just broke... and of course there are the issures you mentioned (bin/console and the new var directory). Now I started a new symfony 3 project from scratch und c&p the files (I hope I got them all). It's working for now at least^^

  • 2016-08-30 weaverryan

    Hi Yang!

    It's not *quite* that simple, but it almost is :). But first, even though this is on 2.6, there should be much/any difference between this project and Symfony 3. But, if you want to upgrade, you should:

    A) Make the above change in composer.json. You could also, optionally, upgrade any other dependency versions to what the Symfony Standard edition uses in its latest version: https://github.com/symfony/sym...

    B) Run composer update

    And, that *might* work, except that you won't have the new "3.0" directory structure (e.g. app/console converted to bin/console). If you're *really* interested, you can check out: http://knpuniversity.com/scree...

    Alternatively, if you want to code along in Symfony 3, you could start a fresh Symfony3 project and steal important bits of code from this project. I bet you'd learn a few cool things either way :).

    Cheers!

  • 2016-08-30 Yang Liu

    Hi Ryan,
    it seems you are using symfony 2.6 for this tutorial. I just watched all of your symfony 3 tutorials and started coding in symfony3, is there a way to use symfony 3 in this project too? I don't suppose change "symfony/symfony": "2.6.*" in composer.json to "symfony/symfony": "3.1.*" will do the trick?

  • 2016-04-05 weaverryan

    Hi Andrew!

    If you're a subscriber or own this course (it's only available currently for these people), then you should have a "Download" button at the top right of *this* page. Download the code, unzip it, and look for the "start" directory inside.

    I hope that helps!

  • 2016-04-02 Andrew Grudin

    May I download start code for the app(Fortune Cookie) from somewhere?