Technology Blog

Partners

WhichIsBest

Ads

ads
WhichIsBest

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



33 comments:

  1. Thank you so much for sharing such an amazing post with informative information with us. It’s helpful for everyone, keep updating such a wonderful blog you are shared.
    Best Linux Training Institute in Kanchipuram | No.1 Linux Training Institute in Kanchipuram

    ReplyDelete
  2. Excellent post!!!. The strategy you have posted in this technology helped me to get into the next level and had lot of information in it.
    Microsoft Azure Training Institute in Kanchipuram | Microsoft Azure Training in Kanchipuram

    ReplyDelete
  3. Thank you so much for sharing such an amazing post with informative information with us. It’s helpful for everyone, keep updating such a wonderful blog you are shared.
    Best CCNP Training Institute in Chennai | CCNP Training in Velachery

    ReplyDelete
  4. Your Blog is really awesome with useful content,thank you so much for sharing such an informative information.keep updating your creative knowledge with helpful article..
    Best Java Training Institute in Chennai | Java Training Center in Velachery

    ReplyDelete
  5. I found a lot of interesting information here. A really good post, very thankful and hopeful that you will write many more posts like this one. It was a really great experience. We had a really hard time because of four weeks, but at the end of the day it was awesome.

    Best Linux Training Institute in Kanchipuram | No.1 Linux Training Center in Kanchipuram

    ReplyDelete
  6. This information about the database operations is very useful for me.Thanks for this information.Regards,
    Python Training Institute in Chennai | Python Training Institute in Nanganallur

    ReplyDelete
  7. Thank you so much for sharing such an amazing post with informative database operations.keep updating such a wonderful blog.Regards,
    Android Training Institute in Chennai | Android Training Institute in Tambaram

    ReplyDelete
  8. Thank you so much for sharing such an amazing post with informative information with us. It’s helpful for everyone, keep updating such a wonderful blog you are shared.Best CCNA Training Institute in Chennai | CCNA Training Center in Velachery

    ReplyDelete
  9. Your article is really an amazing with useful content, thank you so much for sharing such an informative information. keep updating.
    AWS Training Institute in Chennai | Best AWS Training Center in Velachery | AWS Training in Perungudi | AWS Training in Kanchipuram

    ReplyDelete
  10. This information about the database operations is very useful for me.Thanks for this article.Regards,
    Android Training Institute in Chennai | Android Training Institute in Adyar

    ReplyDelete
  11. This information about the technology database operations.Thanks for this information.Regards,
    VMWare Training Institute in Chennai | VMWare Training Institute in Thiruvanmiyur

    ReplyDelete
  12. In this site you clarified the database operations in an effective way.Thanks for this blog.Regards,
    Python Training Institute in Chennai | Python Training Institute in Besant Nagar

    ReplyDelete
  13. This information you shared in this article about the database operations is more efficiency.Thanks for this Blog.Regards,
    IOS Training Institute in Chennai | IOS Training Institute in Saidapet

    ReplyDelete
  14. In this Blog you demonstrate the valuable information about the database operations. Thanks for your wonderful information. Regards,
    Outstanding Python Exam Center in Chennai | Outstanding Python Exam Center in St.Thomas Mount

    ReplyDelete
  15. This Blog is very profitable for me that you explained about the database operations. Thanks for your amazing information. Regards,
    Best Python Exam Center in Chennai | Best Python Exam Center in Porur

    ReplyDelete
  16. In this website you gave the information about the Different ways of symfony2 database operations which is very favourable. Thanks for your marvelous information. Regards,
    No.1 Cloud Computing Training Institute in Chennai | No.1 Cloud Computing Training Institute in Sholinganallur

    ReplyDelete
  17. I found a lot of interesting information here in this site. This is really good post, I’m very thankful and hopeful that you will write many more posts like this one. Regards,
    Top Most IOS Training Institute in Chennai | Top Most IOS Training Institute in Velachery

    ReplyDelete
  18. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    Excellent Web Designing Training Institute in Chennai | Excellent Web Designing Training Institute in T.Nagar

    ReplyDelete
  19. This is really nice information. I was looking for this since a long time. Thanks for sharing. Regards,
    Outstanding Selenium Training Institute in Chennai | Outstanding Selenium Training Institute in OMR

    ReplyDelete
  20. Thank you so much for sharing such an amazing post with informative information with us. It’s helpful for everyone, keep updating such a wonderful blog you are shared.
    Perfect Python Training Institute in Chennai | Perfect Python Training Institute in Perungudi

    ReplyDelete
  21. This is really nice information. I was looking for this since a long time. Thanks for sharing. Regards,
    Best Web Designing Training Institute in Chennai | Best Web Desigining Training Institute in Taramani

    ReplyDelete
  22. Thanks for sharing a valuable information to us. Thanks for your blog.
    CIMA training institute in Hyderabad | ISFS

    ReplyDelete
  23. Your Blog is really Nice and Informative. Thanks for sharing such a interesting article. Keep updating. I really enjoy simply reading all of your weblogs.
    TopMost Python Training Institute in Chennai | TopMost PythonTraining Institute in Madipakkam

    ReplyDelete
  24. Awesome Post!!!You have clearly explained .Its very useful for me to know about new things. Keep on blogging.
    TopMost Cloud Computing Training Institute in Chennai | TopMost Cloud Computing Training Institute in Tambaram

    ReplyDelete
  25. Nice Post! It is really interesting to read from the beginning & I would like to share your blog to my circles, keep sharing.
    Android Training Institute in Chennai | Android Training Institute in Velachery

    ReplyDelete
  26. Nice Post! It is really interesting to read from the beginning & I would like to share your blog to my circles, keep sharing.
    Android Training Institute in Chennai | Android Training Institute in Meenambakkam

    ReplyDelete

WhichIsBest
WhichIsBest