Symfony Profiler

SELECT
  t0.id AS id_1,
  t0.nom AS nom_2,
  t0.adresse AS adresse_3,
  t0.id_isbn AS id_isbn_4,
  t0.email AS email_5,
  t0.url_id AS url_id_6
FROM
  editeur t0
WHERE
  t0.id = ?

SELECT t0.id AS id_1, t0.nom AS nom_2, t0.adresse AS adresse_3, t0.id_isbn AS id_isbn_4, t0.email AS email_5, t0.url_id AS url_id_6 FROM editeur t0 WHERE t0.id = 73;

SELECT
  DISTINCT c.id,
  c.nom,
  c.issn,
  c.e_issn,
  u.url,
  regexp_replace(
    regexp_replace(
      LOWER(
        unaccent(c.nom)
      ),
      '[[:punct:]]',
      '',
      'g'
    ),
    '^\s+',
    '',
    'g'
  ) AS nom_formate
FROM
  collections c
  LEFT JOIN url u ON c.url_id = u.id
WHERE
  c.editeur_id = ?
ORDER BY
  nom_formate ASC

SELECT DISTINCT c.id, c.nom, c.issn, c.e_issn, u.url, 
    regexp_replace(regexp_replace(LOWER(unaccent(c.nom)), '[[73:]]', '', 'g'), '^\s+', '', 'g') AS nom_formate
FROM collections c
LEFT JOIN url u ON c.url_id = u.id
WHERE c.editeur_id = ?
ORDER BY nom_formate ASC;

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,
  '' AS roles,
  '' AS paratextes,
  regexp_replace(
    regexp_replace(
      LOWER(
        unaccent(d.titre)
      ),
      '[[:punct:]]',
      '',
      'g'
    ),
    '^\s+',
    '',
    'g'
  ) AS titre_formate
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
WHERE
  d.validation = 1
  AND d.editeur_id = ?
GROUP BY
  d.id,
  date,
  TYPE,
  editeur,
  collection,
  titre_formate
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,
	'' AS roles, '' AS paratextes,
	regexp_replace(regexp_replace(LOWER(unaccent(d.titre)), '[[73:]]', '', 'g'), '^\s+', '', 'g') AS titre_formate
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
WHERE d.validation = 1 AND d.editeur_id = ?
GROUP BY d.id, date, TYPE, editeur, collection, titre_formate
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[524, 328, 2532, 3291, 507, 530,
        2767, 331, 330, 2434, 529, 527, 522,
        526, 525], 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 (
        524, 328, 2532, 3291, 507, 530, 2767,
        331, 330, 2434, 529, 527, 522, 526, 525
      )
    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[524,328,2532,3291,507,530,2767,331,330,2434,529,527,522,526,525], 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 (524,328,2532,3291,507,530,2767,331,330,2434,529,527,522,526,525)
	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 (
    524, 328, 2532, 3291, 507, 530, 2767,
    331, 330, 2434, 529, 527, 522, 526, 525
  )
  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 (524,328,2532,3291,507,530,2767,331,330,2434,529,527,522,526,525)
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;