8740 sujets

Développement web côté serveur, CMS

Bonjour,

Dans le paramétrage d'une API REST avec PostgreSQL je compose des collections d'objets imbriquées en réponse à mes requêtes SQL sous cette forme*** :
{
  id: 16,
  name: 'Markdown test',
  alternativeHeadline: 'Syntaxe markdown permettant de tester notre parseur',
  slug: 'markdown-test',
  content: 'normal, _italic_, __strong__',
  createdAt: 2022-04-16T17:10:25.000Z,
  modifiedAt: 2022-04-16T18:15:22.000Z,
  type: null,
  description: 'Syntaxe markdown à destination de notre parseur',
  authorId: 1,
  status: 1,
  isRestrictedRead: true,
  isCommentable: false,
  medias: null,
  add: null,
  author: {
    url: null,
    prefix: 'P.',
    suffix: 's.j.',
    nickname: null,
    givenName: 'Henri',
    usualName: null,
    familyName: 'de Lubac',
    additionalName: 'Sonier'
  }
}

Dans un pemier temps, j'avais procédé avec des requêtes SQL successives que je raccrochais les unes aux autres après coup, système très souple en dev' mais pas top pour les perfs je pense (selon les pages j'avais jusqu'à 4 requêtes) :
export async function getArticle(req, res) {
  let data = {}
  const client = await this.pg.connect()
  const query = `
    SELECT *
    FROM __article
    WHERE _slug = $1
    `
  const query2 = `
    SELECT
      __person._given_name,
      __person._additional_name,
      __person._family_name,
      __person._usual_name,
      __person._nickname,
      __person._prefix,
      __person._suffix,
      __person._url
    FROM __account
    LEFT JOIN __person
      ON __person._id = _person_id
    WHERE __account._id = $1
    `
  try {
    const article = await client.query(query, [req.params.slug])
    if (!article.rows[0]) return res.code(404).view('404')
    data = await dataConversion(article.rows[0])
    const author = await client.query(query2, [data.authorId])
    data.author = await dataConversion(author.rows[0])
    modifyData(req, data)
    console.log(data)
    //return data
    return res.view('article', { data })
  } finally {
    client.release()
  }
}

Désormais je ne crée qu'une seule requête qui me donne le même résultat :
export async function getArticle(req, res) {
  let data = {}
  const client = await this.pg.connect()
  const query = `
    SELECT 
      a.*, 
      jsonb_build_object(
        '_given_name', p._given_name,
        '_additional_name', p._additional_name,
        '_family_name', p._family_name,
        '_usual_name', p._usual_name,
        '_nickname', p._nickname,
        '_prefix', p._prefix,
        '_suffix', p._suffix,
        '_url', p._url
      ) AS author
    FROM __article a
    LEFT JOIN __account ac ON a._author_id = ac._id
    LEFT JOIN __person p ON ac._person_id = p._id
    WHERE a._slug = $1
    `
  try {
    const article = await client.query(query, [req.params.slug])
    if (!article.rows[0]) return res.code(404).view('404')
    data = await dataConversion(article.rows[0])
    modifyData(req, data)
    console.log(data)
    //return data
    return res.view('article', { data })
  } finally {
    client.release()
  }
}

Comme vous le voyez, pour arriver à mes fins j'utilise la fonction postgres `jsonb_build_object()` (j'aurais tout aussi bien pu utiliser `json_build_object()`), seulement il me faut redéfinir toutes les clefs pour chaque collections d'objets imbriquées, pas moyen de le faire à la volée...

Y-a-t-il mieux à faire ?

___
*** Ne vous étonnez pas si les dénominations des clefs de la réponse sont en camelCase alors que ces mêmes clefs sont en _snake_case dans les requêtes SQL : je leur fais subir un petit changement de syntaxe avec la fonction dataConversion().
Modifié par Olivier C (29 Mar 2024 - 15:24)
Modérateur
Et l'eau,

En général, un article peut avoir un ou plusieurs auteurs et inversement. Donc, tu as une relation n * n. Sinon, tu peux partir de ce constat : un auteur peut avoir plusieurs articles (natural join/inner join).

Ensuite, je vois tous ces caractères soulignés. Ça nuit à la lisibilité et aucun intérêt. Par contre, il est pertinent de les utiliser pour un namespace.

Quand je vois toute cette série de __person, tu as réussi à me faire saigner des yeux (je pense que tu aimes taper sur le clavier. Pauvre clavier). Regarde de ce côté : alias

Je ne comprends pas trop ton histoire de json. Autant passer par le controller pour faire ce genre de choses (auth)

ps :
- petit tips -> le nom du model au singulier alors que le nom d'une table au pluriel Smiley cligne
- prisma
- TypeORM (attention à sa logique inversée lors des relations entre les models)
Modifié par niuxe (30 Mar 2024 - 16:15)
niuxe a écrit :
En général, un article peut avoir un ou plusieurs auteurs et inversement. Donc, tu as une relation n * n.

Tout à fait, mais je commence doucement et ensuite j'iterrerrai sur mes requêtes une fois maîtrisées.

niuxe a écrit :
Ensuite, je vois tous ces caractères soulignés. Ça nuit à la lisibilité et aucun intérêt. Par contre, il est pertinent de les utiliser pour un namespace.

Ce sont des namespaces justement, qui m'évite en particulier de devoir faire attention à un éventuel télescopage avec des mots réservés. Par exemple "user" ou "read".

niuxe a écrit :
Quand je vois toute cette série de __person, tu as réussi à me faire saigner des yeux (je pense que tu aimes taper sur le clavier. Pauvre clavier). Regarde de ce côté : alias

Les alias, je m'en sers dans le deuxième exemple de requêtes SQL comme tu peux voir. Bref, je sais les utiliser mais, là, mon but n'était pas de peaufiner une requête.

niuxe a écrit :
Je ne comprends pas trop ton histoire de json. Autant passer par le controller pour faire ce genre de choses (auth).

Je cherche à faire des collections d'objets imbriqués. Les fonctions PostgreSQL utilisées pour cette fin sont un moyen comme un autre d'arriver à ce résultat. Et là était la pointe de la question : y a t'il d'autres manières de faire ?

niuxe a écrit :
le nom du model au singulier alors que le nom d'une table au pluriel Smiley cligne
- prisma...

Je connais Prisma et je veux éviter justement.

Pour le nom des tables SQL il y a deux écoles : soit on se places dans la logique d'une collection d'objets, soit dans la logique d'une ligne (row).

J'ai choisi la deuxième option, je trouve qu'elle a du sens au moment de la composition d'une requête. Je garde le pluriel lorsque je propose une collection imbriquées (ex : "roles", "authors"...), ce qui veut dire que ces pluriels ne se trouvent jamais au niveau de ma base données, mais sont des constructions liées à une requête et sont exposées pour le front. Dans une vue on pourra donc faire aisément la différence entre "author", et "authors". Pour le dernier cas on sait que l'on pourra itérer dans une boucle. Un exemple pour une liste avec pug.js :
ul
  each value, key in data.account.capabilities
    - const icon = value ? 'unlocked' : 'locked'
    - const color = value ? '#fff' : 'peru'
    li
      svg.icon-inline(role='img' focusable='false' style='color:' +color)
        use(href='/sprites/util.svg#' + icon)
      = key

À partir du moment où l'on prend une convention et que l'on s'y tient...

Modifié par Olivier C (30 Mar 2024 - 22:38)
Ah non mais c'est pas possible ! Je viens de perdre du temps sur une requête SQL que je venait de refactoriser, que je pensais buggée... et qui était bonne ! Je reviens sur mon code ce soir, à tête reposée, et voilà ce que je trouve : une horreur que j'avais faite plus loin dans le code pour éviter un "undefined" si pas de données pour ma requête précédente :
data.account.capabilities = {}

Deux heures de perdues rien que pour ce bug, vous avez le droit de sortir les bâtons... Smiley smash

Justement, parlons de cette requête car elle produit le type de résultat énoncé plus haut, une collection d'objets avec deux collections imbriquées :
SELECT
  a.*,
  m._url AS _media_url,
  jsonb_build_object(
    '_given_name', p._given_name,
    '_additional_name', p._additional_name,
    '_family_name', p._family_name,
    '_usual_name', p._usual_name,
    '_nickname', p._nickname,
    '_prefix', p._prefix,
    '_suffix', p._suffix,
    '_url', p._url,
    '_nationality', p._nationality,
    '_devise', p._devise
  ) AS person,
  jsonb_build_object(
    '_access_admin', r._access_admin,
    '_create_contents', r._create_contents,
    '_edit_contents', r._edit_contents,
    '_delete_contents', r._delete_contents,
    '_publish_contents', r._publish_contents,
    '_create_comments', r._create_comments,
    '_edit_comments', r._edit_comments,
    '_delete_comments', r._delete_comments,
    '_manage_users', r._manage_users,
    '_manage_groups', r._manage_groups,
    '_manage_contents', r._manage_contents,
    '_manage_tags', r._manage_tags,
    '_manage_menus', r._manage_menus,
    '_upload_files', r._upload_files,
    '_read', r._read
  ) AS capabilities
FROM __account a
LEFT JOIN __media m
  ON m._id = a._media_id
LEFT JOIN __person p
  ON p._id = a._person_id
LEFT JOIN __role r
  ON r._name = a._role_name
WHERE
  a._username = $1

Ce qui produit ce résultat (après quelques formatage sur les dates via des fonctions) :
{
  account: {
    id: 2,
    username: 'Beta',
    password: '$argon2id$v=19$m=65536,t=3,p=4$O7V//bsQ6NBAhMgwoRuz4Q$rL5T4u4qm1sxKz/eDZTpUrfMwxqylEULf26ZYnwZBCA',
    roleName: 'moderator',
    isBanned: false,
    personId: 2,
    displayName: null,
    mediaId: 2,
    language: 'fr_FR',
    isVisible: true,
    community: null,
    siteStyle: null,
    timeZone: null,
    isPrivateMessage: false,
    createdAt: 2005-05-07T17:37:25.000Z,
    modifiedAt: 2017-07-17T05:08:25.000Z,
    lastLoginAt: 2020-05-03T08:10:25.000Z,
    mediaUrl: '/medias/images/uploads/jeanne-d-arc-original.webp',
    person: {
      url: null,
      devise: 'De par le Roy du Ciel !',
      prefix: 'Ste',
      suffix: null,
      nickname: "La Pucelle d'Orléans",
      givenName: 'Jeanne',
      usualName: null,
      familyName: "d'Arc",
      nationality: 'FR',
      additionalName: null
    },
    capabilities: {
      read: true,
      manageTags: true,
      accessAdmin: true,
      manageMenus: false,
      manageUsers: false,
      uploadFiles: true,
      editComments: true,
      editContents: true,
      manageGroups: false,
      createComments: true,
      createContents: true,
      deleteComments: true,
      deleteContents: true,
      manageContents: true,
      publishContents: true
    },
    dateCreatedISO: '2005-05-07',
    dateModifiedISO: '2017-07-17',
    lastLoginISO: '2020-05-03'
  },
  allRoles: [
    'administrator',
    'moderator',
    'editor',
    'author',
    'contributor',
    'commentator',
    'subscriber'
  ],
  url: '/account/Beta',
  name: 'Beta <span>.&nbsp;Compte utilisateur</span>',
  title: 'Compte utilisateurs de Beta',
  description: 'Page de compte utilisateurs de Beta.'
}

Si vous connaissez un système moins verbeux pour les requêtes avec PostgreSQL je suis preneur.
Modifié par Olivier C (30 Mar 2024 - 23:24)