Symfony Doctrine optimisation

Question

I have MN relation between USER and CATEGORY table. Problem appear when user has more than 100 interests. When I execute very simple query like this:

  return $this->createQueryBuilder('usercategory')
        ->innerJoin('usercategory.user', 'u')
        ->innerJoin('usercategory.category', 'c')
        ->where('u.id = :user_id')
        ->setParameter('user_id', $user_id)
        ->getQuery()
        ->getResult();

Symfony profiler reported that more than 100 queries are executed with more than 150ms time for execution. 90% of those queries look like

SELECT t0.category_name AS category_name1,t0.type AS type2, t0.status AS status3,  t0.category_id AS category_id4, t0.parent_id AS parent_id5 FROM categories t0 WHERE t0.category_id = ?

This mean, if user has 100 categories, doctrine will execute 100 query to fetch them all.

Am i doing something wrong and how i can use make it faster by using DQL?

thx, MIlos

->addSelect('c') will cause the categories to be returned from your query (as opposed to lazy loading them). — Cerad
– Cerad, Commented Nov 18, 2013 at 16:07
@Cerad Please make your comment as answer so i can accept it. thx again! — Milos
– Milos, Commented Nov 19, 2013 at 7:27

Cerad · Accepted Answer · 2013-11-19 13:00:25Z

4

->addSelect('c')

will cause the categories to be returned from your query (as opposed to lazy loading them).

answered Nov 19, 2013 at 13:00

Cerad

49k9 gold badges92 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Symfony Doctrine optimisation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related