10 |
4.72 ms |
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,
e.nom AS editeur, c.nom AS collection,
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,
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[3170], 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 (3170)
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,
e.nom AS editeur,
c.nom AS collection,
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,
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[3170], 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 (3170)
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,
e.nom AS editeur, c.nom AS collection,
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,
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[3170], 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 (3170)
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;
|