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



64 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
  11. Really very nice blog information for this one and more technical skills are improve,i like that kind of post.
    RPA Training Institute in Chennai | RPA Training Center in Velachery | Online Training Institute in Velachery

    ReplyDelete
  12. The website is looking bit flashy and it catches the visitors eyes. A design is pretty simple .
    regionale stellenbörse

    ReplyDelete
  13. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  14. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here. digital marketing training in chennai

    ReplyDelete
  15. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. 
    Java Training Center in Chennai | Best J2EE Training Center in Chennai | No.1 Java Training Institution in Velachery | Core Java Training in Chennai

    ReplyDelete
  16. Your Blog is nice and informative..Thanks for posting this useful and valuable article..
    Best Data Mining Project Center in Chennai | No.1 Data Mining Project Center in Velachery

    ReplyDelete
  17. Excellent post. I have read your blog it's very interesting and informative. Keep sharing.Mat Lab Project Center in Chennai | Mat Lab Project Center in Velachery

    ReplyDelete
  18. Really an amazing post..! By reading your blog post i gained more information.
    Power System Project Center in Chennai | Best Power System Projects in Velachery

    ReplyDelete
  19. Great article, really very helpful content you made. Thank you, keep sharing.

    IOT Services | Austere Technologies

    ReplyDelete
  20. VERY INFORMATIVE BLOG. KEEP SHARING SUCH A GOOD ARTICLES.

    Mobility Services | Austere Technologies

    ReplyDelete
  21. Hi,
    Thanks for sharing such an informative blog.weblogic tutorial

    ReplyDelete
  22. I strongly believe that there will be great opportunities for those who looked into this area. Thanks much for sharing such a nice article...
    Best Online Software Training Institute | Oracle DBA Training

    ReplyDelete
  23. i am happy to read your blog,its really amazing with useful information you are shared.keep updating..
    Image Processing Project Center in Chennai | Image Processing Projects in Velachery

    ReplyDelete

  24. Awe! What An Blog Very Helpful and interesting Really A great center for acquiring knowledge.Very Helpful Post And Explained Very Clearly About All the things.Very Helpful. Coming To Our Self We Provide Food Service Parts .Really Thankfull For the blogger providing such a great information.Thank you. Have a Nice Day.

    ReplyDelete

  25. Really It's A Great Pleasure reading your Article,learned a lot of new things,we have to keep on updating it,Urgent Care in Chicago.By getting them into one place.Really thanks for posting.Very Thankful for the Informative Post.Really Thanks For Posting.

    ReplyDelete
  26. Thank you for sharing beneficial information nice post Bala Guntipalli

    ReplyDelete
  27. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work.
    Image Processing Project Center in Chennai | Image Processing Projects in Velachery

    ReplyDelete
  28. Thanks For Posting Such an Valuable and Useful Information.....

    67500/12

    ReplyDelete
  29. Impressive blog with lovely information. really very useful article for us thanks for sharing such a wonderful blog...keep updating..
    Power System Project Center in Chennai | Power System Project Center in Velachery

    ReplyDelete
  30. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Cloud Computing Project Center in Chennai | Cloud Computing Project Center in Velachery

    ReplyDelete