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

Answer 1

->addSelect('c') 

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