[Symfony] Database and Doctrine (1) - DB, Entity, Repository

First, we can link up the database by configuring app/config/parameters.yml: change the database name, user and password to reflect your own choices.

If your database doesn't exists:

 Create database:

$php app/console doctrine:database:create

We are going to create an Entity called Page with text and body:

php app/console doctrine:generate:entity --entity=AppBundle:Page \
 --format=annotation --fields="title:string(255) body:text" \
 --no-interaction

php app/console doctrine:database:create
php app/console doctrine:schema:create

Doctrine can automatically create all the database tables needed for every known entity in your application. To do this, run:

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

If you already have database, import it:

- import database ( mysql <import.sql )

- import these database structures into Symfony:

$php app/console doctrine:mapping:import AppBundle yml

The database mapping is created in src/AppBundle/Resources/config/doctrine/

Now we need the form for that entity, another generator command :

php app/console doctrine:generate:form AppBundle:Page --no-interaction

Doctrine Mapping Types

• string: Type that maps a SQL VARCHAR to a PHP string.
• integer: Type that maps a SQL INT to a PHP integer.
• smallint: Type that maps a database SMALLINT to a PHP integer.
• bigint: Type that maps a database BIGINT to a PHP string.
• boolean: Type that maps a SQL boolean or equivalent (TINYINT) to a PHP boolean.
• decimal: Type that maps a SQL DECIMAL to a PHP string.
• date: Type that maps a SQL DATETIME to a PHP DateTime object.
• time: Type that maps a SQL TIME to a PHP DateTime object.
• datetime: Type that maps a SQL DATETIME/TIMESTAMP to a PHP DateTime object.
• datetimetz: Type that maps a SQL DATETIME/TIMESTAMP to a PHP DateTime object with timezone.
• text: Type that maps a SQL CLOB to a PHP string.
• object: Type that maps a SQL CLOB to a PHP object using serialize() and unserialize()
• array: Type that maps a SQL CLOB to a PHP array using serialize() and unserialize()
• simple_array: Type that maps a SQL CLOB to a PHP array using implode() and explode(), with a comma as delimiter. IMPORTANT Only use this type if you are sure that your values cannot contain a ”,”.
• json_array: Type that maps a SQL CLOB to a PHP array using json_encode() and json_decode()
• float: Type that maps a SQL Float (Double Precision) to a PHP double. IMPORTANT: Works only with locale settings that use decimal points as separator.
• guid: Type that maps a database GUID/UUID to a PHP string. Defaults to varchar but uses a specific type if the platform supports it.
• blob: Type that maps a SQL BLOB to a PHP resource stream

Generating Getters and Setters

# generates all entities of bundles in the AppBundle namespace

$ php app/console doctrine:generate:entities --path="src/" AppBundle

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

Entity files are created in src/AppBundle/Entity/.

Persisting an object to DB:

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

    $em->persist($product);

    $em->flush();

Fetching object from DB:

    $product = $this->getDoctrine()

       ->getRepository('AppBundle:Product')

       ->find($id);

// dynamic method names to find based on a column value

$product = $repository->findOneById($id);

$product = $repository->findOneByName('foo');

// find *all* products

$products = $repository->findAll();

// find a group of products based on an arbitrary column value

$products = $repository->findByPrice(19.99);

// query for one product matching by name and price

$product = $repository->findOneBy(

    array('name' => 'foo', 'price' => 19.99)

);

// query for all products matching the name, ordered by price

$products = $repository->findBy(

    array('name' => 'foo'),

    array('price' => 'ASC')

);

    if (!$product) {

       throw $this->createNotFoundException(

           'No product found for id '.$id

       );

    }

Updating an object

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

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

    if (!$product) {

       throw $this->createNotFoundException(

           'No product found for id '.$id

       );

    }

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

    $em->flush();

Delete an object:

$em->remove($product);

$em->flush();

Doctrine’s Query Builder:

$repository = $this->getDoctrine()

    ->getRepository('AppBundle:Product');

$query = $repository->createQueryBuilder('p')

    ->where('p.price > :price')

    ->setParameter('price', '19.99')

    ->orderBy('p.price', 'ASC')

    ->getQuery();

$products = $query->getResult();

$product = $query->getSingleResult();

$product = $query->getOneOrNullResult();

DQL:

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

$query = $em->createQuery(

    'SELECT p

    FROM AppBundle:Product p

    WHERE p.price > :price

    ORDER BY p.price ASC'

)->setParameter('price', '19.99');

$products = $query->getResult();

Custom Repository Classes

To create a repository to hold all the customized database manipulation methods, you need to:

• Add the name of the repository class to your mapping definition. (located at src/AppBundle/Resources/config/doctrine/*.orm.yml)

    For AppBundle\Entity\Product:

    type: entity

    repositoryClass: AppBundle\Entity\ProductRepository

    table: product

    fields:

          id:...

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 --path="src/" AppBundle

You will notice a new ProductRepository.php file is created under src/AppBundle/Entity.

• Create all necessary functions to provide required database manipulation capabilities (like retrieving data).
 

 

Reference

Symfony Doctrine