CODEWITHSUNDEEP
phpsymfonydoctrinedql
chrisp
chrisp

Reputation: 629

Symfony2 & Doctrine: Optimizing Query (Fetching EAGER)

Currently my query looks like this:

$customers = $em->createQueryBuilder()
                ->select('c')
                ->from('AppBundle:Customer', 'c')
                ->orderBy('c.id', 'ASC')
                ->setFirstResult($offset)
                ->setMaxResults($max)
                ->getQuery()
                ->getResult();

This returns me all data of the Customer entity. However, in my Twig template I have to access the @ManyToMany relation that the Customer entity has with PhoneNumber. That causes an extra query. Now I am trying to optimize this query:

$customers = $em->createQueryBuilder()
                ->select('c')
                ->addSelect('ph')
                ->from('AppBundle:Customer', 'c')
                ->leftJoin('AppBundle:PhoneNumber', 'ph', 'WITH', 'ph MEMBER OF c.phoneNumbers')
                ->orderBy('c.id', 'ASC')
                ->setFirstResult($offset)
                ->setMaxResults($max)
                ->getQuery()
                ->getResult();

Problem Problem with this query is that it returns the PhoneNumbers all by itself. Meaning that in $customers I don't have Customers with PhoneNumbers. Instead $customers is an array of Customers and PhoneNumbers. Over such an array I can't iterate properly. How can i fix this problem?

Dump Extract 

    object(stdClass)#2171 (12) {
      ["__CLASS__"]=>
      string(32) "Updoon\AppBundle\Entity\Customer"
      ["phoneNumbers"]=>
      array(2) {
        [0]=>
        string(35) "Updoon\AppBundle\Entity\PhoneNumber"
        [1]=>
        string(35) "Updoon\AppBundle\Entity\PhoneNumber"
      }
      ["id"]=>
      int(34)
   }

   object(stdClass)#2171 (9) {
      ["__CLASS__"]=>
      string(35) "AppBundle\Entity\PhoneNumber"
      ["customers"]=>
      array(1) {
        [0]=>
        string(32) "AppBundle\Entity\Customer"
      }
      ["id"]=>
      int(95)
      ["number"]=>
      string(11) "51 06 20 20"
    }

Upvotes: 1

Views: 468

Answers (1)

Jovan Perovic
Jovan Perovic

Reputation: 20191

That's almost correct. I would change:

  • remove an extra addSelect() - put everything in a single select()
  • change the leftJoin to utilize @ManyToMany relation

So, something like this:

    $customers = $em->createQueryBuilder()
    ->select('c', 'ph')
    ->from('AppBundle:Customer', 'c')
    ->leftJoin('c.phoneNumbers', 'ph')
    ->orderBy('c.id', 'ASC')
    ->setFirstResult($offset)
    ->setMaxResults($max)
    ->getQuery()
    ->getResult();

Hope this helps...

Upvotes: 1

Related Questions