Contexte

La base unique canutes expose les schémas assemblee, legifrance et senat. Cette recherche se fait avec query_sql, en interrogeant explicitement senat.sens_memcom, senat.sens_sen et senat.sens_com.

Pour le pendant Assemblée de cette recette, voyez Rechercher les députés membres d'une commission.

Contrairement au schéma assemblee, il existe ici une table relationnelle dédiée : sens_memcom. Elle porte le lien entre un sénateur (senmat) et une commission (orgcod), avec des dates de début et de fin.

Schéma utile

-- Table des appartenances aux commissions
-- sens_memcom(memcomid, orgcod, senmat, memcomdatdeb, memcomdatfin, temvalcod, ...)

-- Table des sénateurs
-- sens_sen(senmat, quacod, senprenomuse, sennomuse, ...)

-- Table des commissions
-- sens_com(orgcod, typorgcod, comlilmin, evelib, evelil, orgdatcre, orgdatfin, ...)

1. Rechercher tous les sénateurs ayant eu un mandat dans une commission donnée

SELECT
  trim(s.senmat) AS matricule,
  trim(concat_ws(' ', s.quacod, s.senprenomuse, s.sennomuse)) AS nom_complet,
  TO_CHAR(memcom.memcomdatdeb, 'YYYY-MM-DD') AS date_debut,
  TO_CHAR(memcom.memcomdatfin, 'YYYY-MM-DD') AS date_fin,
  trim(COALESCE(com.evelil, com.evelib, com.evelic, com.comlilmin)) AS commission
FROM senat.sens_memcom AS memcom
JOIN senat.sens_sen AS s
  ON s.senmat = memcom.senmat
LEFT JOIN senat.sens_com AS com
  ON com.orgcod = memcom.orgcod
WHERE memcom.orgcod = 'SOCI'
ORDER BY memcom.memcomdatfin DESC NULLS LAST, s.sennomuse ASC;

2. Rechercher les membres actuels uniquement

Le filtre le plus robuste est basé sur les dates de rattachement, pas seulement sur l'état courant du sénateur.

SELECT
  trim(s.senmat) AS matricule,
  trim(concat_ws(' ', s.quacod, s.senprenomuse, s.sennomuse)) AS nom_complet,
  TO_CHAR(memcom.memcomdatdeb, 'YYYY-MM-DD') AS date_debut
FROM senat.sens_memcom AS memcom
JOIN senat.sens_sen AS s
  ON s.senmat = memcom.senmat
WHERE memcom.orgcod = 'SOCI'
  AND (memcom.memcomdatfin IS NULL OR memcom.memcomdatfin::date >= CURRENT_DATE)
ORDER BY s.sennomuse ASC, s.senprenomuse ASC;

3. Rechercher les membres à une date donnée

SELECT
  trim(s.senmat) AS matricule,
  trim(concat_ws(' ', s.quacod, s.senprenomuse, s.sennomuse)) AS nom_complet
FROM senat.sens_memcom AS memcom
JOIN senat.sens_sen AS s
  ON s.senmat = memcom.senmat
WHERE memcom.orgcod = 'SOCI'
  AND (memcom.memcomdatdeb IS NULL OR memcom.memcomdatdeb::date <= DATE '2024-01-15')
  AND (memcom.memcomdatfin IS NULL OR memcom.memcomdatfin::date >= DATE '2024-01-15')
ORDER BY s.sennomuse ASC, s.senprenomuse ASC;

4. Partir d'un nom de commission plutôt que d'un code

SELECT
  trim(com.orgcod) AS commission_code,
  trim(COALESCE(com.evelil, com.evelib, com.evelic, com.comlilmin)) AS commission,
  trim(s.senmat) AS matricule,
  trim(concat_ws(' ', s.quacod, s.senprenomuse, s.sennomuse)) AS nom_complet
FROM senat.sens_memcom AS memcom
JOIN senat.sens_sen AS s
  ON s.senmat = memcom.senmat
JOIN senat.sens_com AS com
  ON com.orgcod = memcom.orgcod
WHERE trim(COALESCE(com.evelil, com.evelib, com.evelic, com.comlilmin)) ILIKE '%affaires sociales%'
  AND (memcom.memcomdatfin IS NULL OR memcom.memcomdatfin::date >= CURRENT_DATE)
ORDER BY commission ASC, s.sennomuse ASC;

5. Compter les membres par commission

SELECT
  trim(memcom.orgcod) AS commission_code,
  trim(COALESCE(com.evelil, com.evelib, com.evelic, com.comlilmin)) AS commission,
  count(*)::int AS nb_membres
FROM senat.sens_memcom AS memcom
LEFT JOIN senat.sens_com AS com
  ON com.orgcod = memcom.orgcod
WHERE memcom.memcomdatfin IS NULL OR memcom.memcomdatfin::date >= CURRENT_DATE
GROUP BY memcom.orgcod, COALESCE(com.evelil, com.evelib, com.evelic, com.comlilmin)
ORDER BY nb_membres DESC, commission ASC;

6. Récupérer aussi les fonctions internes d'un membre

Pour les fonctions comme président, vice-président ou rapporteur au sein d'une commission, il faut enrichir sens_memcom avec sens_fonmemcom et sens_foncom.

Attention : certains enregistrements n'ont pas de libellé de fonction renseigné ; utilisez NULLIF(..., '') si vous voulez ignorer ces valeurs vides.

SELECT
  trim(s.senmat) AS matricule,
  trim(concat_ws(' ', s.quacod, s.senprenomuse, s.sennomuse)) AS nom_complet,
  TO_CHAR(fonmemcom.fonmemcomdatdeb, 'YYYY-MM-DD') AS date_debut_fonction,
  TO_CHAR(fonmemcom.fonmemcomdatfin, 'YYYY-MM-DD') AS date_fin_fonction,
  trim(COALESCE(foncom.foncomlib, foncom.foncomlil, foncom.foncomlic)) AS fonction
FROM senat.sens_memcom AS memcom
JOIN senat.sens_sen AS s
  ON s.senmat = memcom.senmat
JOIN senat.sens_fonmemcom AS fonmemcom
  ON fonmemcom.memcomid = memcom.memcomid
LEFT JOIN senat.sens_foncom AS foncom
  ON foncom.foncomcod = fonmemcom.foncomcod
WHERE memcom.orgcod = 'SOCI'
ORDER BY fonmemcom.fonmemcomdatdeb DESC NULLS LAST, s.sennomuse ASC;

7. Quand élargir à d'autres familles d'organes

Cette recette est spécifique aux commissions via sens_memcom.

• pour les groupes politiques, utilisez sens_memgrppol,
• pour les délégations, utilisez sens_memdelega,
• pour repérer les codes et libellés d'organes, voyez Chercher des organes parlementaires du Sénat.