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



78 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. 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
  10. This information about the technology database operations.Thanks for this information.Regards,
    VMWare Training Institute in Chennai | VMWare Training Institute in Thiruvanmiyur

    ReplyDelete
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. 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
  35. Very interesting blog which helps me to get the in depth knowledge about the technology, Thanks for sharing such a nice blog..
    Java Project Center in Chennai | Java Project Center in Velachery | Java Projecs in Perungudi

    ReplyDelete
  36. Excellent post... Thank you for sharing such a informative and information blog with us.keep updating such a wonderful post..
    MicorSoft Azure Training Institute in Chennai | Azure Training Center in Chennai | Azure Certification Training in velachery | Online Azure training in Velachery

    ReplyDelete
  37. Nice post.. Its really an amazing with informative information and useful for everyone. Thanks for sharing your wonderful article..

    AWS Certification in Chennai | AWS Exam Center in Chennai | AWS Exams in Velacheri | AWS Online Exams in Velachery | Online Certification in Chennai

    ReplyDelete
  38. “Nice post!” Keep up the Amazing Good Work. Really your post was very interesting to read. Waiting for your upcoming update. Good Luck….



    AWS Training Institute in Velachery |
    AWS Training Institute in Chennai |
    AWS Training Institute in Tambaram |
    AWS Training Institute in Taramani|
    AWS Training Institute in Medavakkam

    ReplyDelete
  39. Nice blog. You put Good stuff. All the topics were explained briefly. So easily understand for me. I am waiting for your next awesome blog. Thanks for sharing.


    Python Training Institute in Velachery |
    Python Training Institute in Chennai |
    Python Training Institute in Tambaram |
    Python Training Institute in Taramani|
    Python Training Institute in Medavakkam

    ReplyDelete
  40. Very informative blog. Thanks for sharing such a excellent blog. It is very useful for everyone. keep sharing
    GMAT Test Center in Chennai | GMAT Test Center in Velachery

    ReplyDelete
  41. Thank you so much for sharing your nice post with us.. keep updating..
    GMAT Test Center in Chennai | GMAT Test Center in Velachery

    ReplyDelete
  42. I am really happy to read your blog. Your article is awesome with impressive content. Keep updating..
    IELTS Test Center in Chennai | IELTS Test Center in Velachery

    ReplyDelete
  43. Thanks for sharing your blog articles with us .... This is actually amazing and very useful information .I admire the valuable information you offer in your articles. Thanks for posting it....And i would like to share our website final year mba projects chennai

    ReplyDelete
  44. Microsoft Azure certification training in Chennai is career-oriented training and you will master managing the Azure to become a certified Azure Administrator. This Azure Administrator online training course in Chennai is aligned with Microsoft certification. Get in-depth experience in Azure.FITA Academy offers the best Microsoft Azure Training in Chennai for aspirants to learn about Microsoft’s Cloud Services extensively. The wide range of Microsoft’sCloud Services like Computing, Storage, Analytics, Networking, etc is precisely taught to students through best hands-on practices. Microsoft’s Cloud Solutions like IaaS, PaaS, SaaS, and Serverless Computing are also conducted largely. Learning at Alltech Academy will make students stand out from the crowd and be distinguishable in terms of achieving proficiency and expertise in developing Microsoft’s Cloud Services.

    ReplyDelete
  45. Alltechzsolutions provides 100% real-time, practical and placement focused CCNA training in Chennai. Our CCNA course concentrates from basic level training to advanced level training. Our CCNA® training in completely focused to get placement in MNC in Chennai and certification on CCNA after completion of our course. Our team of CCNA trainers are CCNA certified professionals with more real-time experience in live projects. Our CCNA® Course syllabus is enough for anyone who wants to get CCNA certification which meets industry expectations. In our course plan, you will learn Networking concepts,OSI – Model,TCP/IP Model & Ipv4 Addressing, with practical exercises and live examples.Alltechzsolutions offer both off line and online courses.

    ReplyDelete

WhichIsBest
WhichIsBest