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



96 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
  31. 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. Simply wanted to inform you that you have people like me who appreciate your work.

    Java Project Center in Chennai | Java Project Center in Velachery

    ReplyDelete
  32. Excellent post. I have read your blog it's very interesting and informative.Thanks for sharing such nice article, keep on up dating such good articles.
    Android Project Center in Chennai | Android Project Center in Velachery

    ReplyDelete
  33. 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.
    Cloud Computing Project Center in Chennai | Cloud Computing Project Center in Velachery

    ReplyDelete
  34. Hi Thanks for the nice information its very useful to read your blog. We provide best Block Chain Services

    ReplyDelete
  35. Hi Thanks for the nice information its very useful to read your blog. We provide best System Integration Services

    ReplyDelete
  36. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book Andaman holiday packages

    ReplyDelete
  37. Hi Thanks for the nice information its very useful to read your blog. We provide best company secretary course

    ReplyDelete
  38. Hi Thanks for the nice information its very useful to read your blog. We provide best Chartered Accountancy

    ReplyDelete
  39. Hi Thanks for the nice information its very useful to read your blog. We provide best Chartered Accountancy

    ReplyDelete
  40. Hi Thanks for the nice information its very useful to read your blog. We provide best Cost And Management Accountancy (CMA)

    ReplyDelete
  41. Hi Thanks for the nice information its very useful to read your blog. We provide best Certified Public Accountant

    ReplyDelete
  42. Hi Thanks for the nice information its very useful to read your blog. We provide best Certified Financial Analyst

    ReplyDelete
  43. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book ANDAMAN TOUR PACKAGE @24599

    ReplyDelete
  44. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    Final Year Project Center for BE in Chennai | Final Year Projects for BE in Velachery

    ReplyDelete
  45. Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book BEST ANDAMAN HONEYMOON PACKAGE @5999

    ReplyDelete
  46. Hi Thanks for the nice information its very useful to read your blog. We provide best Association Of Chartered Certified Accountants

    ReplyDelete
  47. Hi Thanks for the nice information its very useful to read your blog. We provide Software Development Services

    ReplyDelete
  48. Hi Thanks for the nice information its very useful to read your blog. We provide best Chartered Institute Of Management Accountants

    ReplyDelete
  49. Hi Thanks for the nice information its very useful to read your blog. We provide Software Development Services

    ReplyDelete
  50. Hadoop concepts, Applying modelling through R programming using Machine learning algorithms and illustrate impeccable Data Visualization by leveraging on 'R' capabilities.With companies across industries striving to bring their research and analysis (R&A) departments up to speed, the demand for qualified data scientists is rising.
    data science training in bangalore

    ReplyDelete
  51. Awesome blog. Your articles really impressed for me, because of all information so nice and unique.
    Android Project Center in Chennai | Android Project Center in Velachery

    ReplyDelete
  52. Your Blog is really amazing with useful information you are shared here...Thank you for sharing such an informative and wonderful post with us. Keep updating.
    AWS Training Institute in Chennai | AWS Training Institute in Velachery

    ReplyDelete
  53. Your Blog is really amazing with informative content,thank you so much for sharing such an wonderful Post with us.keep updating...

    Best Python Training Institute in Chennai | Best Python Training in Velachery

    ReplyDelete
  54. Thank you so much for sharing your informative post.Its really useful for me.keep updating such a wonderful post with us..
    No.1 Java Training Institute in Chennai | No.1 Java Training Center in Velachery

    ReplyDelete
  55. Hi Thanks for the nice information its very useful to read your blog. We provide best Find All Isfs Courses

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

    ReplyDelete
  57. Thank you so much for sharing such an informative information with us.its really useful for everyone,keep updating such an wonderful blog you are shared.
    Best Linux Training Institute in Kanchipuram | No.1 Linux Training Institute in Kanchipuram

    ReplyDelete

WhichIsBest
WhichIsBest