Symfony Profiler

SELECT
  t0.id AS id_1,
  t0.nom AS nom_2,
  t0.prenom AS prenom_3,
  t0.annee_naissance AS annee_naissance_4,
  t0.annee_mort AS annee_mort_5,
  t0.fonction AS fonction_6,
  t0.commentaire AS commentaire_7,
  t0.url_id AS url_id_8
FROM
  personne t0
WHERE
  t0.id = ?

SELECT t0.id AS id_1, t0.nom AS nom_2, t0.prenom AS prenom_3, t0.annee_naissance AS annee_naissance_4, t0.annee_mort AS annee_mort_5, t0.fonction AS fonction_6, t0.commentaire AS commentaire_7, t0.url_id AS url_id_8 FROM personne t0 WHERE t0.id = 2335;

SELECT
  t0.collection_id AS collection_id_1,
  t0.directeur_id AS directeur_id_2
FROM
  collection_directeur t0
WHERE
  t0.directeur_id = ?

SELECT t0.collection_id AS collection_id_1, t0.directeur_id AS directeur_id_2 FROM collection_directeur t0 WHERE t0.directeur_id = 2335;

SELECT
  DISTINCT d.id,
  CASE WHEN d.annee IS NOT NULL
  AND d.annee <> 0 THEN d.annee ELSE d2.annee END AS date,
  d.type_id AS TYPE,
  STRING_AGG(
    CAST(dl.langue_id AS VARCHAR),
    ' '
    ORDER BY
      dl.langue_id ASC
  ) AS langues,
  d.editeur_id AS editeur,
  d.collection_id AS collection,
  regexp_replace(
    regexp_replace(
      LOWER(
        unaccent(d.titre)
      ),
      '[[:punct:]]',
      '',
      'g'
    ),
    '^\s+',
    '',
    'g'
  ) AS titre_formate,
  roles,
  paratextes
FROM
  document d
  LEFT JOIN document_langue dl ON d.id = dl.document_id
  LEFT JOIN infos_element el ON d.infos_element_id = el.id
  LEFT JOIN document d2 ON el.doc_hote_id = d2.id
  LEFT JOIN (
    SELECT
      aut.document_id,
      STRING_AGG(
        CAST(aut.role_id AS VARCHAR),
        ' '
        ORDER BY
          aut.role_id ASC
      ) AS roles
    FROM
      document_autorite aut
    WHERE
      aut.personne_id = ?
    GROUP BY
      aut.document_id
  ) aut_roles ON d.id = aut_roles.document_id
  LEFT JOIN (
    SELECT
      para.document_id,
      STRING_AGG(
        CAST(para.paratexte_id AS VARCHAR),
        ' '
        ORDER BY
          para.paratexte_id ASC
      ) AS paratextes
    FROM
      document_paratexte para
    WHERE
      para.personne_id = ?
    GROUP BY
      para.document_id
  ) para_paratextes ON d.id = para_paratextes.document_id
WHERE
  d.validation = 1
  AND aut_roles.document_id IS NOT NULL
  OR para_paratextes.document_id IS NOT NULL
GROUP BY
  d.id,
  date,
  TYPE,
  editeur,
  collection,
  titre_formate,
  roles,
  paratextes
ORDER BY
  titre_formate ASC,
  date DESC

SELECT DISTINCT
	d.id,
	CASE WHEN d.annee IS NOT NULL AND d.annee <> 0 THEN d.annee ELSE d2.annee END AS date,	
	d.type_id AS TYPE,
	STRING_AGG(CAST(dl.langue_id AS VARCHAR),' ' ORDER BY dl.langue_id ASC) AS langues,
	d.editeur_id AS editeur,
	d.collection_id AS collection,
	regexp_replace(regexp_replace(LOWER(unaccent(d.titre)), '[[2335:]]', '', 'g'), '^\s+', '', 'g') AS titre_formate,
	roles, paratextes
FROM document d
LEFT JOIN document_langue dl ON d.id = dl.document_id
LEFT JOIN infos_element el ON d.infos_element_id = el.id
LEFT JOIN document d2 ON el.doc_hote_id = d2.id
LEFT JOIN (SELECT aut.document_id, STRING_AGG(CAST(aut.role_id AS VARCHAR), ' ' ORDER BY aut.role_id ASC) AS roles
    FROM document_autorite aut
    WHERE aut.personne_id = 2335
    GROUP BY aut.document_id
) aut_roles ON d.id = aut_roles.document_id
LEFT JOIN (SELECT para.document_id, STRING_AGG(CAST(para.paratexte_id AS VARCHAR), ' ' ORDER BY para.paratexte_id ASC) AS paratextes
    FROM document_paratexte para
    WHERE para.personne_id = ?
    GROUP BY para.document_id
) para_paratextes ON d.id = para_paratextes.document_id
WHERE d.validation = 1 AND aut_roles.document_id IS NOT NULL OR para_paratextes.document_id IS NOT NULL
GROUP BY d.id, date, TYPE, editeur, collection, titre_formate, roles, paratextes
ORDER BY titre_formate ASC, date DESC;

SELECT
  DISTINCT id,
  titre,
  comp_titre,
  TYPE,
  fascicule,
  cahier,
  volume,
  tome,
  titre_formate,
  date,
  editeur,
  collection,
  id_hote,
  titre_hote,
  volume_hote,
  tome_hote,
  fascicule_hote,
  cahier_hote,
  discipline_fr,
  discipline_en,
  type_these,
  directeur,
  codirecteur,
  STRING_AGG(
    CAST(langue AS VARCHAR),
    ' '
  ) AS langue_id,
  pos
FROM
  (
    SELECT
      d.id,
      d.titre,
      d.comp_titre,
      d.type_id AS TYPE,
      d.fascicule,
      d.cahier,
      d.volume,
      d.tome,
      regexp_replace(
        regexp_replace(
          LOWER(
            unaccent(d.titre)
          ),
          '[[:punct:]]',
          '',
          'g'
        ),
        '^\s+',
        '',
        'g'
      ) AS titre_formate,
      CASE WHEN d.annee IS NOT NULL
      AND d.annee <> 0 THEN d.annee ELSE d2.annee END AS date,
      -- EDITEUR ET COLLECTION --
      e.nom AS editeur,
      c.nom AS collection,
      -- INFOS ELEMENT --
      d2.id AS id_hote,
      CASE WHEN d2.id IS NOT NULL THEN d2.titre ELSE NULL END AS titre_hote,
      CASE WHEN d2.id IS NOT NULL THEN d2.volume ELSE NULL END AS volume_hote,
      CASE WHEN d2.id IS NOT NULL THEN d2.tome ELSE NULL END AS tome_hote,
      CASE WHEN d2.id IS NOT NULL THEN d2.fascicule ELSE NULL END AS fascicule_hote,
      CASE WHEN d2.id IS NOT NULL THEN d2.cahier ELSE NULL END AS cahier_hote,
      -- INFOS THESE --
      th.discipline_fr,
      th.discipline_en,
      th.type_these,
      CONCAT(p1.prenom, ' ', p1.nom) AS directeur,
      STRING_AGG(
        CONCAT(p2.prenom, ' ', p2.nom),
        ', '
      ) AS codirecteur,
      STRING_AGG(
        CAST(dl.langue_id AS VARCHAR),
        ' '
      ) AS langue,
      ARRAY_POSITION(ARRAY[2764, 2765, 2766], d.id) AS pos
    FROM
      document d
      LEFT JOIN editeur e ON d.editeur_id = e.id
      LEFT JOIN collections c ON d.collection_id = c.id
      LEFT JOIN infos_element el ON d.infos_element_id = el.id
      LEFT JOIN document d2 ON el.doc_hote_id = d2.id
      LEFT JOIN infos_these th ON d.infos_these_id = th.id
      LEFT JOIN personne p1 ON th.directeur_id = p1.id
      LEFT JOIN codirection_these cdt ON th.id = cdt.these_id
      LEFT JOIN personne p2 ON cdt.codirecteur_id = p2.id
      LEFT JOIN document_langue dl ON d.id = dl.document_id
    WHERE
      d.validation = 1
      AND d.id IN (2764, 2765, 2766)
    GROUP BY
      d.id,
      d.titre,
      d.comp_titre,
      d.type_id,
      d.fascicule,
      d.cahier,
      d.volume,
      d.tome,
      titre_formate,
      date,
      editeur,
      collection,
      id_hote,
      titre_hote,
      volume_hote,
      tome_hote,
      fascicule_hote,
      cahier_hote,
      th.discipline_fr,
      th.discipline_en,
      th.type_these,
      directeur,
      pos,
      langue_id
  ) AS req
GROUP BY
  id,
  titre,
  comp_titre,
  TYPE,
  fascicule,
  cahier,
  volume,
  tome,
  titre_formate,
  date,
  editeur,
  collection,
  id_hote,
  titre_hote,
  volume_hote,
  tome_hote,
  fascicule_hote,
  cahier_hote,
  discipline_fr,
  discipline_en,
  type_these,
  directeur,
  codirecteur,
  pos
ORDER BY
  pos

SELECT DISTINCT id, titre, comp_titre, TYPE, fascicule, cahier, volume, tome,
	titre_formate, date, editeur, collection,
	id_hote, titre_hote, volume_hote, tome_hote, fascicule_hote, cahier_hote,
	discipline_fr, discipline_en, type_these, directeur, codirecteur,
	STRING_AGG(CAST(langue AS VARCHAR), ' ') AS langue_id, pos
FROM (
	SELECT d.id, d.titre, d.comp_titre, d.type_id AS TYPE, d.fascicule, d.cahier, d.volume, d.tome, 
	regexp_replace(regexp_replace(LOWER(unaccent(d.titre)), '[[:punct:]]', '', 'g'), '^\s+', '', 'g') AS titre_formate,
	CASE WHEN d.annee IS NOT NULL AND d.annee <> 0 THEN d.annee ELSE d2.annee END AS date,
	-- EDITEUR ET COLLECTION --
	e.nom AS editeur, c.nom AS collection, 
	-- INFOS ELEMENT --
	d2.id AS id_hote, 
	CASE WHEN d2.id IS NOT NULL THEN d2.titre ELSE NULL END AS titre_hote,
	CASE WHEN d2.id IS NOT NULL THEN d2.volume ELSE NULL END AS volume_hote,
	CASE WHEN d2.id IS NOT NULL THEN d2.tome ELSE NULL END AS tome_hote,
	CASE WHEN d2.id IS NOT NULL THEN d2.fascicule ELSE NULL END AS fascicule_hote,
	CASE WHEN d2.id IS NOT NULL THEN d2.cahier ELSE NULL END AS cahier_hote,
	-- INFOS THESE --
	th.discipline_fr, th.discipline_en, th.type_these, CONCAT(p1.prenom,' ',p1.nom) AS directeur, STRING_AGG(CONCAT(p2.prenom,' ',p2.nom), ', ') AS codirecteur,
	STRING_AGG(CAST(dl.langue_id AS VARCHAR), ' ') AS langue,
	ARRAY_POSITION(ARRAY[2764,2765,2766], d.id) AS pos
	FROM document d
	LEFT JOIN editeur e ON d.editeur_id = e.id
	LEFT JOIN collections c ON d.collection_id = c.id
	LEFT JOIN infos_element el ON d.infos_element_id = el.id
	LEFT JOIN document d2 ON el.doc_hote_id = d2.id
	LEFT JOIN infos_these th ON d.infos_these_id = th.id
	LEFT JOIN personne p1 ON th.directeur_id = p1.id
	LEFT JOIN codirection_these cdt ON th.id = cdt.these_id
	LEFT JOIN personne p2 ON cdt.codirecteur_id = p2.id
	LEFT JOIN document_langue dl ON d.id = dl.document_id 
	WHERE d.validation = 1
	AND d.id IN (2764,2765,2766)
	GROUP BY d.id, d.titre, d.comp_titre, d.type_id, d.fascicule, d.cahier, d.volume, d.tome, titre_formate,
		date, editeur, collection, id_hote, titre_hote, volume_hote, tome_hote, fascicule_hote, cahier_hote,
		th.discipline_fr, th.discipline_en, th.type_these, directeur, pos, langue_id
) AS req
GROUP BY id, titre, comp_titre, TYPE, fascicule, cahier, volume, tome,
	titre_formate, date, editeur, collection, id_hote, titre_hote, volume_hote,
	tome_hote, fascicule_hote, cahier_hote, discipline_fr, discipline_en,
	type_these, directeur, codirecteur, pos
ORDER BY pos;

SELECT
  document_id,
  STRING_AGG(
    CONCAT(p.prenom, ' ', p.nom),
    ', '
    ORDER BY
      aut.rang
  ) AS identite
FROM
  document_autorite aut
  INNER JOIN personne p ON aut.personne_id = p.id
  INNER JOIN document d ON aut.document_id = d.id
WHERE
  aut.document_id IN (2764, 2765, 2766)
  AND (
    (
      d.type_id IN (1, 2, 4, 6, 8)
      AND aut.role_id = 1
    )
    OR (
      d.type_id IN (3, 7)
      AND aut.role_id = 2
    )
  )
GROUP BY
  document_id

SELECT document_id, STRING_AGG(CONCAT(p.prenom,' ',p.nom), ', ' ORDER BY aut.rang) AS identite
FROM document_autorite aut
INNER JOIN personne p ON aut.personne_id = p.id
INNER JOIN document d ON aut.document_id = d.id
WHERE aut.document_id IN (2764,2765,2766)
AND ( (d.type_id IN (1,2,4,6,8) AND aut.role_id = 1)
	OR (d.type_id IN (3,7) AND aut.role_id = 2))
GROUP BY document_id;