Databases and Symfony

Overview

Symfony standard edition comes bundled with Doctrine, a library that provides ORM and reading/writing to databases.

Symfony, however, is not tied to Doctrine.

The important thing to keep in mind with Doctrine is that you're working with the entity classes, not directly with database objects. Doctrine results are entity classes, not rows in tables.

Configuring database connections

The actual database connection property values are stored in app/config/parameters.yml, which are in turn referenced in the app's main configuration file, app/config/config.yml:

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

Using the Symfony configuration, a database can be created with

$ php app/console doctrine:database:create

Entity classes

Entity classes define the basic mapping to a table in the database.

Configuring entity classes

Classes that represent table data go in the Entity directory inside of AppBundle.

Columns are represented by properties of the class. Typically these are protected and accessed with public "getter" and "setter" functions.

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

class Product
{
	protected $id;
	protected $name;
	protected $description;

	public function getId()
	{
		return ($this->id);
	}

	public function setID($id)
	{
		$this->id = $id;
	}

	/* etc... */ 
}

The getter and setter routines can be generated with

$ php app/console doctrine:generate:entities AppBundle

or

$ php app/console doctrine:generate:entities AppBundle/Entity/Product

To create an entity class solely from the command line:

$ php app/console doctrine:generate:entity \
    --entity="AppBundle:Category" \
    --fields="name:string(255)"

Mapping information

Mapping metadata can be specified with YAML, XML, or directly in the entity class via annotations:,

// 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")
	 */
	protected $id;

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

	/* etc... */
}

Generating database tables and/or schema

Doctrine can create and update tables in the database using the entity classes as templates with:

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

Changes to the properties and mapping of the entity class will cause updates that will attempt to preserve the existing data.

The preferred way to generate changes that can be applied to a production environment is with migrations which generate SQL statements that are stored in migration classes.

Repository classes

Repository classes isolate custom queries so the can be more easily and reliably reused and tested.

Mapping repository classes

To map an entity class to a repository class, add the name of the repository class to the mapping definition in the entity class:

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

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="AppBundle\Entity\ProductRepository")
 */
class Product
{
    //...
}

Automatically generating repository classes

Doctrine can generate the repository class for you by running the same command used earlier to generate the missing getter and setter methods:

$ php app/console doctrine:generate:entities AppBundle

Note that this command uses the project's database settings to connect. If the server is set to localhost in parameters.yml and the command is run from another machine, the database connection won't be correct.

Adding custom queries to repository classes

Add methods to the repository class for each custom query:

class ProductRepository extends EntityRepository
{
   public function findAllOrderedByName()
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT p FROM AppBundle:Product p ORDER BY p.name ASC'
            )
            ->getResult();
    }
}

Retrieve the results of the custom query in a controller:

$em = $this->getDoctrine()->getManager();
$products = $em->getRepository('AppBundle:Product')
    ->findAllOrderedByName();

Committing object data to a database

The logic to commit object data to a database is place in a controller:

// src/AppBundle/Controller/DefaultController.php

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

// ...
public function createAction()
{
    $product = new Product();
    $product->setName('A Foo Bar');
    $product->setPrice(19.95);
    $product->setDescription('Lorem ipsum dolor');

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

    $em->persist($product);
    $em->flush();

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

Committing associated entities

The entities on both sides of a many-to-one relationship must be updated before persisting the data:

$cat = new MyCategory();
$cat->add($item);		/* link parent to child */
$item->setCategory($cat);	/* link child to parent */

$em->persist($cat);		/* commits parent and child records */
$em->flush();

Retrieving data

Retrieving listings

$groups = $this->getDoctrine()
	->getRepository('AppBundle:TutorialGroup')
	->findAll();

Retrieving a single record

Repositories are used for queries for particular types of objects.

$product = $this->getDoctrine()
        ->getRepository('AppBundle:Product')
        ->find($id);

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

AppBundle::Product is shorthand for any class under the Entity namespace of the bundle.

Alternatively in a controller, the @ParamConverter annotation can be used to automatically load up an object using the value of an $id parameter passed to the controller.

findBy(), findOneBy(), and findBy[COLUMN_NAME] are repsoitory methods used to filter queries.