Thursday, November 27, 2014

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



22 comments:

  1. Nice to learn something new about database operations from this tutorial.
    residential interior designers in Chennai

    ReplyDelete
  2. Very clear explanation, nice tutorial. Thanks for sharing.
    IELTS classes in Kuwait

    ReplyDelete
  3. Gone are the days that people used to do direct marketing and now its time to increase the revenue through Digital Marketing Training in Chennai.Updating to the latest trends is the better and smarter way like Digital Marketing Course. Thank you so much for sharing contents like this. Keep up this good work.(Digital Marketing Course in Chennai)

    ReplyDelete
  4. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates…
    Regards
    Aamala
    SEO Training in Chennai

    ReplyDelete
  5. If you want get fantastic phone apps then click here and get with amazing affordable rates Phone System App

    ReplyDelete
  6. Nice blog. Informative and useful post you have shared. Thanks for sharing.


    Internet Marketing Company in chennai

    ReplyDelete
  7. The digital marketing is the powerful marketing media which is used to promote the product or brand. The digital marketing activities include search engine optimization, email direct marketing, social media optimisation, advertising.

    ReplyDelete
  8. Its interesting and informative. Thanks for your great information! erp in chennai|erp solutions chennai

    ReplyDelete
  9. Corporate Webdesign Nürnberg.Wir realisieren Ihren Internetauftritt. Überzeugen Sie Ihre Kunden mit Webdesign und profitieren Sie vom Wiedererkennungswert.
    Webdesign Nürnberg für Ihren perfekten Internetauftritt.Überzeugen Sie Ihre Kunden mit einer Corporate Website und profitieren Sie vom hohen Wiedererkennungswert unseres Webdesigns.

    ReplyDelete
  10. Corporate Webdesign Nürnberg.Wir realisieren Ihren Internetauftritt. Überzeugen Sie Ihre Kunden mit Webdesign und profitieren Sie vom Wiedererkennungswert.
    Webdesign Nürnberg für Ihren perfekten Internetauftritt.Überzeugen Sie Ihre Kunden mit einer Corporate Website und profitieren Sie vom hohen Wiedererkennungswert unseres Webdesigns.

    ReplyDelete