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!

  • 2017-08-03 Victor Bocharsky

    Hey Nina,

    It's something related to SQL knowledges, i.e. in your example "category c0_" means creating an alias "c0_" for "category" table. So now I can refer to this table by its short alias "c0_" where "c" is the first letter of real table name, and "0" just means an index. If you have a joins, you'll see that tables have different indexes and it makes sense, because you may have table names that start with the same latter like "posts", "products", etc. The same for column names like c0_.name AS name1 where name1 is the field alias. So with these aliases Doctrine reduces the query length - probably not too much important, but I suppose in this way it's easier to transform query builder into raw SQL queries. Actually, it comes from Doctrine internals.

    So in shorts, Doctrine uses SQL aliases because it's a good practice to use them and because it's easier to automate transformation of query builder into raw SQL queries. And probably you won't need to see those raw SQL queries, maybe just for debugging, but it's really rare case.

    Cheers!

  • 2017-08-03 Nina

    Hello
    Please, can your tell me we why dump() query looks like:
    string(105) "SELECT c0_.id AS id0, c0_.name AS name1, c0_.iconKey AS iconKey2 FROM category c0_ ORDER BY c0_.name DESC"
    what mean "c0_.id AS id0"?
    what is "c0_"?
    why it is written this " c0_.name AS name1"?

    why dump() query:
    $dql = 'SELECT cat FROM AppBundle\Entity\Category cat ORDER BY cat.name DESC';
    Doesn't look like for example:
    'SELECT id, name, iconKey FROM ORDER BY name DESC'; ?

  • 2017-07-24 Diego Aguiar

    Hey Nina!

    I believe you forgot to run "composer install"

  • 2017-07-21 Nina

    Hi guys!
    I have a problem with the course code and its installation.
    Fatal error: require_once(): Failed opening required 'C:\OpenServer\domains\startdoc\app/bootstrap.php.cache' (include_path='.;c:/openserver/modules/php/PHP-7.0-x64;c:/openserver/modules/php/PHP-7.0-x64/PEAR/pear') in C:\OpenServer\domains\startdoc\app\console on line 10

    How can I fix this error?

  • 2017-06-29 weaverryan

    Woohoo! And thanks for letting me know that fixed it! :)

  • 2017-06-29 Евгений Явгель

    Yes, I'm using PHP 7.1 and YES you are right: 'composer update' resolved my problem completely. Thanks for your immediate reply !!!

  • 2017-06-29 weaverryan

    Hey @Евгений Явгель:disqus!

    Hmm, let's see :). First, just in case, try a full composer update. Are you using PHP 7.1? It's possible that some library has an incompatibility with a newer PHP version, and running composer update may download a newer, compatible version.

    But if that doesn't work, can you send a screenshot of the error? I'm curious what the stacktrace looks like and exactly what part of the code is throwing that error.

    Cheers!

  • 2017-06-29 Евгений Явгель

    Hi guys!!! I have the same problem as argy_13. But when I try to fix this problem (1 - composer require --dev phpdocumentor/phpdocumentor or 2 - composer require doctrine/annotations) it didn't help me (( I still have problem [Symfony\Component\Debug\Exception\ContextErrorException]
    Warning: A non-numeric value encountered Do you know how can i fix it?

  • 2017-06-09 Diego Aguiar

    I'm glad to hear that you could fix your problem, let's keep learning :)

    Cheers!

  • 2017-06-09 argy_13

    Thanks for your immediate reply Diego!
    Unfortunately, this didn t help me, but because i m using symfony 3 (i am new in this) i found the solution here...
    https://stackoverflow.com/q...
    so instead of running to the terminal the command that you write in readme file of the course
    "php composer.phar install" because it gives me many exceptions
    i run the command from the second answer
    "composer require doctrine/annotations" and i didn t have none problem!
    p.s. of course i reinstall everything from scratch!
    But also thank you again for your answer and keep up the wonderful work that you re doing!

  • 2017-06-08 Diego Aguiar

    Hey argy_13

    For point 1): You have to locate your "php.ini" file, run in a terminal "$ php -i", then open it and activate your opcache or zend_optimize (it depends which one you are using), if you are using opcache, you just have to add this two lines to your file:


    opcache.enable=1
    opcache.load_comments=1


    If for some reason you have more troubles doing it, you can follow this guide: http://www.hostingadvice.co...

    I believe this should fix your whole problem, but if not, try updating "distribution-bundle" by running:
    $ composer update sensio/distribution-bundle

    I hope it fix your problem, have a nice day!

  • 2017-06-08 argy_13

    Hi guys!
    I have a problem with the course code and its installation through terminal in ubuntu!
    I follow the instructions of readme file and i got these errors when i tried to run "php composer.phar update
    ":

    1) [Doctrine\Common\Annotations\AnnotationException]
    You have to enable opcache.load_comments=1 or zend_optimizerplus.load_comments=1.
    2) Script Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::clearCache handling the post-install-cmd event terminated with an exception
    3) [RuntimeException]
    An error occurred when executing the "'cache:clear --no-warmup'" command.

    i changed the composer.json as you suggest from this link https://github.com/symfony/...

    Now i have the following errors:
    1) Script Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::clearCache handling the post-update-cmd event terminated with an exception
    (didn t change from the previous time)
    2) [RuntimeException]
    An error occurred when executing the "'cache:clear --no-warmup'" command:
    PHP Fatal error: Class 'Symfony\Component\HttpKernel\Kernel' not found in
    /home/projects_linux/queries1/app/AppKernel.php on line 6
    (these is like the previous but with the AppKernel problem that i have it when i try to use database:create)

    Do you know how can i fix it?
    Thanks in advanced!!

  • 2017-05-04 Victor Bocharsky

    Hey Jian,

    The coolness here is that you don't need to add "category_id" - you operate objects. Since Category is object and you create this type of objects with Alice - you just may set it below for your FortuneCookie entity and Alice make everything for you, i.e. set the whole Category object with FortuneCookie::setCategory(). Of course, you need FortuneCookie::setCategory() setter to make it possible.

    Cheers!

  • 2017-05-04 jian su

    Ya..category works. But I have create a category_id field and setter inside my Entity. would that works? Or I better off stick with category object instead?

  • 2017-05-03 Victor Bocharsky

    Hey Jian,

    Looks like you're trying to assign category to nonexistent property `category_id`, use `category` instead:


    AppBundle\Entity\FortuneCookie:
    fortuneCookie_{1..10}:
    category: '@category_*'

    Let me know if you still have this error.

    Cheers!

  • 2017-05-01 jian su

    Hi guys:

    I try to load fixture to 3.1 since this is 2.6. but I am having troubles

    Errors message:
    [UnexpectedValueException]
    Could not determine how to assign category_id to a AppBundle\Entity\FortuneCookie object

    Here is my fixtures.yml

    AppBundle\Entity\Category:
    category_{1..10}:
    name: <name()>
    iconKey: <name()>


    AppBundle\Entity\FortuneCookie:
    fortuneCookie_{1..10}:
    category_id: '@category_*'
    fortune: <sentence()>
    createdAt: <datetimebetween(now)>
    numberPrinted: <numberbetween(1,10)>
    discontinued: <numberbetween(1,10)>

    here is my LoadFixture.php


    namespace AppBundle\DataFixtures\ORM;

    use Doctrine\Common\DataFixtures\FixtureInterface;
    use Doctrine\Common\Persistence\ObjectManager;
    use Nelmio\Alice\Fixtures;

    class LoadFixture implements FixtureInterface
    {
    public function load(ObjectManager $manager)
    {
    Fixtures::load(__DIR__.'/fixtures.yml', $manager);
    }

    }
  • 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/...

    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/sc...

    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?