orm Documentation

File: README.md

4.0.x	3.7.x	3.6.x	2.21.x	2.20.x

Doctrine ORM is an object-relational mapper for PHP 8.1+ that provides transparent persistence
for PHP objects. It sits on top of a powerful database abstraction layer (DBAL). One of its key features
is the option to write database queries in a proprietary object oriented SQL dialect called Doctrine Query Language (DQL),
inspired by Hibernate's HQL. This provides developers with a powerful alternative to SQL that maintains flexibility
without requiring unnecessary code duplication.

More resources:

File: docs/en/cookbook/advanced-field-value-conversion-using-custom-mapping-types.rst

Advanced field value conversion using custom mapping types

.. sectionauthor:: Jan Sorgalla [email protected]

When creating entities, you sometimes have the need to transform field values
before they are saved to the database. In Doctrine you can use Custom Mapping
Types to solve this (see: :ref:reference-basic-mapping-custom-mapping-types).

There are several ways to achieve this: converting the value inside the Type
class, converting the value on the database-level or a combination of both.

This article describes the third way by implementing the MySQL specific column
type Point <https://dev.mysql.com/doc/refman/8.0/en/gis-class-point.html>_.

The Point type is part of the Spatial extension <https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html>_
of MySQL and enables you to store a single location in a coordinate space by
using x and y coordinates. You can use the Point type to store a
longitude/latitude pair to represent a geographic location.

The entity

We create a simple entity with a field $point which holds a value object
Point representing the latitude and longitude of the position.

The entity class:

.. code-block:: php

text

<?php

namespace Geo\Entity;

use Geo\ValueObject\Point;

#[Entity]
class Location
{
    #[Column(type: 'point')]
    private Point $point;

    #[Column]
    private string $address;

    public function setPoint(Point $point): void
    {
        $this->point = $point;
    }

    public function getPoint(): Point
    {
        return $this->point;
    }

    public function setAddress(string $address): void
    {
        $this->address = $address;
    }

    public function getAddress(): string
    {
        return $this->address;
    }
}

We use the custom type point in the #[Column] attribute of the
$point field. We will create this custom mapping type in the next chapter.

The point class:

.. code-block:: php

text

<?php

namespace Geo\ValueObject;

class Point
{
    public function __construct(
        private float $latitude,
        private float $longitude,
    ) {
    }

    public function getLatitude(): float
    {
        return $this->latitude;
    }

    public function getLongitude(): float
    {
        return $this->longitude;
    }
}

The mapping type

Now we're going to create the point type and implement all required methods.

.. code-block:: php

text

<?php

namespace Geo\Types;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

use Geo\ValueObject\Point;

class PointType extends Type
{
    const POINT = 'point';

    public function getName()
    {
        return self::POINT;
    }

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return 'POINT';
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        list($longitude, $latitude) = sscanf($value, 'POINT(%f %f)');

        return new Point($latitude, $longitude);
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if ($value instanceof Point) {
            $value = sprintf('POINT(%F %F)', $value->getLongitude(), $value->getLatitude());
        }

        return $value;
    }

    public function convertToPHPValueSQL($sqlExpr, AbstractPlatform $platform)
    {
        return sprintf('AsText(%s)', $sqlExpr);
    }

    public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
    {
        return sprintf('PointFromText(%s)', $sqlExpr);
    }
}

We do a 2-step conversion here. In the first step, we convert the Point
object into a string representation before saving to the database (in the
convertToDatabaseValue method) and back into an object after fetching the
value from the database (in the convertToPHPValue method).

The format of the string representation format is called
Well-known text (WKT) <https://en.wikipedia.org/wiki/Well-known_text>_.
The advantage of this format is, that it is both human readable and parsable by MySQL.

Internally, MySQL stores geometry values in a binary format that is not
identical to the WKT format. So, we need to let MySQL transform the WKT
representation into its internal format.

This is where the convertToPHPValueSQL and convertToDatabaseValueSQL
methods come into play.

This methods wrap a sql expression (the WKT representation of the Point) into
MySQL functions ST_PointFromText <https://dev.mysql.com/doc/refman/8.0/en/gis-wkt-functions.html#function_st-pointfromtext>_
and ST_AsText <https://dev.mysql.com/doc/refman/8.0/en/gis-format-conversion-functions.html#function_st-astext>_
which convert WKT strings to and from the internal format of MySQL.

.. note::

text

When using DQL queries, the ``convertToPHPValueSQL`` and
``convertToDatabaseValueSQL`` methods only apply to identification variables
and path expressions in SELECT clauses. Expressions in  WHERE clauses are
**not** wrapped!

If you want to use Point values in WHERE clauses, you have to implement a
:doc:`user defined function <dql-user-defined-functions>` for
``PointFromText``.

Example usage

.. code-block:: php

text

<?php

// Bootstrapping stuff...
// $em = new \Doctrine\ORM\EntityManager($connection, $config);

// Setup custom mapping type
use Doctrine\DBAL\Types\Type;

Type::addType('point', 'Geo\Types\PointType');
$em->getConnection()->getDatabasePlatform()->registerDoctrineTypeMapping('point', 'point');

// Store a Location object
use Geo\Entity\Location;
use Geo\ValueObject\Point;

$location = new Location();

$location->setAddress('1600 Amphitheatre Parkway, Mountain View, CA');
$location->setPoint(new Point(37.4220761, -122.0845187));

$em->persist($location);
$em->flush();
$em->clear();

// Fetch the Location object
$query = $em->createQuery("SELECT l FROM Geo\Entity\Location l WHERE l.address = '1600 Amphitheatre Parkway, Mountain View, CA'");
$location = $query->getSingleResult();

/** @var Geo\ValueObject\Point */
$point = $location->getPoint();

File: docs/en/cookbook/aggregate-fields.rst

Aggregate Fields

.. sectionauthor:: Benjamin Eberlei [email protected]

You will often come across the requirement to display aggregate
values of data that can be computed by using the MIN, MAX, COUNT or
SUM SQL functions. For any ORM this is a tricky issue
traditionally. Doctrine 2 offers several ways to get access to
these values and this article will describe all of them from
different perspectives.

You will see that aggregate fields can become very explicit
features in your domain model and how this potentially complex
business rules can be easily tested.

An example model

Say you want to model a bank account and all their entries. Entries
into the account can either be of positive or negative money
values. Each account has a credit limit and the account is never
allowed to have a balance below that value.

For simplicity we live in a world were money is composed of
integers only. Also we omit the receiver/sender name, stated reason
for transfer and the execution date. These all would have to be
added on the Entry object.

Our entities look like:

.. code-block:: php

text

<?php

namespace Bank\Entities;

use Doctrine\ORM\Annotation as ORM;

/**
 * @ORM\Entity
 */
class Account
{
    /** @ORM\Id @ORM\GeneratedValue @ORM\Column(type="integer") */
    private $id;

    /** @ORM\Column(type="string", unique=true) */
    private $no;

    /** @ORM\OneToMany(targetEntity="Entry", mappedBy="account", cascade={"persist"}) */
    private $entries;

    /** @ORM\Column(type="integer") */
    private $maxCredit = 0;

    public function __construct($no, $maxCredit = 0)
    {
        $this->no = $no;
        $this->maxCredit = $maxCredit;
        $this->entries = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

/**
 * @ORM\Entity
 */
class Entry
{
    /** @ORM\Id @ORM\GeneratedValue @ORM\Column(type="integer") */
    private $id;

    /** @ORM\ManyToOne(targetEntity="Account", inversedBy="entries") */
    private $account;

    /** @ORM\Column(type="integer") */
    private $amount;

    public function __construct($account, $amount)
    {
        $this->account = $account;
        $this->amount = $amount;
        // more stuff here, from/to whom, stated reason, execution date and such
    }

    public function getAmount()
    {
        return $this->amount;
    }
}

Using DQL

The Doctrine Query Language allows you to select for aggregate
values computed from fields of your Domain Model. You can select
the current balance of your account by calling:

.. code-block:: php

text

<?php
$dql = "SELECT SUM(e.amount) AS balance FROM Bank\Entities\Entry e " .
       "WHERE e.account = ?1";
$balance = $em->createQuery($dql)
              ->setParameter(1, $myAccountId)
              ->getSingleScalarResult();

The $em variable in this (and forthcoming) example holds the
Doctrine EntityManager. We create a query for the SUM of all
amounts (negative amounts are withdraws) and retrieve them as a
single scalar result, essentially return only the first column of
the first row.

This approach is simple and powerful, however it has a serious
drawback. We have to execute a specific query for the balance
whenever we need it.

To implement a powerful domain model we would rather have access to
the balance from our Account entity during all times (even if
the Account was not persisted in the database before!).

Also an additional requirement is the max credit per Account
rule.

We cannot reliably enforce this rule in our Account entity with
the DQL retrieval of the balance. There are many different ways to
retrieve accounts. We cannot guarantee that we can execute the
aggregation query for all these use-cases, let alone that a
userland programmer checks this balance against newly added
entries.

Using your Domain Model

Account and all the Entry instances are connected through a
collection, which means we can compute this value at runtime:

.. code-block:: php

text

<?php
class Account
{
    // .. previous code
    public function getBalance()
    {
        $balance = 0;
        foreach ($this->entries as $entry) {
            $balance += $entry->getAmount();
        }
        return $balance;
    }
}

Now we can always call Account::getBalance() to access the
current account balance.

To enforce the max credit rule we have to implement the "Aggregate
Root" pattern as described in Eric Evans book on Domain Driven
Design. Described with one sentence, an aggregate root controls the
instance creation, access and manipulation of its children.

In our case we want to enforce that new entries can only added to
the Account by using a designated method. The Account is
the aggregate root of this relation. We can also enforce the
correctness of the bi-directional Account <-> Entry
relation with this method:

.. code-block:: php

text

<?php
class Account
{
    public function addEntry($amount)
    {
        $this->assertAcceptEntryAllowed($amount);

        $e = new Entry($this, $amount);
        $this->entries[] = $e;
        return $e;
    }
}

Now look at the following test-code for our entities:

.. code-block:: php

text

<?php
class AccountTest extends \PHPUnit_Framework_TestCase
{
    public function testAddEntry()
    {
        $account = new Account("123456", $maxCredit = 200);
        $this->assertEquals(0, $account->getBalance());

        $account->addEntry(500);
        $this->assertEquals(500, $account->getBalance());

        $account->addEntry(-700);
        $this->assertEquals(-200, $account->getBalance());
    }

    public function testExceedMaxLimit()
    {
        $account = new Account("123456", $maxCredit = 200);

        $this->setExpectedException("Exception");
        $account->addEntry(-1000);
    }
}

To enforce our rule we can now implement the assertion in
Account::addEntry:

.. code-block:: php

text

<?php
class Account
{
    private function assertAcceptEntryAllowed($amount)
    {
        $futureBalance = $this->getBalance() + $amount;
        $allowedMinimalBalance = ($this->maxCredit * -1);
        if ($futureBalance < $allowedMinimalBalance) {
            throw new Exception("Credit Limit exceeded, entry is not allowed!");
        }
    }
}

We haven't talked to the entity manager for persistence of our
account example before. You can call
EntityManager::persist($account) and then
EntityManager::flush() at any point to save the account to the
database. All the nested Entry objects are automatically
flushed to the database also.

.. code-block:: php

text

<?php
$account = new Account("123456", 200);
$account->addEntry(500);
$account->addEntry(-200);
$em->persist($account);
$em->flush();

The current implementation has a considerable drawback. To get the
balance, we have to initialize the complete Account::$entries
collection, possibly a very large one. This can considerably hurt
the performance of your application.

Using an Aggregate Field

To overcome the previously mentioned issue (initializing the whole
entries collection) we want to add an aggregate field called
"balance" on the Account and adjust the code in
Account::getBalance() and Account:addEntry():

.. code-block:: php

text

<?php
class Account
{
    /**
     * @ORM\Column(type="integer")
     */
    private $balance = 0;

    public function getBalance()
    {
        return $this->balance;
    }

    public function addEntry($amount)
    {
        $this->assertAcceptEntryAllowed($amount);

        $e = new Entry($this, $amount);
        $this->entries[] = $e;
        $this->balance += $amount;
        return $e;
    }
}

This is a very simple change, but all the tests still pass. Our
account entities return the correct balance. Now calling the
Account::getBalance() method will not occur the overhead of
loading all entries anymore. Adding a new Entry to the
Account::$entities will also not initialize the collection
internally.

Adding a new entry is therefore very performant and explicitly
hooked into the domain model. It will only update the account with
the current balance and insert the new entry into the database.

Tackling Race Conditions with Aggregate Fields

Whenever you denormalize your database schema race-conditions can
potentially lead to inconsistent state. See this example:

.. code-block:: php

text

<?php
// The Account $accId has a balance of 0 and a max credit limit of 200:
// request 1 account
$account1 = $em->find('Bank\Entities\Account', $accId);

// request 2 account
$account2 = $em->find('Bank\Entities\Account', $accId);

$account1->addEntry(-200);
$account2->addEntry(-200);

// now request 1 and 2 both flush the changes.

The aggregate field Account::$balance is now -200, however the
SUM over all entries amounts yields -400. A violation of our max
credit rule.

You can use both optimistic or pessimistic locking to safe-guard
your aggregate fields against this kind of race-conditions. Reading
Eric Evans DDD carefully he mentions that the "Aggregate Root"
(Account in our example) needs a locking mechanism.

Optimistic locking is as easy as adding a version column:

.. code-block:: php

text

<?php
class Account
{
    /** @ORM\Column(type="integer") @ORM\Version */
    private $version;
}

The previous example would then throw an exception in the face of
whatever request saves the entity last (and would create the
inconsistent state).

Pessimistic locking requires an additional flag set on the
EntityManager::find() call, enabling write locking directly in
the database using a FOR UPDATE.

.. code-block:: php

text

<?php
use Doctrine\DBAL\LockMode;

$account = $em->find('Bank\Entities\Account', $accId, LockMode::PESSIMISTIC_READ);

Keeping Updates and Deletes in Sync

The example shown in this article does not allow changes to the
value in Entry, which considerably simplifies the effort to
keep Account::$balance in sync. If your use-case allows fields
to be updated or related entities to be removed you have to
encapsulate this logic in your "Aggregate Root" entity and adjust
the aggregate field accordingly.

Conclusion

This article described how to obtain aggregate values using DQL or
your domain model. It showed how you can easily add an aggregate
field that offers serious performance benefits over iterating all
the related objects that make up an aggregate value. Finally I
showed how you can ensure that your aggregate fields do not get out
of sync due to race-conditions and concurrent access.

File: docs/en/cookbook/custom-mapping-types.rst

Custom Mapping Types

Doctrine allows you to create new mapping types. This can come in
handy when you're missing a specific mapping type or when you want
to replace the existing implementation of a mapping type.

In order to create a new mapping type you need to subclass
Doctrine\DBAL\Types\Type and implement/override the methods as
you wish. Here is an example skeleton of such a custom type class:

.. code-block:: php

text

<?php
namespace My\Project\Types;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

/**
 * My custom datatype.
 */
class MyType extends Type
{
    const MYTYPE = 'mytype'; // modify to match your type name

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        // return the SQL used to create your column type. To create a portable column type, use the $platform.
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        // This is executed when the value is read from the database. Make your conversions here, optionally using the $platform.
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        // This is executed when the value is written to the database. Make your conversions here, optionally using the $platform.
    }

    public function getName()
    {
        return self::MYTYPE; // modify to match your constant name
    }
}

.. note::

text

The following assumptions are applied to mapping types by the ORM:

-  The ``UnitOfWork`` never passes values to the database convert
   method that did not change in the request.
-  The ``UnitOfWork`` internally assumes that entity identifiers are
   castable to string. Hence, when using custom types that map to PHP
   objects as IDs, such objects must implement the ``__toString()`` magic
   method.

When you have implemented the type you still need to let Doctrine
know about it. This can be achieved through the
Doctrine\DBAL\Types\Type#addType($name, $className)
method. See the following example:

.. code-block:: php

text

<?php
// in bootstrapping code

// ...

use Doctrine\DBAL\Types\Type;

// ...

// Register my type
Type::addType('mytype', 'My\Project\Types\MyType');

To convert the underlying database type of your
new "mytype" directly into an instance of MyType when performing
schema operations, the type has to be registered with the database
platform as well:

.. code-block:: php

text

<?php
$conn = $em->getConnection();
$conn->getDatabasePlatform()->registerDoctrineTypeMapping('db_mytype', 'mytype');

When registering the custom types in the configuration you specify a unique
name for the mapping type and map that to the corresponding fully qualified
class name. Now the new type can be used when mapping columns:

.. code-block:: php

text

<?php
class MyPersistentClass
{
    /** @Column(type="mytype") */
    private $field;
}

File: docs/en/cookbook/decorator-pattern.rst

Persisting the Decorator Pattern

.. sectionauthor:: Chris Woodford [email protected]

This recipe will show you a simple example of how you can use
Doctrine 2 to persist an implementation of the
Decorator Pattern <https://en.wikipedia.org/wiki/Decorator_pattern>_

Component

The Component class needs to be persisted, so it's going to
be an Entity. As the top of the inheritance hierarchy, it's going
to have to define the persistent inheritance. For this example, we
will use Single Table Inheritance, but Class Table Inheritance
would work as well. In the discriminator map, we will define two
concrete subclasses, ConcreteComponent and ConcreteDecorator.

.. code-block:: php

text

<?php

namespace Test;

use Doctrine\ORM\Annotation as ORM;

/**
 * @ORM\Entity
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="discr", type="string")
 * @ORM\DiscriminatorMap({
 *   "cc" = "Test\Component\ConcreteComponent",
 *   "cd" = "Test\Decorator\ConcreteDecorator"
 * })
 */
abstract class Component
{
    /**
     * @ORM\Id @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /** @ORM\Column(type="string", nullable=true) */
    protected $name;

    /**
     * Get id
     * @return integer $id
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     * @param string $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * Get name
     * @return string $name
     */
    public function getName()
    {
        return $this->name;
    }

}

ConcreteComponent

The ConcreteComponent class is pretty simple and doesn't do much
more than extend the abstract Component class (only for the
purpose of keeping this example simple).

.. code-block:: php

text

<?php

namespace Test\Component;

use Doctrine\ORM\Annotation as ORM;
use Test\Component;

/** @ORM\Entity */
class ConcreteComponent extends Component
{}

Decorator

The Decorator class doesn't need to be persisted, but it does
need to define an association with a persisted Entity. We can
use a MappedSuperclass for this.

.. code-block:: php

text

<?php

namespace Test;

use Doctrine\ORM\Annotation as ORM;

/** @ORM\MappedSuperclass */
abstract class Decorator extends Component
{

    /**
     * @ORM\OneToOne(targetEntity="Test\Component", cascade={"all"})
     * @ORM\JoinColumn(name="decorates", referencedColumnName="id")
     */
    protected $decorates;

    /**
     * initialize the decorator
     * @param Component $c
     */
    public function __construct(Component $c)
    {
        $this->setDecorates($c);
    }

    /**
     * (non-PHPdoc)
     * @see Test.Component::getName()
     */
    public function getName()
    {
	    return 'Decorated ' . $this->getDecorates()->getName();
    }

    /**
     * the component being decorated
     * @return Component
     */
    protected function getDecorates()
    {
	    return $this->decorates;
    }

    /**
     * sets the component being decorated
     * @param Component $c
     */
    protected function setDecorates(Component $c)
    {
	    $this->decorates = $c;
    }

}

All operations on the Decorator (i.e. persist, remove, etc) will
cascade from the Decorator to the Component. This means that
when we persist a Decorator, Doctrine will take care of
persisting the chain of decorated objects for us. A Decorator can
be treated exactly as a Component when it comes time to
persisting it.

The Decorator's constructor accepts an instance of a
Component, as defined by the Decorator pattern. The
setDecorates/getDecorates methods have been defined as protected to
hide the fact that a Decorator is decorating a Component and
keeps the Component interface and the Decorator interface
identical.

To illustrate the intended result of the Decorator pattern, the
getName() method has been overridden to append a string to the
Component's getName() method.

ConcreteDecorator

The final class required to complete a simple implementation of the
Decorator pattern is the ConcreteDecorator. In order to further
illustrate how the Decorator can alter data as it moves through
the chain of decoration, a new field, "special", has been added to
this class. The getName() has been overridden and appends the value
of the getSpecial() method to its return value.

.. code-block:: php

text

<?php

namespace Test\Decorator;

use Doctrine\ORM\Annotation as ORM;
use Test\Decorator;

/** @ORM\Entity */
class ConcreteDecorator extends Decorator
{

    /** @ORM\Column(type="string", nullable=true) */
    protected $special;

    /**
     * Set special
     * @param string $special
     */
    public function setSpecial($special)
    {
        $this->special = $special;
    }

    /**
     * Get special
     * @return string $special
     */
    public function getSpecial()
    {
        return $this->special;
    }

    /**
     * (non-PHPdoc)
     * @see Test.Component::getName()
     */
    public function getName()
    {
        return '[' . $this->getSpecial()
            . '] ' . parent::getName();
    }

}

Examples

Here is an example of how to persist and retrieve your decorated
objects

.. code-block:: php

text

<?php

use Test\Component\ConcreteComponent,
    Test\Decorator\ConcreteDecorator;

// assumes Doctrine 2 is configured and an instance of
// an EntityManager is available as $em

// create a new concrete component
$c = new ConcreteComponent();
$c->setName('Test Component 1');
$em->persist($c); // assigned unique ID = 1

// create a new concrete decorator
$c = new ConcreteComponent();
$c->setName('Test Component 2');

$d = new ConcreteDecorator($c);
$d->setSpecial('Really');
$em->persist($d);
// assigns c as unique ID = 2, and d as unique ID = 3

$em->flush();

$c = $em->find('Test\Component', 1);
$d = $em->find('Test\Component', 3);

echo get_class($c);
// prints: Test\Component\ConcreteComponent

echo $c->getName();
// prints: Test Component 1

echo get_class($d)
// prints: Test\Component\ConcreteDecorator

echo $d->getName();
// prints: [Really] Decorated Test Component 2

File: docs/en/cookbook/dql-custom-walkers.rst

Extending DQL in Doctrine 2: Custom AST Walkers

.. sectionauthor:: Benjamin Eberlei [email protected]

The Doctrine Query Language (DQL) is a proprietary sql-dialect that
substitutes tables and columns for Entity names and their fields.
Using DQL you write a query against the database using your
entities. With the help of the metadata you can write very concise,
compact and powerful queries that are then translated into SQL by
the Doctrine ORM.

In Doctrine 1 the DQL language was not implemented using a real
parser. This made modifications of the DQL by the user impossible.
Doctrine 2 in contrast has a real parser for the DQL language,
which transforms the DQL statement into an
Abstract Syntax Tree <https://en.wikipedia.org/wiki/Abstract_syntax_tree>_
and generates the appropriate SQL statement for it. Since this
process is deterministic Doctrine heavily caches the SQL that is
generated from any given DQL query, which reduces the performance
overhead of the parsing process to zero.

You can modify the Abstract syntax tree by hooking into DQL parsing
process by adding a Custom Tree Walker. A walker is an interface
that walks each node of the Abstract syntax tree, thereby
generating the SQL statement.

There are two types of custom tree walkers that you can hook into
the DQL parser:

An output walker. This one actually generates the SQL, and there
is only ever one of them. We implemented the default SqlWalker
implementation for it.
A tree walker. There can be many tree walkers, they cannot
generate the sql, however they can modify the AST before its
rendered to sql.

Now this is all awfully technical, so let me come to some use-cases
fast to keep you motivated. Using walker implementation you can for
example:

Modify the AST to generate a Count Query to be used with a
paginator for any given DQL query.
Modify the Output Walker to generate vendor-specific SQL
(instead of ANSI).
Modify the AST to add additional where clauses for specific
entities (example ACL, country-specific content...)
Modify the Output walker to pretty print the SQL for debugging
purposes.

In this cookbook-entry I will show examples on the first two
points. There are probably much more use-cases.

Generic count query for pagination

Say you have a blog and posts all with one category and one author.
A query for the front-page or any archive page might look something
like:

.. code-block:: sql

text

SELECT p, c, a FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...

Now in this query the blog post is the root entity, meaning its the
one that is hydrated directly from the query and returned as an
array of blog posts. In contrast the comment and author are loaded
for deeper use in the object tree.

A pagination for this query would want to approximate the number of
posts that match the WHERE clause of this query to be able to
predict the number of pages to show to the user. A draft of the DQL
query for pagination would look like:

.. code-block:: sql

text

SELECT count(DISTINCT p.id) FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...

Now you could go and write each of these queries by hand, or you
can use a tree walker to modify the AST for you. Lets see how the
API would look for this use-case:

.. code-block:: php

text

<?php
$pageNum = 1;
$query = $em->createQuery($dql);
$query->setFirstResult( ($pageNum-1) * 20)->setMaxResults(20);

$totalResults = Paginate::count($query);
$results = $query->getResult();

The Paginate::count(Query $query) looks like:

.. code-block:: php

text

<?php
class Paginate
{
    static public function count(Query $query)
    {
        /** @var Query $countQuery */
        $countQuery = clone $query;

        $countQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, array('DoctrineExtensions\Paginate\CountSqlWalker'));
        $countQuery->setFirstResult(null)->setMaxResults(null);

        return $countQuery->getSingleScalarResult();
    }
}

It clones the query, resets the limit clause first and max results
and registers the CountSqlWalker custom tree walker which
will modify the AST to execute a count query. The walkers
implementation is:

.. code-block:: php

text

<?php
class CountSqlWalker extends TreeWalkerAdapter
{
    /**
     * Walks down a SelectStatement AST node, thereby generating the appropriate SQL.
     *
     * @return string The SQL.
     */
    public function walkSelectStatement(SelectStatement $AST)
    {
        $parent = null;
        $parentName = null;
        foreach ($this->getQueryComponents() as $dqlAlias => $qComp) {
            if ($qComp['parent'] === null && $qComp['nestingLevel'] == 0) {
                $parent = $qComp;
                $parentName = $dqlAlias;
                break;
            }
        }

        $pathExpression = new PathExpression(
            PathExpression::TYPE_STATE_FIELD | PathExpression::TYPE_SINGLE_VALUED_ASSOCIATION, $parentName,
            $parent['metadata']->getSingleIdentifierFieldName()
        );
        $pathExpression->type = PathExpression::TYPE_STATE_FIELD;

        $AST->selectClause->selectExpressions = array(
            new SelectExpression(
                new AggregateExpression('count', $pathExpression, true), null
            )
        );
    }
}

This will delete any given select expressions and replace them with
a distinct count query for the root entities primary key. This will
only work if your entity has only one identifier field (composite
keys won't work).

Modify the Output Walker to generate Vendor specific SQL

Most RMDBS have vendor-specific features for optimizing select
query execution plans. You can write your own output walker to
introduce certain keywords using the Query Hint API. A query hint
can be set via Query::setHint($name, $value) as shown in the
previous example with the HINT_CUSTOM_TREE_WALKERS query hint.

We will implement a custom Output Walker that allows to specify the
SQL_NO_CACHE query hint.

.. code-block:: php

text

<?php
$dql = "SELECT p, c, a FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...";
$query = $m->createQuery($dql);
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'DoctrineExtensions\Query\MysqlWalker');
$query->setHint("mysqlWalker.sqlNoCache", true);
$results = $query->getResult();

Our MysqlWalker will extend the default SqlWalker. We will
modify the generation of the SELECT clause, adding the
SQL_NO_CACHE on those queries that need it:

.. code-block:: php

text

<?php
class MysqlWalker extends SqlWalker
{
     /**
     * Walks down a SelectClause AST node, thereby generating the appropriate SQL.
     *
     * @param $selectClause
     * @return string The SQL.
     */
    public function walkSelectClause($selectClause)
    {
        $sql = parent::walkSelectClause($selectClause);

        if ($this->getQuery()->getHint('mysqlWalker.sqlNoCache') === true) {
            if ($selectClause->isDistinct) {
                $sql = str_replace('SELECT DISTINCT', 'SELECT DISTINCT SQL_NO_CACHE', $sql);
            } else {
                $sql = str_replace('SELECT', 'SELECT SQL_NO_CACHE', $sql);
            }
        }

        return $sql;
    }
}

Writing extensions to the Output Walker requires a very deep
understanding of the DQL Parser and Walkers, but may offer your
huge benefits with using vendor specific features. This would still
allow you write DQL queries instead of NativeQueries to make use of
vendor specific features.

File: docs/en/cookbook/dql-user-defined-functions.rst

DQL User Defined Functions

.. sectionauthor:: Benjamin Eberlei [email protected]

By default DQL supports a limited subset of all the vendor-specific
SQL functions common between all the vendors. However in many cases
once you have decided on a specific database vendor, you will never
change it during the life of your project. This decision for a
specific vendor potentially allows you to make use of powerful SQL
features that are unique to the vendor.

It is worth to mention that Doctrine ORM also allows you to handwrite
your SQL instead of extending the DQL parser. Extending DQL is sort of an
advanced extension point. You can map arbitrary SQL to your objects
and gain access to vendor specific functionalities using the
EntityManager#createNativeQuery() API as described in
the :doc:Native Query <../reference/native-sql> chapter.

The DQL Parser has hooks to register functions that can then be
used in your DQL queries and transformed into SQL, allowing to
extend Doctrines Query capabilities to the vendors strength. This
post explains the User-Defined Functions API (UDF) of the Dql
Parser and shows some examples to give you some hints how you would
extend DQL.

There are three types of functions in DQL, those that return a
numerical value, those that return a string and those that return a
Date. Your custom method has to be registered as either one of
those. The return type information is used by the DQL parser to
check possible syntax errors during the parsing process, for
example using a string function return value in a math expression.

Registering your own DQL functions

You can register your functions adding them to the ORM
configuration:

.. code-block:: php

text

<?php
$config = new \Doctrine\ORM\Configuration();
$config->addCustomStringFunction($name, $class);
$config->addCustomNumericFunction($name, $class);
$config->addCustomDatetimeFunction($name, $class);

$em = new EntityManager($connection, $config);

The $name is the name the function will be referred to in the
DQL query. $class is a string of a class-name which has to
extend Doctrine\ORM\Query\Node\FunctionNode. This is a class
that offers all the necessary API and methods to implement a UDF.

Instead of providing the function class name, you can also provide
a callable that returns the function object:

.. code-block:: php

text

<?php
$config = new \Doctrine\ORM\Configuration();
$config->addCustomStringFunction($name, function () {
    return new MyCustomFunction();
});

In this post we will implement some MySql specific Date calculation
methods, which are quite handy in my opinion:

Date Diff

Mysql's DateDiff function <https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff>_
takes two dates as argument and calculates the difference in days
with date1-date2.

The DQL parser is a top-down recursive descent parser to generate
the Abstract-Syntax Tree (AST) and uses a TreeWalker approach to
generate the appropriate SQL from the AST. This makes reading the
Parser/TreeWalker code manageable in a finite amount of time.

The FunctionNode class I referred to earlier requires you to
implement two methods, one for the parsing process (obviously)
called parse and one for the TreeWalker process called
getSql(). I show you the code for the DateDiff method and
discuss it step by step:

.. code-block:: php

text

<?php
/**
 * DateDiffFunction ::= "DATEDIFF" "(" ArithmeticPrimary "," ArithmeticPrimary ")"
 */
class DateDiff extends FunctionNode
{
    // (1)
    public $firstDateExpression = null;
    public $secondDateExpression = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(TokenType::T_IDENTIFIER); // (2)
        $parser->match(TokenType::T_OPEN_PARENTHESIS); // (3)
        $this->firstDateExpression = $parser->ArithmeticPrimary(); // (4)
        $parser->match(TokenType::T_COMMA); // (5)
        $this->secondDateExpression = $parser->ArithmeticPrimary(); // (6)
        $parser->match(TokenType::T_CLOSE_PARENTHESIS); // (3)
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'DATEDIFF(' .
            $this->firstDateExpression->dispatch($sqlWalker) . ', ' .
            $this->secondDateExpression->dispatch($sqlWalker) .
        ')'; // (7)
    }
}

The Parsing process of the DATEDIFF function is going to find two
expressions the date1 and the date2 values, whose AST Node
representations will be saved in the variables of the DateDiff
FunctionNode instance at (1).

The parse() method has to cut the function call "DATEDIFF" and its
argument into pieces. Since the parser detects the function using a
lookahead the T_IDENTIFIER of the function name has to be taken
from the stack (2), followed by a detection of the arguments in
(4)-(6). The opening and closing parenthesis have to be detected
also. This happens during the Parsing process and leads to the
generation of a DateDiff FunctionNode somewhere in the AST of the
dql statement.

The ArithmeticPrimary method call is the most common
denominator of valid EBNF tokens taken from the :ref:DQL EBNF grammar <dql_ebnf_grammar>
that matches our requirements for valid input into the DateDiff Dql
function. Picking the right tokens for your methods is a tricky
business, but the EBNF grammar is pretty helpful finding it, as is
looking at the Parser source code.

Now in the TreeWalker process we have to pick up this node and
generate SQL from it, which apparently is quite easy looking at the
code in (7). Since we don't know which type of AST Node the first
and second Date expression are we are just dispatching them back to
the SQL Walker to generate SQL from and then wrap our DATEDIFF
function call around this output.

Now registering this DateDiff FunctionNode with the ORM using:

.. code-block:: php

text

<?php
$config = new \Doctrine\ORM\Configuration();
$config->addCustomStringFunction('DATEDIFF', 'DoctrineExtensions\Query\MySql\DateDiff');

We can do fancy stuff like:

.. code-block:: sql

text

SELECT p FROM DoctrineExtensions\Query\BlogPost p WHERE DATEDIFF(CURRENT_TIME(), p.created) < 7

Date Add

Often useful it the ability to do some simple date calculations in
your DQL query using
MySql's DATE_ADD function <https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add>_.

I'll skip the blah and show the code for this function:

.. code-block:: php

text

<?php
/**
 * DateAddFunction ::=
 *     "DATE_ADD" "(" ArithmeticPrimary ", INTERVAL" ArithmeticPrimary Identifier ")"
 */
class DateAdd extends FunctionNode
{
    public $firstDateExpression = null;
    public $intervalExpression = null;
    public $unit = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(TokenType::T_IDENTIFIER);
        $parser->match(TokenType::T_OPEN_PARENTHESIS);

        $this->firstDateExpression = $parser->ArithmeticPrimary();

        $parser->match(TokenType::T_COMMA);
        $parser->match(TokenType::T_IDENTIFIER);

        $this->intervalExpression = $parser->ArithmeticPrimary();

        $parser->match(TokenType::T_IDENTIFIER);

        /** @var Lexer $lexer */
        $lexer = $parser->getLexer();
        $this->unit = $lexer->token['value'];

        $parser->match(TokenType::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'DATE_ADD(' .
            $this->firstDateExpression->dispatch($sqlWalker) . ', INTERVAL ' .
            $this->intervalExpression->dispatch($sqlWalker) . ' ' . $this->unit .
        ')';
    }
}

The only difference compared to the DATEDIFF here is, we
additionally need the Lexer to access the value of the
T_IDENTIFIER token for the Date Interval unit, for example the
MONTH in:

.. code-block:: sql

text

SELECT p FROM DoctrineExtensions\Query\BlogPost p WHERE DATE_ADD(CURRENT_TIME(), INTERVAL 4 MONTH) > p.created

The above method now only supports the specification using
INTERVAL, to also allow a real date in DATE_ADD we need to add
some decision logic to the parsing process (makes up for a nice
exercise).

Now as you see, the Parsing process doesn't catch all the possible
SQL errors, here we don't match for all the valid inputs for the
interval unit. However where necessary we rely on the database
vendors SQL parser to show us further errors in the parsing
process, for example if the Unit would not be one of the supported
values by MySql.

Typed functions

By default, result of custom functions is fetched as-is from the database driver.
If you want to be sure that the type is always the same, then your custom function needs to
implement Doctrine\ORM\Query\AST\TypedExpression. Then, the result is wired
through Doctrine\DBAL\Types\Type::convertToPhpValue() of the Type returned in getReturnType().

.. code-block:: php

text

<?php

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\TypedExpression;

class DateDiff extends FunctionNode implements TypedExpression
{
    // ...

    public function getReturnType(): Type
    {
        return Type::getType(Types::INTEGER);
    }
}

Conclusion

Now that you all know how you can implement vendor specific SQL
functionalities in DQL, we would be excited to see user extensions
that add vendor specific function packages, for example more math
functions, XML + GIS Support, Hashing functions and so on.

For ORM we will come with the current set of functions, however for
a future version we will re-evaluate if we can abstract even more
vendor sql functions and extend the DQL languages scope.

Code for this Extension to DQL and other Doctrine Extensions can be
found
in the GitHub DoctrineExtensions repository <https://github.com/beberlei/DoctrineExtensions>_.

File: docs/en/cookbook/entities-in-session.rst

Entities in the Session

There are several use-cases to save entities in the session, for example:

User object
Multi-step forms

To achieve this with Doctrine you have to pay attention to some details to get
this working.

Merging entity into an EntityManager

In Doctrine, an entity objects has to be "managed" by an EntityManager to be
updated. Entities saved into the session are not managed in the next request
anymore. This means that you have to register these entities with an
EntityManager again if you want to change them or use them as part of
references between other entities.

It is a good idea to avoid storing entities in serialized formats such as
$_SESSION: instead, store the entity identifiers or raw data.

For a representative User object the code to get turn an instance from
the session into a managed Doctrine object looks like this:

.. code-block:: php

text

<?php
require_once 'bootstrap.php';
$em = GetEntityManager(); // creates an EntityManager

session_start();
if (isset($_SESSION['user'])) {
    $user = $em->find(User::class, $_SESSION['user']);

    if (! $user instanceof User) {
        // user not found in the database
        $_SESSION['user'] = null;
    }
}

Serializing entities into the session

Serializing entities in the session means serializing also all associated
entities and collections. While this might look like a quick solution in
simple applications, you will encounter problems due to the fact that the
data in the session is stale.

In order to prevent working with stale data, try saving only minimal
information about your entities in your session, without storing entire
entity objects. Should you need the full information of an object, so it
is suggested to re-query the database, which is usually the most
authoritative source of information in typical PHP applications.

File: docs/en/cookbook/implementing-arrayaccess-for-domain-objects.rst

Implementing ArrayAccess for Domain Objects

.. sectionauthor:: Roman Borschel ([email protected])

This recipe will show you how to implement ArrayAccess for your
domain objects in order to allow more uniform access, for example
in templates. In these examples we will implement ArrayAccess on a
Layer Supertype <https://martinfowler.com/eaaCatalog/layerSupertype.html>_
for all our domain objects.

Option 1

In this implementation we will make use of PHPs highly dynamic
nature to dynamically access properties of a subtype in a supertype
at runtime. Note that this implementation has 2 main caveats:

It will not work with private fields
It will not go through any getters/setters

.. code-block:: php

text

<?php
abstract class DomainObject implements ArrayAccess
{
    public function offsetExists($offset) {
        return isset($this->$offset);
    }

    public function offsetSet($offset, $value) {
        $this->$offset = $value;
    }

    public function offsetGet($offset) {
        return $this->$offset;
    }

    public function offsetUnset($offset) {
        $this->$offset = null;
    }
}

Option 2

In this implementation we will dynamically invoke getters/setters.
Again we use PHPs dynamic nature to invoke methods on a subtype
from a supertype at runtime. This implementation has the following
caveats:

It relies on a naming convention
The semantics of offsetExists can differ
offsetUnset will not work with typehinted setters

.. code-block:: php

text

<?php
abstract class DomainObject implements ArrayAccess
{
    public function offsetExists($offset) {
        // In this example we say that exists means it is not null
        $value = $this->{"get$offset"}();
        return $value !== null;
    }

    public function offsetSet($offset, $value) {
        $this->{"set$offset"}($value);
    }

    public function offsetGet($offset) {
        return $this->{"get$offset"}();
    }

    public function offsetUnset($offset) {
        $this->{"set$offset"}(null);
    }
}

Read-only

You can slightly tweak option 1 or option 2 in order to make array
access read-only. This will also circumvent some of the caveats of
each option. Simply make offsetSet and offsetUnset throw an
exception (i.e. BadMethodCallException).

.. code-block:: php

text

<?php
abstract class DomainObject implements ArrayAccess
{
    public function offsetExists($offset) {
        // option 1 or option 2
    }

    public function offsetSet($offset, $value) {
        throw new BadMethodCallException("Array access of class " . get_class($this) . " is read-only!");
    }

    public function offsetGet($offset) {
        // option 1 or option 2
    }

    public function offsetUnset($offset) {
        throw new BadMethodCallException("Array access of class " . get_class($this) . " is read-only!");
    }
}

File: docs/en/cookbook/implementing-the-notify-changetracking-policy.rst

Implementing the Notify ChangeTracking Policy

.. sectionauthor:: Roman Borschel ([email protected])

The NOTIFY change-tracking policy is the most effective
change-tracking policy provided by Doctrine but it requires some
boilerplate code. This recipe will show you how this boilerplate
code should look like. We will implement it on a
Layer Supertype <https://martinfowler.com/eaaCatalog/layerSupertype.html>_
for all our domain objects.

Implementing NotifyPropertyChanged

The NOTIFY policy is based on the assumption that the entities
notify interested listeners of changes to their properties. For
that purpose, a class that wants to use this policy needs to
implement the NotifyPropertyChanged interface from the
Doctrine\Common namespace.

.. code-block:: php

text

<?php
use Doctrine\Common\NotifyPropertyChanged;
use Doctrine\Common\PropertyChangedListener;

abstract class DomainObject implements NotifyPropertyChanged
{
    private $listeners = array();

    public function addPropertyChangedListener(PropertyChangedListener $listener) {
        $this->listeners[] = $listener;
    }

    /** Notifies listeners of a change. */
    protected function onPropertyChanged($propName, $oldValue, $newValue) {
        if ($this->listeners) {
            foreach ($this->listeners as $listener) {
                $listener->propertyChanged($this, $propName, $oldValue, $newValue);
            }
        }
    }
}

Then, in each property setter of concrete, derived domain classes,
you need to invoke onPropertyChanged as follows to notify
listeners:

.. code-block:: php

text

<?php
// Mapping not shown, either in annotations or xml as usual
class MyEntity extends DomainObject
{
    private $data;
    // ... other fields as usual

    public function setData($data) {
        if ($data != $this->data) { // check: is it actually modified?
            $this->onPropertyChanged('data', $this->data, $data);
            $this->data = $data;
        }
    }
}

The check whether the new value is different from the old one is
not mandatory but recommended. That way you can avoid unnecessary
updates and also have full control over when you consider a
property changed.

File: README.md Copy Chapter

More resources: Copy Chapter

File: docs/en/cookbook/advanced-field-value-conversion-using-custom-mapping-types.rst Copy Chapter

Advanced field value conversion using custom mapping types

The entity Copy Chapter

The mapping type Copy Chapter

Example usage Copy Chapter

File: docs/en/cookbook/aggregate-fields.rst Copy Chapter

Aggregate Fields

An example model Copy Chapter

Using DQL Copy Chapter

Using your Domain Model Copy Chapter

Using an Aggregate Field Copy Chapter

Tackling Race Conditions with Aggregate Fields Copy Chapter

Keeping Updates and Deletes in Sync Copy Chapter

Conclusion Copy Chapter

File: docs/en/cookbook/custom-mapping-types.rst Copy Chapter

Custom Mapping Types

File: docs/en/cookbook/decorator-pattern.rst Copy Chapter

Persisting the Decorator Pattern

Component Copy Chapter

ConcreteComponent Copy Chapter

Decorator Copy Chapter

ConcreteDecorator Copy Chapter

Examples Copy Chapter

File: docs/en/cookbook/dql-custom-walkers.rst Copy Chapter

Extending DQL in Doctrine 2: Custom AST Walkers

Generic count query for pagination Copy Chapter

Modify the Output Walker to generate Vendor specific SQL Copy Chapter

File: docs/en/cookbook/dql-user-defined-functions.rst Copy Chapter

DQL User Defined Functions

Registering your own DQL functions Copy Chapter

Date Diff Copy Chapter

Date Add Copy Chapter

Typed functions Copy Chapter

Conclusion Copy Chapter

File: docs/en/cookbook/entities-in-session.rst Copy Chapter

Entities in the Session

Merging entity into an EntityManager Copy Chapter

Serializing entities into the session Copy Chapter

File: docs/en/cookbook/implementing-arrayaccess-for-domain-objects.rst Copy Chapter

Implementing ArrayAccess for Domain Objects

Option 1 Copy Chapter

Option 2 Copy Chapter

Read-only Copy Chapter

File: docs/en/cookbook/implementing-the-notify-changetracking-policy.rst Copy Chapter

Implementing the Notify ChangeTracking Policy

Implementing NotifyPropertyChanged Copy Chapter

Explore Related Technologies & Topics

File: README.md

More resources:

File: docs/en/cookbook/advanced-field-value-conversion-using-custom-mapping-types.rst

The entity

The mapping type

Example usage

File: docs/en/cookbook/aggregate-fields.rst

An example model

Using DQL

Using your Domain Model

Using an Aggregate Field

Tackling Race Conditions with Aggregate Fields

Keeping Updates and Deletes in Sync

Conclusion

File: docs/en/cookbook/custom-mapping-types.rst

File: docs/en/cookbook/decorator-pattern.rst

Component

ConcreteComponent

Decorator

ConcreteDecorator

Examples

File: docs/en/cookbook/dql-custom-walkers.rst

Generic count query for pagination

Modify the Output Walker to generate Vendor specific SQL

File: docs/en/cookbook/dql-user-defined-functions.rst

Registering your own DQL functions

Date Diff

Date Add

Typed functions

Conclusion

File: docs/en/cookbook/entities-in-session.rst

Merging entity into an EntityManager

Serializing entities into the session

File: docs/en/cookbook/implementing-arrayaccess-for-domain-objects.rst

Option 1

Option 2

Read-only

File: docs/en/cookbook/implementing-the-notify-changetracking-policy.rst

Implementing NotifyPropertyChanged