Editing Databases and Symfony

[[Category:Symfony]] [[category:PHP]] [[Category:Web Development]]

== Overview ==

Symfony standard edition comes bundled with [http://www.doctrine-project.org/ Doctrine], a library that provides [http://en.wikipedia.org/wiki/Object-relational_mapping ORM] and reading/writing to databases.<ref>[http://symfony.com/doc/current/book/doctrine.html Doctrine], Symfony documentation]</ref>

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`:<ref>[http://symfony.com/doc/current/book/doctrine.html#configuring-the-database Configuring the Database], Symfony documentation</ref>

<syntaxhighlight lang="yaml">
# app/config/config.yml
doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
</syntaxhighlight>

Using the Symfony configuration, a database can be created with 

<syntaxhighlight lang="bash">
$ php app/console doctrine:database:create
</syntaxhighlight>

== 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`.<ref>[http://symfony.com/doc/current/book/doctrine.html Doctrine Documentation], Symfony Documentation, "The Book"</ref>

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

<syntaxhighlight lang="php" highlight="6-8,10,15">
// 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... */ 
}
</syntaxhighlight>

The getter and setter routines can be generated with

<syntaxhighlight lang="bash">
$ php app/console doctrine:generate:entities AppBundle
</syntaxhighlight>

or 

<syntaxhighlight lang="bash">
$ php app/console doctrine:generate:entities AppBundle/Entity/Product
</syntaxhighlight>

To create an entity class solely from the command line:

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

=== Mapping information ===

Mapping metadata can be specified with YAML, XML, or directly in the entity class via annotations:<ref>[http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html#property-mapping Doctrine Property Mapping]</ref>,<ref>[http://symfony.com/doc/current/book/doctrine.html#book-doctrine-field-types Doctrine Field Types Reference]</ref><ref>[http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#annref-column Annotations Reference], Doctrine documentation</ref>

<syntaxhighlight lang="php" highlight="4,7-8,13-15,20">
// 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... */
}
</syntaxhighlight>

=== Generating database tables and/or schema ===

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

<syntaxhighlight lang="bash">
$ php app/console doctrine:schema:update --force
</syntaxhighlight>

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 [http://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html 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.<ref>[http://symfony.com/doc/current/book/doctrine.html#custom-repository-classes Custom Repository Classes], Symfony documentation</ref>

=== 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:

<syntaxhighlight lang="php" highlight="7">
// src/AppBundle/Entity/Product.php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

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

=== 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:

<syntaxhighlight lang="bash">
$ php app/console doctrine:generate:entities AppBundle
</syntaxhighlight>

'''''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:

<syntaxhighlight lang="php" highlight="5-9">
class ProductRepository extends EntityRepository
{
   public function findAllOrderedByName()
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT p FROM AppBundle:Product p ORDER BY p.name ASC'
            )
            ->getResult();
    }
}
</syntaxhighlight>

Retrieve the results of the custom query in a controller:

<syntaxhighlight lang="php">
$em = $this->getDoctrine()->getManager();
$products = $em->getRepository('AppBundle:Product')
    ->findAllOrderedByName();
</syntaxhighlight>

== Committing object data to a database ==

The logic to commit object data to a database is place in a controller:<ref>[http://symfony.com/doc/current/book/doctrine.html#persisting-objects-to-the-database Persisting Objects to the Database], Doctrine documentation at Symphony documentation, "The Book"</ref>

<syntaxhighlight lang="php" highlight="17-18">
// 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());
}
</syntaxhighlight>

=== Committing associated entities ===

The entities on both sides of a many-to-one relationship must be updated before persisting the data:<ref>[http://doctrine-orm.readthedocs.org/en/latest/reference/working-with-associations.html Working With Associations], Doctrine documentation</ref>

<syntaxhighlight lang="php">
$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();
</syntaxhighlight>

== Retrieving data ==

=== Retrieving listings ===

<syntaxhighlight lang="php">
$groups = $this->getDoctrine()
	->getRepository('AppBundle:TutorialGroup')
	->findAll();
</syntaxhighlight>

=== Retrieving a single record ===

Repositories are used for queries for particular types of objects.<ref>[http://symfony.com/doc/current/book/doctrine.html#fetching-objects-from-the-database Fetching Objects From the Database], Symfony documentation</ref>

<syntaxhighlight lang="php">
   $product = $this->getDoctrine()
        ->getRepository('AppBundle:Product')
        ->find($id);

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

`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.<ref>[http://symfony.com/doc/current/bundles/SensioFrameworkExtraBundle/annotations/converters.html FrameworkExtraBundle documentation], Symfony documentation</ref>

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

== Notes ==
<references/>