[Résolu] PostgreSQL : regrouper plusieurs requêtes

Bonsoir,
Je profite d'un regain d'intérêt pour le SQL sur le forum actuellement, en posant mes propres questions (je suis vraiment novice en la matière) : est-il possible de fusionner ces deux requêtes afin qu'elles ne fassent qu'une seule ?

-- Sélectionner un article avec l'ID 1
SELECT * FROM __post WHERE _id = 1;

-- Sélectionner l'auteur de ce même article :
SELECT __person._given_name, __person._family_name
FROM __post
INNER JOIN __person
ON __post._author_id = __person._id AND __post._id = 1;

Je dois pouvoir bidouiller avec mon interface pour Postgres, mais je me disais qu'une seule requête serait mieux optimisée...

À titre informatif, les tables interrogées :

CREATE TABLE __post (
  _id                 BIGSERIAL         NOT NULL,
  _name               VARCHAR(255)      NOT NULL,                  -- titre
  _content            TEXT              NULL,                      -- contenu
  _creation           TIMESTAMP         DEFAULT CURRENT_TIMESTAMP, -- date de création -- DEFAULT CURRENT_DATE
  _revision           TIMESTAMP         NULL,                      -- date de révision
  _type               VARCHAR(255)      NULL,                      -- article, page, etc...
  _slug               VARCHAR(255)      NULL,                      -- slug propre à l'article, différent de l'url canonique, celle-ci étant reconstituée à partir le l'index par exemple
  _description        VARCHAR           NULL,                      -- contenu utilisé pour la balise meta description
  _author_id          BIGINT            NOT NULL,                  -- créateur du post (contributeur principal)
  _status             SMALLINT          NOT NULL,                  -- publié, brouillon, refusé, poubelle
  _comments_status    BOOLEAN           NULL,                      -- commentaires activés ou non
  _keywords           VARCHAR(255)      NULL,                      -- mots-clefs pour le post
  _medias             VARCHAR(255)      NULL,                      -- medias en lien avec le post
  CONSTRAINT __post_pkey PRIMARY KEY (_id),
  CONSTRAINT __author_id__person_fkey FOREIGN KEY (_author_id) REFERENCES __person(_id)
);

CREATE TABLE __person (
  _id                 BIGSERIAL         NOT NULL,
  _account_id         BIGINT            NULL,                      -- référence éventuelle à __account
  _sexe               CHAR(1)           NULL,                      -- selon la norme ISO/IEC 5218 ; inconnu : 0, homme : 1, femme : 2, non applicable : 9
  _given_name         VARCHAR(32)       NULL,                      -- prénom
  _additional_name    VARCHAR(32)       NULL,                      -- deuxième prénom
  _family_name        VARCHAR(32)       NULL,                      -- nom de famille
  _usual_name         VARCHAR(32)       NULL,                      -- nom d'usage, nom d'épouse
  _nickname           VARCHAR(32)       NULL,                      -- surnom
  _prefix             VARCHAR(32)       NULL,                      -- titres et civilité
  _suffix             VARCHAR(32)       NULL,                      -- abréviation typographique postname (ex : s.j., o.p.)
  _birth_date         DATE              NULL,                      -- date de naissance
  -- [etc, je simplifie un peu, ici c'est pour l'exemple...]
  _description        TEXT              NULL,                      -- 800 caractères max
  CONSTRAINT __person_pkey PRIMARY KEY (_id)
);

Modifié par Olivier C (21 Feb 2022 - 07:04)

Et l'eau,

Je ne comprends pas réellement ta question.
Je vois ceci (Je fais fausse route peut être ?) :

SELECT 
    pe._given_name, 
    pe._family_name 
FROM 
    __post po
INNER JOIN 
    __person pe
ON 
    po._author_id = pe._id
WHERE 
    po.id = 1;

Il faudrait que tu expliques ce que tu attends comme données. Sinon, je vois l'utilisation de UNION ou LEFT/RIGHT JOIN.
**attention** il y a une contrainte non négligeable à utiliser UNION

ps : pourquoi des caractères soulignés ?
Modifié par niuxe (19 Feb 2022 - 22:47)

Bonsoir Niuxe. Les caractères soulignés (un ou deux underscores selon colonne ou table) sont des préfixes qui me permettent d'utiliser tous les termes sans restrictions, sans me soucier du risque d'utiliser un mot réservé pour mes tables ou mes colonnes. Je connais la liste des mots réservés, mais elle peut évoluer au fil des versions, et puis je n'aime pas les restrictions (et je n'aimais pas non plus la possibilité alternative de mettre des guillemets partout).

Je cherche donc à avoir tous les résultats d'un article (table __post), comme avec cette requête :

SELECT * FROM __post WHERE _id = 1; -- bien sûr, dans la vraie vie, l'ID est appelé dynamiquement dans une requête préparée

... tout en bénéficiant dans le même appel du nom explicite de l'auteur présent sur une autre table (__person), comme avec cette requête :

SELECT __person._given_name, __person._family_name
FROM __post
INNER JOIN __person
ON __post._author_id = __person._id;

Je crois que c'est possible par le biais de mon interface, pg_promise, mais je préférerais le faire uniquement en SQL si possible.
Modifié par Olivier C (24 Feb 2022 - 04:33)

J'ai adapté ta requête Niuxe :

SELECT 
    __person._given_name, // les alias n'étaient pas encore déclarés à ce niveau
    __person._family_name // idem
FROM 
    __post po
INNER JOIN 
    __person pe
ON 
    po._author_id = pe._id
WHERE 
    po.id = 1;

Elle ne fonctionne pas en l'état :

SELECT : commande introuvable
__person._given_name, : commande introuvable
__person._family_name : commande introuvable
FROM : commande introuvable
__post : commande introuvable
INNER : commande introuvable
__person : commande introuvable
ON : commande introuvable
po._author_id : commande introuvable
WHERE : commande introuvable
po.id : commande introuvable

... mais je pense quand-même que tu me donnes le début de la solution avec "ON". Je vais lire un peu la documentation de ce côté-là. Merci.
Modifié par Olivier C (19 Feb 2022 - 23:40)

Olivier C a écrit :

Je cherche donc à avoir tous les résultats d'un article (table __post), comme avec cette requête :

SELECT * FROM __post WHERE _id = 1; -- bien sûr, dans la vraie vie, l'ID est appelé dynamiquement dans une requête préparée

Tu dois obtenir qu'une seule ligne avec ce genre de requête. Mais suivant la requête que tu veux mettre en place après (INNER JOIN), tu risques de ne pas trouver de résultat (where post.id = 1).

Si tu souhaites récupérer les posts communs ou non, LEFT/RIGHT JOIN (chaque fois, je confonds le sens). Jette un coup d'oeil à ce lien : SQL LEFT JOIN

Je ne sais pas si j'ai été explicite. Je commence à comprendre ton souci. Mais je ne suis pas sûr que mon explication soit correcte.

ps : Debian ? Il me semble que ça fait 15 jours que tu l'utilises ? Qu'en est-il ? adopté ?
Modifié par niuxe (19 Feb 2022 - 23:52)

Effectivement, tu as raison, la deuxième requête induisait en erreur : je récupérais tous les auteurs de tous les articles, alors que je ne veux récupérer que l'auteur de l'article de la première requête.

Édit : nos messages se sont croisés. Je me documente demain et te ferais un retour, que je trouve ou non. Là, maintenant... dodo !
Modifié par Olivier C (20 Feb 2022 - 00:24)

J'ai corrigé la deuxième requête et modifié le post initial en conséquence.

Donc, pour reformuler un peu ma demande, comment puis-je fusionner ces deux requêtes ? :

-- Sélectionner l'article avec l'ID n°1 :
SELECT * FROM __post WHERE _id = 1;

-- Sélectionner l'auteur de ce même article :
SELECT __person._given_name, __person._family_name
FROM __post
INNER JOIN __person
ON __post._author_id = __person._id AND __post._id = 1;

Edit @Niuxe : pour Debian, je le réserverais pour le dev', je garde tout de même Ubuntu comme environnement de bureau. Du coup comme je fais tourner Debian dans une VM ce n'est pas top, surtout pour l'accès aux dossier du bureau, à terme je me pencherais sur Docker sur lequel je lorgne depuis longtemps. Mais bon, c'est encore un truc de plus à maîtriser...
Modifié par Olivier C (20 Feb 2022 - 21:58)

Mince alors ! ça marche !
Merci infiniment Mathieu, je vais devoir potasser un peu ta solution pour l'intégrer.
Merci encore. Et bonne soirée.

De rien, le forum ça sert à ça
Tu n'étais pas loin, une fois que tu as compris le mécanisme tu peux faire beaucoup de choses, genre des jointures de jointures (par exemple en ajoutantdans ta requête ci-dessus un truc du genre INNER JOIN __emails ON __person._email_id = __emails._email_id) ou même faire deux jointures différentes sur la même table (si tu dois récupérer 2 personnes différentes sur le même post, par exemple un auteur et un modérateur)...
Bonne soirée !

Mathieu8337 a écrit :
Tu n'étais pas loin...

Et oui, mais lorsque l'on tâtonne dans l’obscurité on peut être proche de la sortie sans jamais la trouver... Tu viens de me débloquer en m'ouvrant la voie pour beaucoup de requêtes du même genre. Merci infiniment.