Symfony Profiler

SELECT

  /*l.id, l.nom,*/
  (
    SELECT
      count(*)
    FROM
      resultat r1
      inner join question q1 on r1.question_id = q1.id
      inner join type_msg t1 ON r1.type_id = t1.id
      INNER JOIN poste p1 ON r1.poste_id = p1.id
      INNER JOIN lieu l1 ON p1.lieu_id = l1.id
    WHERE
      valeur <> 2
      AND q1.genre = :boolean
      and t1.code IS NULL
      AND l1.lft >= :lft
      AND l1.rgt <= :rgt
  ) as oui,
  (
    SELECT
      count(*)
    FROM
      resultat r1
      INNER JOIN type_msg t1 ON r1.type_id = t1.id
      INNER JOIN poste p1 ON r1.poste_id = p1.id
      INNER JOIN lieu l1 ON p1.lieu_id = l1.id
    WHERE
      valeur = 2
      AND t1.code IS NULL
      AND l1.lft >= :lft
      AND l1.rgt <= :rgt
  ) as non,
  round(
    (
      (
        SELECT
          count(*)
        FROM
          resultat r1
          inner join question q1 on r1.question_id = q1.id
          inner join type_msg t1 ON r1.type_id = t1.id
          INNER JOIN poste p1 ON r1.poste_id = p1.id
          INNER JOIN lieu l1 ON p1.lieu_id = l1.id
        WHERE
          valeur <> 2
          AND q1.genre = :boolean
          and t1.code IS NULL
          AND l1.lft >= :lft
          AND l1.rgt <= :rgt
      )
    )* 100 / (
      (
        SELECT
          count(*)
        FROM
          resultat r1
          inner join question q1 on r1.question_id = q1.id
          inner join type_msg t1 ON r1.type_id = t1.id
          INNER JOIN poste p1 ON r1.poste_id = p1.id
          INNER JOIN lieu l1 ON p1.lieu_id = l1.id
        WHERE
          valeur <> 2
          AND q1.genre = :boolean
          and t1.code IS NULL
          AND l1.lft >= :lft
          AND l1.rgt <= :rgt
      ) + (
        SELECT
          count(*)
        FROM
          resultat r1
          INNER JOIN type_msg t1 ON r1.type_id = t1.id
          INNER JOIN poste p1 ON r1.poste_id = p1.id
          INNER JOIN lieu l1 ON p1.lieu_id = l1.id
        WHERE
          valeur = 2
          AND t1.code IS NULL
          AND l1.lft >= :lft
          AND l1.rgt <= :rgt
      )
    ),
    2
  ) taux,
  (
    SELECT
      count(DISTINCT pos.id)
    FROM
      resultat r1
      INNER JOIN type_msg tp ON tp.id = r1.type_id
      INNER JOIN poste pos ON r1.poste_id = pos.id
      INNER JOIN lieu lie ON pos.lieu_id = lie.id
    WHERE
      tp.code IS NULL
      and lie.lft >= :lft
      AND lie.rgt <= :rgt
  ) as ouverts,
  (
    SELECT
      COUNT(po2.id)
    FROM
      poste po2
      INNER JOIN lieu li2 ON po2.lieu_id = li2.id
    WHERE
      li2.lft >= :lft
      AND li2.rgt <= :rgt
  ) as postes
  /*FROM lieu l /*WHERE l.lft = :lft AND l.rgt = :rgt*/
LIMIT
  1

SELECT /*l.id, l.nom,*/
                       (SELECT count(*) FROM resultat r1  inner join question q1 on r1.question_id = q1.id inner join 
                           type_msg t1 ON r1.type_id=t1.id
                              INNER JOIN poste p1 ON r1.poste_id=p1.id INNER JOIN lieu l1 ON p1.lieu_id=l1.id
                              WHERE valeur <> 2 AND q1.genre = :boolean and  t1.code IS NULL AND l1.lft >= :lft AND l1.rgt <= :rgt    ) as oui,
                       (SELECT count(*) FROM resultat r1 INNER JOIN type_msg t1 ON r1.type_id=t1.id
                              INNER JOIN poste p1 ON r1.poste_id=p1.id INNER JOIN lieu l1 ON p1.lieu_id=l1.id
                              WHERE valeur = 2 AND  t1.code IS NULL AND l1.lft >= :lft AND l1.rgt <= :rgt    ) as non,
                              
                              round(((SELECT count(*) FROM resultat r1  inner join question q1 on r1.question_id = q1.id inner join 
                           type_msg t1 ON r1.type_id=t1.id
                              INNER JOIN poste p1 ON r1.poste_id=p1.id INNER JOIN lieu l1 ON p1.lieu_id=l1.id
                              WHERE valeur <> 2 AND q1.genre = :boolean and  t1.code IS NULL AND l1.lft >= :lft AND l1.rgt <= :rgt    ))*100
                              /
                              ((SELECT count(*) FROM resultat r1  inner join question q1 on r1.question_id = q1.id inner join 
                           type_msg t1 ON r1.type_id=t1.id
                              INNER JOIN poste p1 ON r1.poste_id=p1.id INNER JOIN lieu l1 ON p1.lieu_id=l1.id
                              WHERE valeur <> 2 AND q1.genre = :boolean and  t1.code IS NULL AND l1.lft >= :lft AND l1.rgt <= :rgt    )
                              +
                              (SELECT count(*) FROM resultat r1 INNER JOIN type_msg t1 ON r1.type_id=t1.id
                              INNER JOIN poste p1 ON r1.poste_id=p1.id INNER JOIN lieu l1 ON p1.lieu_id=l1.id
                              WHERE valeur = 2 AND  t1.code IS NULL AND l1.lft >= :lft AND l1.rgt <= :rgt    )),2) taux,
                       
                              
                       (SELECT count(DISTINCT pos.id) FROM resultat r1 INNER JOIN type_msg tp ON tp.id=r1.type_id INNER JOIN poste pos ON r1.poste_id=pos.id 
                                INNER JOIN lieu lie ON pos.lieu_id=lie.id
                              WHERE tp.code IS NULL     and lie.lft >= :lft AND lie.rgt <= :rgt ) as ouverts,
                       (SELECT COUNT(po2.id) FROM poste po2 INNER  JOIN lieu li2 ON po2.lieu_id = li2.id WHERE li2.lft >= :lft AND li2.rgt <= :rgt) as postes                       
                /*FROM lieu l /*WHERE l.lft = :lft AND l.rgt = :rgt*/ LIMIT 1;

SELECT
  p0_.id AS id_0,
  p0_.idpv AS idpv_1,
  p0_.libelle AS libelle_2,
  p0_.nbi AS nbi_3,
  p0_.nbh AS nbh_4,
  p0_.nbf AS nbf_5,
  p0_.lieu_id AS lieu_id_6,
  p0_.observateur_id AS observateur_id_7
FROM
  poste p0_
WHERE
  p0_.observateur_id IS NOT NULL

SELECT p0_.id AS id_0, p0_.idpv AS idpv_1, p0_.libelle AS libelle_2, p0_.nbi AS nbi_3, p0_.nbh AS nbh_4, p0_.nbf AS nbf_5, p0_.lieu_id AS lieu_id_6, p0_.observateur_id AS observateur_id_7 FROM poste p0_ WHERE p0_.observateur_id IS NOT NULL;

SELECT
  COUNT(r.valeur) as postesOuverts,
  sum(p.nbi) as nbi,
  sum(r.valeur) as nbv,
  (
    SUM(r.valeur)/(
      SUM(p.nbi)
    )
  )* 100 as taux,
  (
    SELECT
      COUNT(po.id)
    FROM
      poste po
      INNER JOIN lieu li ON po.lieu_id = li.id
    WHERE
      li.lft >= :lft
      AND li.rgt <= :rgt
  ) as postes
FROM
  poste p
  LEFT JOIN resultat r ON r.poste_id = p.id
  INNER JOIN lieu l ON p.lieu_id = l.id
WHERE
  r.question_id = :nbv
  AND r.type_id = :par
  AND l.lft >= :lft
  AND l.rgt <= :rgt
  And r.id = (
    select
      id
    FROM
      resultat re
    WHERE
      question_id = r.question_id
      AND re.poste_id = p.id
      AND re.type_id = r.type_id
    ORDER BY
      re.id DESC
    LIMIT
      1
  )

SELECT COUNT(r.valeur) as postesOuverts, sum(p.nbi) as nbi, sum(r.valeur) as nbv, (SUM(r.valeur)/(SUM(p.nbi )))*100 as taux,
                (SELECT COUNT(po.id) FROM poste po INNER  JOIN lieu li ON po.lieu_id = li.id WHERE li.lft >= :lft AND li.rgt <= :rgt) as postes
                FROM  poste p LEFT JOIN resultat r ON r.poste_id =  p.id INNER JOIN lieu l ON p.lieu_id = l.id 
                WHERE r.question_id = :nbv AND r.type_id = :par AND l.lft >= :lft AND l.rgt <= :rgt 
                And r.id = (select id FROM resultat re WHERE question_id = r.question_id AND re.poste_id = p.id AND re.type_id = r.type_id  ORDER BY re.id DESC LIMIT 1)
                ;

SELECT
  p0_.id AS id_0,
  p0_.idpv AS idpv_1,
  p0_.libelle AS libelle_2,
  p0_.nbi AS nbi_3,
  p0_.nbh AS nbh_4,
  p0_.nbf AS nbf_5,
  p0_.lieu_id AS lieu_id_6,
  p0_.observateur_id AS observateur_id_7
FROM
  poste p0_
  INNER JOIN sms s1_ ON p0_.id = s1_.poste_id
WHERE
  s1_.resolution IS NOT NULL

SELECT p0_.id AS id_0, p0_.idpv AS idpv_1, p0_.libelle AS libelle_2, p0_.nbi AS nbi_3, p0_.nbh AS nbh_4, p0_.nbf AS nbf_5, p0_.lieu_id AS lieu_id_6, p0_.observateur_id AS observateur_id_7 FROM poste p0_ INNER JOIN sms s1_ ON p0_.id = s1_.poste_id WHERE s1_.resolution IS NOT NULL;

SELECT
  t0.id AS id_1,
  t0.nom AS nom_2,
  t0.lat AS lat_3,
  t0.lon AS lon_4,
  t0.lft AS lft_5,
  t0.lvl AS lvl_6,
  t0.rgt AS rgt_7,
  t0.nbe AS nbe_8,
  t0.decoupage_id AS decoupage_id_9,
  t0.superviseur_id AS superviseur_id_10,
  t0.circonscription_id AS circonscription_id_11,
  t0.tree_root AS tree_root_12,
  t0.parent_id AS parent_id_13,
  t0.circ_id AS circ_id_14,
  t15.id AS id_16,
  t15.nom AS nom_17,
  t15.prenoms AS prenoms_18,
  t15.adresse AS adresse_19,
  t15.statut AS statut_20,
  t15.created_at AS created_at_21,
  t15.updated_at AS updated_at_22,
  t15.gestionnaire_id AS gestionnaire_id_23,
  t15.user_id AS user_id_24,
  t15.zone_id AS zone_id_25,
  t15.zon_id AS zon_id_26
FROM
  lieu t0
  LEFT JOIN agent t15 ON t15.zone_id = t0.id
WHERE
  t0.nom = ?
LIMIT
  1

SELECT t0.id AS id_1, t0.nom AS nom_2, t0.lat AS lat_3, t0.lon AS lon_4, t0.lft AS lft_5, t0.lvl AS lvl_6, t0.rgt AS rgt_7, t0.nbe AS nbe_8, t0.decoupage_id AS decoupage_id_9, t0.superviseur_id AS superviseur_id_10, t0.circonscription_id AS circonscription_id_11, t0.tree_root AS tree_root_12, t0.parent_id AS parent_id_13, t0.circ_id AS circ_id_14, t15.id AS id_16, t15.nom AS nom_17, t15.prenoms AS prenoms_18, t15.adresse AS adresse_19, t15.statut AS statut_20, t15.created_at AS created_at_21, t15.updated_at AS updated_at_22, t15.gestionnaire_id AS gestionnaire_id_23, t15.user_id AS user_id_24, t15.zone_id AS zone_id_25, t15.zon_id AS zon_id_26 FROM lieu t0 LEFT JOIN agent t15 ON t15.zone_id = t0.id WHERE t0.nom = 'BENIN' LIMIT 1;

SELECT
  t0.id AS id_1,
  t0.code AS code_2,
  t0.genre AS genre_3,
  t0.controller AS controller_4,
  t0.violent AS violent_5,
  t0.ordre AS ordre_6,
  t0.actif AS actif_7
FROM
  type_msg t0
WHERE
  t0.code = ?
LIMIT
  1

SELECT t0.id AS id_1, t0.code AS code_2, t0.genre AS genre_3, t0.controller AS controller_4, t0.violent AS violent_5, t0.ordre AS ordre_6, t0.actif AS actif_7 FROM type_msg t0 WHERE t0.code = 'com' LIMIT 1;

SELECT
  COUNT(*) as incidents
FROM
  type_msg t
  INNER JOIN sms s ON s.type_msg_id = t.id
  INNER JOIN numero n ON s.sender_num_id = n.id
  INNER JOIN agent a ON n.agent_id = a.id
  INNER JOIN poste p ON a.id = p.observateur_id
  INNER JOIN lieu l ON p.lieu_id = l.id
WHERE
  t.code LIKE :alt
  AND l.lft >= :lft
  AND l.rgt <= :rgt
  AND resolution IN (
    "resolu", "résolu", "true", "resolved"
  )

SELECT COUNT(*) as incidents
                FROM type_msg t INNER JOIN sms s ON s.type_msg_id =  t.id INNER JOIN numero n ON s.sender_num_id = n.id
                 INNER JOIN   agent a ON n.agent_id = a.id INNER JOIN poste p ON a.id = p.observateur_id INNER JOIN lieu l ON p.lieu_id = l.id
                WHERE t.code LIKE 'alt%' AND l.lft >= 1 AND l.rgt <= 7164
                 AND resolution IN ("resolu", "résolu", "true", "resolved") ;

SELECT
  t0.id AS id_1,
  t0.libelle AS libelle_2,
  t0.genre AS genre_3,
  t0.code AS code_4,
  t0.modalites AS modalites_5,
  t0.versus AS versus_6,
  t0.alerte AS alerte_7,
  t0.open AS open_8,
  t0.openalert AS openalert_9,
  t0.parent_id AS parent_id_10,
  t0.opoption_id AS opoption_id_11
FROM
  question t0
WHERE
  t0.code = ?
LIMIT
  1

SELECT t0.id AS id_1, t0.libelle AS libelle_2, t0.genre AS genre_3, t0.code AS code_4, t0.modalites AS modalites_5, t0.versus AS versus_6, t0.alerte AS alerte_7, t0.open AS open_8, t0.openalert AS openalert_9, t0.parent_id AS parent_id_10, t0.opoption_id AS opoption_id_11 FROM question t0 WHERE t0.code = 'nbv' LIMIT 1;

SELECT
  COUNT(*) as incidents
FROM
  type_msg t
  INNER JOIN sms s ON s.type_msg_id = t.id
  INNER JOIN numero n ON s.sender_num_id = n.id
  INNER JOIN agent a ON n.agent_id = a.id
  INNER JOIN poste p ON a.id = p.observateur_id
  INNER JOIN lieu l ON p.lieu_id = l.id
WHERE
  t.code LIKE :alt
  AND l.lft >= :lft
  AND l.rgt <= :rgt
  AND resolution is not NULL

SELECT COUNT(*) as incidents
                FROM type_msg t INNER JOIN sms s ON s.type_msg_id =  t.id INNER JOIN numero n ON s.sender_num_id = n.id
                 INNER JOIN   agent a ON n.agent_id = a.id INNER JOIN poste p ON a.id = p.observateur_id INNER JOIN lieu l ON p.lieu_id = l.id
                WHERE t.code LIKE 'alt%' AND l.lft >= 1 AND l.rgt <= 7164
                 AND resolution is not NULL;