Different ways of symfony2 database operations and executing queries using doctrine DQL

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. 

QUERYING FOR OBJECTS:

    You've already seen how the repository object allows you to run basic queries without any work in the previous post.

EXAMPLE:

    $repository->find($id);
    $repository->findOneByName('Foo');

i) QUERYING FOR OBJECTS USING DOCTRINE'S QUERY BUILDER:

    Imagine that you want to query for products, but only return products that cost more than 11.11, ordered from cheapest to most expensive. You can use Doctrine's QueryBuilder for this:

EXAMPLE:

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

$query = $repository->createQueryBuilder('p')
->where('p.price > :price')
->setParameter('price', '11.11’)
->orderBy('p.price', 'ASC')
->getQuery();
$products = $query->getResult();

ii) QUERYING FOR OBJECTS WITH DQL:

Instead of using the QueryBuilder, you can alternatively write the queries directly using DQL:

EXAMPLE:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
'SELECT p
FROM FancyStoreBundle:Product p
WHERE p.price > :price
ORDER BY p.price ASC'
)->setParameter('price', '11.11’);

$products = $query->getResult();

In addition, doctrine allow users with its own style of querying results from database table. Now we see some different methods (techniques), how to use Doctrine querying system. Let have the same example of Product table.

Example:

Method 1:
$em = $this->getDoctrine()->getManager();
$qb = $em->createQueryBuilder() 
        ->SELECT('p')
      ->FROM(FancyStoreBundle:Product', 'p');
$query = $qb->getQuery();
$results=$query->getResults();

Method 2:
$em = $this->getDoctrine()->getManager();
$query = 'SELECT p FROM FancyStoreBundle:Product p;
        $qb = $em->createQuery($query);
        $results = $qb->getSingleScalarResult();

By surprise all the above queries returns same results. From this shows doctrine allow users by their ways to querying any valid database objects.

CUSTOM REPOSITORY CLASSES:

In the previous sections, you began constructing and using more complex queries from inside a controller. In order to isolate, test and reuse these queries, it's a good practice to create a custom repository class for your entity and add methods with your query logic there.


STEP 1: To do this, add the name of the repository class to your mapping definition

Example:
# src/Fancy/StoreBundle/Resources/config/doctrine/Product.orm.yml
Fancy\StoreBundle\Entity\Product:
   type: entity
   repositoryClass: Fancy\StoreBundle\Entity\ProductRepository
# ...

STEP 2: 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 Fancy

STEP 3: Next, add a new method - findAllOrderedByName() - to the newly generated repository class. This method will query for all of the Product entities, ordered alphabetically.

Example:
// src/Fancy/StoreBundle/Entity/ProductRepository.php
namespace Fancy\StoreBundle\Entity;

use Doctrine\ORM\EntityRepository;

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

STEP 4: You can use this new method just like the default finder methods of the repository:

Example:

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

 
ENTITY RELATIONSHIPS/ASSOCIATIONS:

Suppose that the products in your application all belong to exactly one "category". In this case, you'll need a Category object and a way to relate a Product object to a Category object. Start by creating the Category entity. Since you know that you'll eventually need to persist the class through Doctrine, you can let Doctrine create the class for you.

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

This task generates the Category entity for you, with an id field, a name field and the associated getter and setter functions.

RELATIONSHIP MAPPING METADATA:

To relate the Category and Product entities, start by creating a products property on the Category class:

Example:

# src/Fancy/StoreBundle/Resources/config/doctrine/Category.orm.yml
Fancy\StoreBundle\Entity\Category:
   type: entity
   # ...
   oneToMany:
       products:
           targetEntity: Product
           mappedBy: category
   # don't forget to init the collection in the __construct() method of the entity

Next, since each Product class can relate to exactly one Category object, you'll want to add a $category property to the Product class:

Example:

# src/Fancy/StoreBundle/Resources/config/doctrine/Product.orm.yml
Fancy\StoreBundle\Entity\Product:
   type: entity
   # ...
   manyToOne:
       category:
           targetEntity: Category
           inversedBy: products
           joinColumn:
               name: category_id
               referencedColumnName: id



Finally, now that you've added a new property to both the Category and Product classes, tell Doctrine to generate the missing getter and setter methods for you

        $ php app/console doctrine:generate:entities Fancy

FETCHING RELATED OBJECTS:

When you need to fetch associated objects, your workflow looks just like it did before. First, fetch a $product object and then access its related Category.

Example:

public function showAction($id)
{
    $product = $this->getDoctrine()
        ->getRepository(FancyStoreBundle:Product')
        ->find($id);

    $categoryName = $product->getCategory()->getName();

    // ...
}

USEFULL SYMFONY2 COMMANDS (CRUD OPERATIONS) :

1. php app/console generate:bundle TestSampleBundle
2. php app/console doctrine:mapping:import TestSampleBundle yml
3. php app/console doctrine:generate:entities TestSampleBundle
4. php app/console doctrine:crud TestSampleBundle:{EntityName}eg:Product