How to limit down queries from doctrine using Join in symfony2

Question

I'm having troubles getting related entities in a single query.I used both fetch=Eager and manual query with join.Here are my entities

    ----------------------Store.php--------------------------------------
/**
     * @ORM\OneToMany(targetEntity="Mycomp\ProductBundle\Entity\Product", mappedBy="store_id", cascade={"all"},fetch="EXTRA_LAZY")
     *
     **/
    protected $products;


--------------------Product.php---------------------------------------
/**
     * @ORM\OneToMany(targetEntity="ProductOption", mappedBy="product", cascade={"all"},orphanRemoval=true,fetch="EAGER")
     *
     **/
    protected $options;
    /**
     * @ORM\OneToMany(targetEntity="Like", mappedBy="product", cascade={"all"},orphanRemoval=true,indexBy="id",fetch="EAGER")
     *
     **/
    protected $likes;
-------------------Like.php---------------------------------------------
/**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Product", inversedBy="likes")
     *
     **/
    private $product;

    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Mycomp\UserBundle\Entity\User",inversedBy="likes")
     *
     **/
    private $user;
-------------------Option.php---------------------------------------------
/**
     * @ORM\ManyToOne(targetEntity="Product", inversedBy="options")
     *
     **/
    private $product;

And the query:

$qb = $em->createQueryBuilder('qb1');
        $qb->add('select', 'a', 'p', 'po', 'pl','sl')
            ->add('from', 'MycompStoreBundle:Store a')
            ->add('where', 'a.id = :store')
            ->innerJoin('a.products','p')
                ->leftJoin('p.options','po') // ***
                ->leftJoin('p.likes','pl')
            ->leftJoin('a.followers','sl')// ***
        ->setParameter('store', $id);
     $store  = $qb->getQuery()->getResult();

And in twig

{% for product in store.0.products %}
{{ product.name }}

    {% for option in product.options %}
{{ option }}

{% endfor %}

    <div id="like-holder{{ product.id }}">  {{   like_module(product) }}</div>
{% endfor %}

For 4 products i get these queries:

+ SELECT t0.username AS [...] FROM apps_user t0 WHERE t0.id = ? LIMIT 1
Parameters: [2]
[Display runnable query]
Time: 1.52 ms [ + Explain query ]
+ SELECT s0_.id AS id0, [...] FROM store s0_ INNER JOIN [...] WHERE (s0_.id = ?) AND s0_.dtype [...]
Parameters: ['5']
[Display runnable query]
Time: 0.89 ms [ + Explain query ]
+ SELECT t0.id AS id1, t0.name AS [...] FROM product t0 WHERE t0.store_id_id = ? AND [...]
Parameters: [5]
[Display runnable query]
Time: 0.69 ms [ + Explain query ]
+ SELECT t0.id AS id1, t0.name AS [...] FROM product_option t0 WHERE t0.product_id = ? AND [...]
Parameters: [5]
[Display runnable query]
Time: 0.46 ms [ + Explain query ]
+ SELECT t0.createdAt AS createdAt1, [...] FROM product_like t0 WHERE t0.product_id = ? AND [...]
Parameters: [5]
[Display runnable query]
Time: 0.39 ms [ + Explain query ]
+ SELECT t0.id AS id1, t0.name AS [...] FROM product_option t0 WHERE t0.product_id = ? AND [...]
Parameters: [6]
[Display runnable query]
Time: 0.54 ms [ + Explain query ]
+ SELECT t0.createdAt AS createdAt1, [...] FROM product_like t0 WHERE t0.product_id = ? AND [...]
Parameters: [6]
[Display runnable query]
Time: 0.45 ms [ + Explain query ]
+ SELECT t0.id AS id1, t0.name AS [...] FROM product_option t0 WHERE t0.product_id = ? AND [...]
Parameters: [7]
[Display runnable query]
Time: 0.42 ms [ + Explain query ]
+ SELECT t0.createdAt AS createdAt1, [...] FROM product_like t0 WHERE t0.product_id = ? AND [...]
Parameters: [7]
[Display runnable query]
Time: 0.40 ms [ + Explain query ]
+ SELECT t0.id AS id1, t0.name AS [...] FROM product_option t0 WHERE t0.product_id = ? AND [...]
Parameters: [8]
[Display runnable query]
Time: 0.37 ms [ + Explain query ]
+ SELECT t0.createdAt AS createdAt1, [...] FROM product_like t0 WHERE t0.product_id = ? AND [...]
Parameters: [8]
[Display runnable query]
Time: 0.42 ms [ + Explain query ]

Why is that? As you can see i get addition 2 queries per product.For likes and options.

George · Accepted Answer · 2014-08-14 17:54:41Z

Try changing the qb code from:

$qb = $em->createQueryBuilder('qb1');
    $qb->add('select', 'a', 'p', 'po', 'pl','sl')
        ->add('from', 'MycompStoreBundle:Store a')
        ->add('where', 'a.id = :store')
        ->innerJoin('a.products','p')
            ->leftJoin('p.options','po') // ***
            ->leftJoin('p.likes','pl')
        ->leftJoin('a.followers','sl')// ***
    ->setParameter('store', $id);
 $store  = $qb->getQuery()->getResult();

to:

$qb = $em->createQueryBuilder('a');
        $qb->select('a', 'p', 'po', 'pl','sl')
            ->from('MycompStoreBundle:Store a')
            ->where('a.id = :store')
            ->innerJoin('a.products','p')
            ->leftJoin('p.options','po') // ***
            ->leftJoin('p.likes','pl')
            ->leftJoin('a.followers','sl')// ***
            ->setParameter('store', $id); 

$store  = $qb->getQuery()->getResult();

user3817718 · Accepted Answer · 2014-08-14 18:40:13Z

0

Ok here is the fix in case someone needs it sometime.

$qb = $em->createQueryBuilder('qb1');
        $qb->add('select', 'a,s,po,pi,us,so,si,su')
            ->add('from', 'MycompStoreBundle:Store a')
            ->add('where', 'a.id = :store')

            ->leftJoin('a.followers','so')
           ->leftJoin('a.tags','si')
            ->leftJoin('a.categories','su')

            ->Join('a.products','s')
                ->leftJoin('s.options','po')
                ->leftJoin('s.likes','pi')
                ->leftJoin('pi.user','us')

        ->setParameter('store', $id);

     $store  = $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);

then treat everything as array.

answered Aug 14, 2014 at 18:40

user3817718

1

Comments

Matthew A Thomas · Accepted Answer · 2014-09-17 10:58:13Z

You could neaten that query further by doing.

 //Controller
 $em = $this->getDoctrine()->getManager();
 $qb = $em->createQueryBuilder();

 $store = $qb->select('a', 's', 'po', 'pi', 'us', 'so', 'si', 'su')
        ->from('MycompStoreBundle:Store', 'a')
        ->add('where', 'a.id = :id')
        ->innerJoin('a.products','s')
        ->leftJoin('a.followers','so')
        ->leftJoin('a.tags','si')
        ->leftJoin('a.categories','su')
        ->leftJoin('s.options','po')
        ->leftJoin('s.likes','pi')
        ->leftJoin('pi.user','us')
        ->setParameter(compact('id'))
        ->getQuery()
        ->getOneOrNullResult();

  return compact('store');

  //twig
  {% for product in store.products}

     {% for option in product.options %}
         {{ option }}
     {% endfor %}

     <div id="like-holder{{ product.id }}">  {{   like_module(product) }}</div>
     {% else %}
       // no results
  {% endfor %}

Collectives™ on Stack Overflow

How to limit down queries from doctrine using Join in symfony2

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related