Symfony2 dealing with queries that are not in a relationships

Question

As the title says I need to somehow query for allot of tables that are not related in the best way possible. With dql you can join tables that are mapped, but if they are not mapped, how can I deal with that.

For example my main index page has the most queries(71 for now). As I understand in the future the more items it will have to query()for example if I add 100 products the query will increase very fast.

The biggest problem is that my index page not only queries form one controller. For example:

indexAction:

$em = $this->getDoctrine()->getManager();
            $carousel = $em->getRepository('ApplicationSonataMediaBundle:Gallery')->findOneBy(array('name' => 'Carousel'));
            $featureProducts = $em->getRepository('MpShopBundle:Product')->findBy(array('status' => 1, 'special' => 1));
            $newProducts = $em->getRepository('MpShopBundle:Product')->findBy(array('status' => 1), array('id' => 'ASC'), 8); // pakeisti y DESC
            $session = $this->getRequest()->getSession();
            $skin = $em->getRepository('MpShopBundle:Skin')->findOneBy(array('status' => 1));

               return $this->render('MpShopBundle:Frontend:index.html.twig',  array(
               'featureProducts'=>$featureProducts,
               'skin' => $skin,
               'newProducts' => $newProducts,
               'carousel' => $carousel,
               ));

This is only the index controller that already queries for allot of objects. But these objects are from different tables except the newProducts and featureProducts. Maybe I can join them to in one query? But they havo to be seperate.

Not only that but in the twig I extend even more controllers that have their own queries too.

<span class="top">{% render controller("MpShopBundle:Navbar:navbar" ) %}</span>

{% block sidebar %} {% render url( 'sidebar' ) %} {% endblock %}

This all sums up to allot of queries.. What would be the most logical way to reduce the query count?

Answer 1

The controller you posted makes around 5 queries, and joining the queries for $newProducts and $featureProducts won't save anything. Your problem probably lies with associations that are not Fetch joined, i.e: your product might have some association to some other entity and when you iterate through your products in your template and want to fetch the associated entity, it will create another query to the database to load that entity, since they are lazy loaded. See this question on how to fix that.