Getting started with Symfony2 Database Operations Doctrine Example

     Symfony is an enterprise level PHP MVC framework. In symfony database operations are done using Object Relationship Mapping (ORM) which means we use Classes and Objects rather than direct SQL queries.     

    The easiest way to understand how Doctrine works in symfony2 is to see it in action using example. In this tutorial, you'll configure your database, create a Product object, persist it to the database and fetch it back out. 

Example: A Product

NOTE:

If you want to follow along with the example in this chapter, create an FancyStoreBundle via:

            $ php app/console generate:bundle -–namespace=Fancy/StoreBundle

CONFIGURING THE DATABASE:

    Before you starting, you'll need to configure your database connection information. This configuration is generally available in parameters.yml file. Basic path for this file in symfony is “app/config/parameters.yml”.

Example:

    # app/config/parameters.yml

    parameters:

        database_driver:    pdo_mysql

        database_host:      localhost

        database_name:      test_project

        database_user:      root

        database_password:  password

    After giving above information in parameter.yml file. Main configuration file of symfony i.e. “app/config/config.yml” has look like below manner.

Example:

    # app/config/config.yml

    imports:

    - { resource: parameters.yml }

    doctrine:

        dbal:

           driver:   "%database_driver%"

           host:     "%database_host%"

           dbname:   "%database_name%"

           user:     "%database_user%"

           password: "%database_password%"

           charset:  UTF8

NOTE:

    Defining the configuration via parameters.yml is just a practice. The parameters defined in that file are referenced by the main configuration file.

ADD MAPPING INFORMATION

    Doctrine allows you to work with databases in a interesting way than just fetching rows of a column-based table into an array. Instead, Doctrine allows you to persist entire objects to the database and fetch entire objects out of the database. This works by mapping a class to a database table, and the properties of that class to columns on the table. The following command does that for us.

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

    The above command, creates an ORM file as Product.orm.yml as following.

Example:

    # src/Fancy/StoreBundle/Resources/config/doctrine/Product.orm.yml

    Fancy\StoreBundle\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

CREATING ENTITY CLASS

    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, you need to create getter and setter methods (e.g. getName(), setName()) in order to access its properties (since the properties are protected). Fortunately, Doctrine can do this for you by running:

            $ php app/console doctrine:generate:entities Fancy/StoreBundle/Entity/Product

    This command makes sure that all of the getters and setters are generated for the Product class.

INSERTING OBJECTS TO THE DATABASE

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

EXAMPLE:

    // src/Fancy/StoreBundle/Controller/DefaultController.php

    // ...

    use Fancy\StoreBundle\Entity\Product;

    use Symfony\Component\HttpFoundation\Response;

    public function createAction()

    {

        $product = new Product();

        $product->setName(‘Pencil’);

        $product->setPrice('19.99');

        $product->setDescription('HB 1 pencil');

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

        $em->persist($product);

        $em->flush();

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

    }

SELECTING 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

EXAMPLE:

    public function showAction($id)

    {

        $product = $this->getDoctrine()

                        ->getRepository(FancyStoreBundle:Product')

                        ->find($id);

        if (!$product) {

            throw $this->createNotFoundException(

            'No product found for id '.$id

        );

        }else{

            //do your operation with $product object

        }

    }

    To get the repository the following method getRepository() is used. Its bring Product Repository in this scenario.

            $repository = $this->getDoctrine()

                                ->getRepository(FancyStoreBundle:Product');

    Once you have your repository, you have access to all sorts of helpful methods:

EXAMPLE:

    // query by the primary key (usually "id")

    $product = $repository->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);

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

EXAMPLE:

    // 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')

    );

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:

EXAMPLE:

    public function updateAction($id)

    {

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

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

        if (!$product) {

            throw $this->createNotFoundException(

            'No product found for id '.$id

            );

        }else{

            $product->setName(Rubber');

        $em->flush();

        }

        return $this->redirect($this->generateUrl('homepage'));

    }

DELETING AN OBJECT

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

EXAMPLE:

    $em->remove($product);

    $em->flush();