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



59 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
  27. 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.
    Web Designing and Development Training Institute in Chennai | Web Designing and Development Training Institute in Thiruvanmiyur

    ReplyDelete
  28. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating.
    Android Apps Training Institute in Chennai | Android Apps Training Institute in Besant Nagar

    ReplyDelete
  29. Nice and good article.. it is very useful for me to learn and understand easily.. thanks for sharing your valuable information and time.. please keep updating.
    Web Desigining Training Institute in Chennai | Web Designing Training Institute in Pallavaram

    ReplyDelete
  30. 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.
    Web Desigining Training Institute in Chennai | Web Designing Training Institute in Pallavaram

    ReplyDelete
  31. Awesome Post!!!You have clearly explained .Its very useful for me to know about new things. Keep on blogging.
    VMware Training Institute in Chennai | VMware Training Institute in Meenambakkam

    ReplyDelete
  32. Awesome..You have clearly explained ...Its very useful for me to know about new things..Keep on blogging..
    Android Training Institute in Chennai | Android Training Institute in T.nagar

    ReplyDelete
  33. Great blog thanks for sharing. The digital marketing company in chennai providing a best digital marketing service in chennai.

    ReplyDelete
  34. Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
    Google ads services
    Google Ads Management Services
    Google Ads Management Company
    Google advertising agency
    Google Ads Management agency
    Google ad services on phone

    ReplyDelete
  35. Thanks for sharing this great article! That is very interesting I love reading and I am always searching for informative articles like this..
    Cisco Certification Training in Chennai | Cisco Certification Courses in OMR | Cisco Certification Exams in Velachery

    ReplyDelete
  36. Wow!!..What an excellent informative post, its really useful.Thank you so much for sharing such a awesome article with us.keep updating..
    VMware Certification Training in Chennai | VMware Training Institute in Velachery | VMware Certification Courses in Medavakkam

    ReplyDelete
  37. Amazing blog. Thank you for sharing. The information you shared is very effective for learners I have got some important suggestions from it..
    Embedded System Training Institute in Chennai | Embedded Training in Velachery | Embedded Courses in T.nagar

    ReplyDelete
  38. Great post.Thanks for one marvelous posting! I enjoyed reading it;The information was very useful.Keep the good work going on!!
    Tally Training Institute in Chennai | Tally Training in Velachery | Best Tally Courses in Guindy | Tally Training Center in Pallikaranai

    ReplyDelete
  39. I am reading your post from the beginning,it was so interesting to read & I feel thanks to you for posting such a good blog,keep updates regularly..
    Web Designing and Development Training in Chennai | Web Designing Training Center in Velachery | Web Design Courses in Pallikaranai

    ReplyDelete
  40. Awesome post.. Really you are done a wonderful job.thank for sharing such a wonderful information with us..please keep on updating..
    PCB Designing Training Institute in Chennai | PCB Training Center in Velachery | PCB Design Courses in Thiruvanmiyur

    ReplyDelete
  41. Wonderful article..This is very informative blog. Glad to found your blog.Helps to gain knowledge about new concepts and techniques. Thanks for posting information in this blog..
    Linux Certification Training Institute in Chennai | Linux Training Center in Velachery | Linux Courses in Medavakkam

    ReplyDelete
  42. This is useful post for me. I learn lot of new information from your post. keep sharing. thank you for share us..
    Web Designing Training Institute in Chennai | Web Design Taining Center in Velachery | Web Designing Courses in Taramani

    ReplyDelete
  43. Thanks for sharing this great article! That is very interesting I love reading and I am always searching for informative articles like this.
    MatLab Training Institute in Chennai | MatLab Training Center in Velachery | Matlab Courses in Adyar

    ReplyDelete

WhichIsBest
WhichIsBest