Symfony 2 / Doctrine: How to reduce the number of queries without losing the benefits of ORM?
I am using Symfony 2.7 with Doctrine . My controller actions often look like this:
# my/namespace/Controller/ItemsController.php -> listAction()
$items = $this->get('repository.items')->findAll();
return $this->render('itemsList.html.twig', array('items' => $items));
In my templates, I like to iterate over related objects :
# my/namespace/Resources/views/itemsList.html.twig
{% for item in items %}
Item: {{ item.name }} <br/>
Groups: <br/>
<ul>
{% for group in item.groups %}
<li>{{ group.name }}</li>
{% endfor %}
</ul>
{% endfor %}
This causes a lot of SELECT queries , which I would like to avoid. The only solution I know of so far is collecting all the required data in repository
and assigning it to action
instead of traversing within the template.
I would like to keep it the way you see it in the twip snippet. Are there smart caching options that can detect cases like mine and automatically optimize queries?
Thanks in advance!
source to share
As others have said, you should get the Entities group inside your repository. You can use something like this:
//inside the repository
public function findAllFetchGroups(){
return $this->createQueryBuilder('a')
->addSelect('gs')->join('a.groups', 'gs')
->getQuery()->getResult();
}
source to share
There are two ways to achieve your goal. You can get = "EAGER" your linked data (but it always loads, possibly optional data) or you can use a query builder to combine the linked collection, it will be loaded in one query. But remember to keep the code clean - no requests outside the repositories :)
source to share