1

Hello I want to create a complex query where I need request info from 5 tables and group it by companies.name applying a filter by branches.idMunicipality my query is the next:

$em = $this->getDoctrine()->getManager();
    $companies = $em->createQuery('
        SELECT categories,
                subcategories,
                companies, 
                branches, 
                sales 
                FROM AspersoftDirectorioBundle:CompanyCategory categories 
                JOIN categories.subcategories subcategories 
                JOIN subcategories.companies companies
                JOIN companies.branches branches
                LEFT JOIN companies.sales sales
                WHERE branches.idMunicipality = :idMunicipality
                GROUP BY companies.id
                ORDER BY categories.name ASC'
    )
            ->setParameter('idMunicipality' , "475")
            ->getResult();
    return $companies;

My issues are:

  1. My DB is bidirectional in all tables then I have 27 DB querys (page loads very low)
  2. Group by don't work
  3. In my opinion is very hard get values in views because I need to use 4 or 5 "for"

Thank in advance for your help

EsopMx
  • 85
  • 1
  • 10
  • 1
    For #1, you have to identify where your slowdowns are and add appropriate indexes. You should have indexes on the columns of larger tables that join data together. For #2, is "companies" and "categories" in the GROUP BY and ORDER BY the name of the entity or the name of the table? Doctrine uses entity names for referencing everything, you should not be using the name of the table. #3, what "for" are you referring to? – PressingOnAlways Apr 17 '14 at 17:49
  • 1# - yes all my entities are appropiate indexed, but cuz they are with bidirectional relationship when I do a query to 1 table all foreign keys work and my db querys increase to 15 or more (all my entities so, how can I reduce the number of querys? – EsopMx Apr 20 '14 at 17:36
  • Don't join what you don't need.. and look into lazy loading hydration. – PressingOnAlways Apr 21 '14 at 04:28

0 Answers0