Databases and the Doctrine ORM

Configuring the Database

Before you really begin, you'll need to configure your database connection information. By convention, this information is usually configured in an app/config/parameters.yml file:

1
2
3
4
5
6
7
8

# app/config/parameters.yml
parameters:
    database_host:     localhost
    database_name:     test_project
    database_user:     root
    database_password: password

# ...

1
2
3
4
5
6
7
8

# app/config/config.yml
doctrine:
    dbal:
        driver:   pdo_mysql
        host:     '%database_host%'
        dbname:   '%database_name%'
        user:     '%database_user%'
        password: '%database_password%'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<!-- app/config/config.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<container xmlns="http://symfony.com/schema/dic/services"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:doctrine="http://symfony.com/schema/dic/doctrine"
    xsi:schemaLocation="http://symfony.com/schema/dic/services
        http://symfony.com/schema/dic/services/services-1.0.xsd
        http://symfony.com/schema/dic/doctrine
        http://symfony.com/schema/dic/doctrine/doctrine-1.0.xsd">

    <doctrine:config>
        <doctrine:dbal
            driver="pdo_mysql"
            host="%database_host%"
            dbname="%database_name%"
            user="%database_user%"
            password="%database_password%" />
    </doctrine:config>
</container>

1
2
3
4
5
6
7
8
9
10

// app/config/config.php
$container->loadFromExtension('doctrine', array(
    'dbal' => array(
        'driver'   => 'pdo_mysql',
        'host'     => '%database_host%',
        'dbname'   => '%database_name%',
        'user'     => '%database_user%',
        'password' => '%database_password%',
    ),
));

Now that Doctrine can connect to your database, the following command can automatically generate an empty test_project database for you:

1

$ php app/console doctrine:database:create

1
2

$ php app/console doctrine:database:drop --force
$ php app/console doctrine:database:create

1
2
3
4

[mysqld]
# Version 5.5.3 introduced "utf8mb4", which is recommended
collation-server     = utf8mb4_unicode_ci # Replaces utf8_unicode_ci
character-set-server = utf8mb4            # Replaces utf8

1
2
3
4
5
6

# app/config/config.yml
doctrine:
    dbal:
        driver: pdo_sqlite
        path: '%kernel.root_dir%/sqlite.db'
        charset: UTF8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<!-- app/config/config.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<container xmlns="http://symfony.com/schema/dic/services"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:doctrine="http://symfony.com/schema/dic/doctrine"
    xsi:schemaLocation="http://symfony.com/schema/dic/services
        http://symfony.com/schema/dic/services/services-1.0.xsd
        http://symfony.com/schema/dic/doctrine
        http://symfony.com/schema/dic/doctrine/doctrine-1.0.xsd">

    <doctrine:config>
        <doctrine:dbal
            driver="pdo_sqlite"
            path="%kernel.root_dir%/sqlite.db"
            charset="UTF-8" />
    </doctrine:config>
</container>

1
2
3
4
5
6
7
8

// app/config/config.php
$container->loadFromExtension('doctrine', array(
    'dbal' => array(
        'driver'  => 'pdo_sqlite',
        'path'    => '%kernel.root_dir%/sqlite.db',
        'charset' => 'UTF-8',
    ),
));

Creating an Entity Class

Suppose you're building an application where products need to be displayed. Without even thinking about Doctrine or databases, you already know that you need a Product object to represent those products. Create this class inside the Entity directory of your AppBundle:

1
2
3
4
5
6
7
8
9

// src/AppBundle/Entity/Product.php
namespace AppBundle\Entity;

class Product
{
    private $name;
    private $price;
    private $description;
}

The class - often called an "entity", meaning a basic class that holds data - is simple and helps fulfill the business requirement of needing products in your application. This class can't be persisted to a database yet - it's just a simple PHP class.

1

$ php app/console doctrine:generate:entity

Add Mapping Information

Doctrine allows you to work with databases in a much more interesting way than just fetching rows of scalar data into an array. Instead, Doctrine allows you to fetch entire objects out of the database, and to persist entire objects to the database. For Doctrine to be able to do this, you must map your database tables to specific PHP classes, and the columns on those tables must be mapped to specific properties on their corresponding PHP classes.

You'll provide this mapping information in the form of "metadata", a collection of rules that tells Doctrine exactly how the Product class and its properties should be mapped to a specific database table. This metadata can be specified in a number of different formats, including YAML, XML or directly inside the Product class via DocBlock annotations:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

// src/AppBundle/Entity/Product.php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="product")
 */
class Product
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private $name;

    /**
     * @ORM\Column(type="decimal", scale=2)
     */
    private $price;

    /**
     * @ORM\Column(type="text")
     */
    private $description;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# src/AppBundle/Resources/config/doctrine/Product.orm.yml
AppBundle\Entity\Product:
    type: entity
    table: product
    id:
        id:
            type: integer
            generator: { strategy: AUTO }
    fields:
        name:
            type: string
            length: 100
        price:
            type: decimal
            scale: 2
        description:
            type: text

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<!-- src/AppBundle/Resources/config/doctrine/Product.orm.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping
        http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd">

    <entity name="AppBundle\Entity\Product" table="product">
        <id name="id" type="integer">
            <generator strategy="AUTO" />
        </id>
        <field name="name" type="string" length="100" />
        <field name="price" type="decimal" scale="2" />
        <field name="description" type="text" />
    </entity>
</doctrine-mapping>

Doctrine allows you to choose from a wide variety of different field types, each with their own options. For information on the available field types, see the Databases and the Doctrine ORM section.

1
2
3
4
5

/**
 * @IgnoreAnnotation("fn")
 */
class Product
// ...

1

$ php app/console doctrine:schema:validate

Generating Getters and Setters

Even though Doctrine now knows how to persist a Product object to the database, the class itself isn't really useful yet. Since Product is just a regular PHP class with private properties, you need to create public getter and setter methods (e.g. getName(), setName($name)) in order to access its properties in the rest of your application's code. Add these methods manually or with your own IDE.

Creating the Database Tables/Schema

You now have a usable Product class with mapping information so that Doctrine knows exactly how to persist it. Of course, you don't yet have the corresponding product table in your database. Fortunately, Doctrine can automatically create all the database tables needed for every known entity in your application. To do this, run:

1

$ php app/console doctrine:schema:update --force

Your database now has a fully-functional product table with columns that match the metadata you've specified.

Persisting Objects to the Database

Now that you have mapped the Product entity to its corresponding product table, you're ready to persist Product objects to the database. From inside a controller, this is pretty easy. Add the following method to the DefaultController of the bundle:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

// src/AppBundle/Controller/DefaultController.php

// ...
use AppBundle\Entity\Product;
use Symfony\Component\HttpFoundation\Response;

// ...
public function createAction()
{
    $product = new Product();
    $product->setName('Keyboard');
    $product->setPrice(19.99);
    $product->setDescription('Ergonomic and stylish!');

    $entityManager = $this->getDoctrine()->getManager();

    // tells Doctrine you want to (eventually) save the Product (no queries yet)
    $entityManager->persist($product);

    // actually executes the queries (i.e. the INSERT query)
    $entityManager->flush();

    return new Response('Saved new product with id '.$product->getId());
}

Take a look at the previous example in more detail:

• lines 10-13 In this section, you instantiate and work with the $product object like any other normal PHP object.
• line 15 This line fetches Doctrine's entity manager object, which is responsible for the process of persisting objects to, and fetching objects from, the database.
• line 18 The persist($product) call tells Doctrine to "manage" the $product object. This does not cause a query to be made to the database.
• line 21 When the flush() method is called, Doctrine looks through all of the objects that it's managing to see if they need to be persisted to the database. In this example, the $product object's data doesn't exist in the database, so the entity manager executes an INSERT query, creating a new row in the product table.

Whether creating or updating objects, the workflow is always the same. In the next section, you'll see how Doctrine is smart enough to automatically issue an UPDATE query if the entity already exists in the database.

Fetching Objects from the Database

Fetching an object back out of the database is even easier. For example, suppose you've configured a route to display a specific Product based on its id value:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

public function showAction($productId)
{
    $product = $this->getDoctrine()
        ->getRepository(Product::class)
        ->find($productId);

    if (!$product) {
        throw $this->createNotFoundException(
            'No product found for id '.$productId
        );
    }

    // ... do something, like pass the $product object into a template
}

When you query for a particular type of object, you always use what's known as its "repository". You can think of a repository as a PHP class whose only job is to help you fetch entities of a certain class. You can access the repository object for an entity class via:

1
2

$repository = $this->getDoctrine()
    ->getRepository(Product::class);

Once you have a repository object, you can access all sorts of helpful methods:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

$repository = $this->getDoctrine()->getRepository(Product::class);

// looks for a single product by its primary key (usually "id")
$product = $repository->find($productId);

// dynamic method names to find a single product based on a column value
$product = $repository->findOneById($productId);
$product = $repository->findOneByName('Keyboard');

// dynamic method names to find a group of products based on a column value
$products = $repository->findByPrice(19.99);

// finds *all* products
$products = $repository->findAll();

You can also take advantage of the useful findBy() and findOneBy() methods to easily fetch objects based on multiple conditions:

1
2
3
4
5
6
7
8
9
10
11
12

$repository = $this->getDoctrine()->getRepository(Product::class);

// looks for a single product matching the given name and price
$product = $repository->findOneBy(
    array('name' => 'Keyboard', 'price' => 19.99)
);

// looks for multiple products matching the given name, ordered by price
$products = $repository->findBy(
    array('name' => 'Keyboard'),
    array('price' => 'ASC')
);

Tip

When rendering a page requires to make some database calls, the web debug toolbar at the bottom of the page displays the number of queries and the time it took to execute them:

If the number of database queries is too high, the icon will turn yellow to indicate that something may not be correct. Click on the icon to open the Symfony Profiler and see the exact queries that were executed.

Updating an Object

Once you've fetched an object from Doctrine, updating it is easy. Suppose you have a route that maps a product id to an update action in a controller:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

use AppBundle\Entity\Product;
// ...

public function updateAction($productId)
{
    $entityManager = $this->getDoctrine()->getManager();
    $product = $entityManager->getRepository(Product::class)->find($productId);

    if (!$product) {
        throw $this->createNotFoundException(
            'No product found for id '.$productId
        );
    }

    $product->setName('New product name!');
    $entityManager->flush();

    return $this->redirectToRoute('homepage');
}

Updating an object involves just three steps:

1. fetching the object from Doctrine;
2. modifying the object;
3. calling flush() on the entity manager.

Notice that calling $entityManager->persist($product) isn't necessary. Recall that this method simply tells Doctrine to manage or "watch" the $product object. In this case, since you fetched the $product object from Doctrine, it's already managed.

Deleting an Object

Deleting an object is very similar, but requires a call to the remove() method of the entity manager:

1
2

$entityManager->remove($product);
$entityManager->flush();

As you might expect, the remove() method notifies Doctrine that you'd like to remove the given object from the database. The actual DELETE query, however, isn't actually executed until the flush() method is called.

Querying for Objects with DQL

Imagine that you want to query for products that cost more than 19.99, ordered from least to most expensive. You can use DQL, Doctrine's native SQL-like language, to construct a query for this scenario:

1
2
3
4
5
6
7
8
9

$entityManager = $this->getDoctrine()->getManager();
$query = $entityManager->createQuery(
    'SELECT p
    FROM AppBundle:Product p
    WHERE p.price > :price
    ORDER BY p.price ASC'
)->setParameter('price', 19.99);

$products = $query->getResult();

If you're comfortable with SQL, then DQL should feel very natural. The biggest difference is that you need to think in terms of selecting PHP objects, instead of rows in a database. For this reason, you select from the AppBundle:Product entity (an optional shortcut for the AppBundle\Entity\Product class) and then alias it as p.

The getResult() method returns an array of results. To get only one result, you can use getOneOrNullResult():

1

$product = $query->setMaxResults(1)->getOneOrNullResult();

The DQL syntax is incredibly powerful, allowing you to easily join between entities (the topic of relations will be covered later), group, etc. For more information, see the official Doctrine Query Language documentation.

Querying for Objects Using Doctrine's Query Builder

Instead of writing a DQL string, you can use a helpful object called the QueryBuilder to build that string for you. This is useful when the actual query depends on dynamic conditions, as your code soon becomes hard to read with DQL as you start to concatenate strings:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

$repository = $this->getDoctrine()
    ->getRepository(Product::class);

// createQueryBuilder() automatically selects FROM AppBundle:Product
// and aliases it to "p"
$query = $repository->createQueryBuilder('p')
    ->where('p.price > :price')
    ->setParameter('price', '19.99')
    ->orderBy('p.price', 'ASC')
    ->getQuery();

$products = $query->getResult();
// to get just one result:
// $product = $query->setMaxResults(1)->getOneOrNullResult();

The QueryBuilder object contains every method necessary to build your query. By calling the getQuery() method, the query builder returns a normal Query object, which can be used to get the result of the query.

For more information on Doctrine's Query Builder, consult Doctrine's Query Builder documentation.

Organizing Custom Queries into Repository Classes

All the queries in the previous sections were written directly in your controller. But for organization, Doctrine provides special repository classes that allow you to keep all your query logic in one, central place.

see How to Create custom Repository Classes for info.