[Résolu] Autre problème de SQL

Bonjour à tous
Voici un autre schéma de données

Il s'agit d'un programme de travail du chœur
De façon très simplifiée un "programme" a d'une part un nombre quelconque de "documents" attachés et un certain nombre d'"œuvres" qui le constituent.
"documents" et "œuvres" sont des objets qui n'ont en commun que le fait d'être rattachés au même programme.
En mode database, j'ai fait
- la table des programmes
- la table des œuvres
- la table des "éléments de programme" qui permet de rattacher une œuvre à un programme, sachant que la même œuvre peut se retrouver dans des programmes différents, voire plusieurs fois dans le même programme
- la table des "documents de programme" qui permet de rattacher une à un programme un document défini par son URL

Le problème c'est quand je voudrais en UNE SEULE requête obtenir tous les éléments d'un programme.
Ce que je voudrais obtenir et que ma connaissance fragmentaire du SQL ne me permet pas c'est une suite de lignes contenant les infos des documents suivi d'une suite de lignes contenant les infos des œuvres. Une œuvre ayant souvent plusieurs fichiers attachés (partition, différents enregistrements, traduction du texte, etc.) il y a plusieurs lignes par œuvre.

Pourriez vous me dire comment faire?

Merci de votre aide.
Modifié par PapyJP (28 Jun 2020 - 10:30)

Jean-Pierre-Bruneau a écrit :
Ah mais la je ne sais que te dire

"à ma connaissance" ce n'est pas faisable, mais dans ce domaine en particulier mes connaissances sont pratiquement nulles

La raison de ma demande c'est que sur un autre site j'ai demandé s'il était préférable de faire un longue requête ou plusieurs petite.
La seule personne qui m'a répondu préconisait de faire une seule requête et je me suis perdu là dedans.

Pour quelqu'un qui ne sait (plus) penser qu'en "objets", il est naturel qu'un "objet" corresponde à une table (par exemple une "œuvre" a une vie indépendante d'un "programme" auquel elle contribue).
Par ailleurs il ne semble pas possible en SQL d'avoir des "listes", ce qui introduit la nécessité d'avoir des tables intermédiaires pour réaliser du "many to many', et même d'isoler des éléments qui n'ont a priori pas de raisons de l'être car le "one to many" n'existe pas , il faut faire du "many to one".
Tout cela conduit à une requête relativement (n'exagérons pas!) complexe pour récupérer tous les éléments d'un programme.
Mais là où tout m'a paru partir en fumée c'est qu'un "programme" n'a pas seulement des "œuvres", mais également d'autres choses attachées que pour l’instant j'appelle "documents".
La structure de ces objets n'étant pas du tout la même que celle des œuvres, chaque document ne peut pas donner une ligne similaire à celle produite par une œuvre. J'étais donc contraint de mettre les infos des documents et des œuvres dans des colonnes distinctes avec comme résultat de multiplier les lignes par autant que de documents.
Bref je suis parti dîner hier soir avec un problème, qui s'est clarifié dans mon esprit durant la nuit.
Mon code a l'air de fonctionner assez bien avec plusieurs requêtes (je comprends que c'est ce que tu fais) mais j'ai posé la question sur Alsacreations pour avoir un autre avis sur la question.

Bonjour,
j'avoue que j'ai du mal à visualiser précisément… je me suis écartée de ce domaine…
Mais cela ressemble aux genres de choses qui relèvent des requêtes avec jointures multiples — vu qu'il y a trois tables — voire des requêtes avec sous-requêtes…
Edit : mais peut-être cela ne vous ait pas inconnu…

Modifié par Zelena (28 Jun 2020 - 14:13)

Zelena a écrit :
Bonjour,
j'avoue que j'ai du mal à visualiser précisément… je me suis écartée de ce domaine…
Mais cela ressemble aux genres de choses qui relèvent des requêtes avec jointures multiples — vu qu'il y a trois tables — voire des requêtes avec sous-requêtes…
Edit : mais peut-être cela ne vous ait pas inconnu…

Merci de ta réponse
SI ça t'amuse, la requête principale est

SELECT DISTINCT
	p.progID,
	p.title AS programTitle,
	p.plannedDate AS programDate,
	pi.rank AS rank,
	pi.authorID,
	IF(pi.title IS NULL, w.title, pi.title) AS title,
	IF(w2.title = w.title, null, w2.title) AS mainWorkTitle,
	IF(pi.solo IS NULL, w.solo, pi.solo) AS solo,
	IF(pi.choir IS NULL, w.choir, pi.choir) AS choir,
	IF(pi.instrument IS NULL, w.instrument, pi.instrument) AS instrument,
	pi.comments AS comments,
	w.mvtNum,
	wf.link AS fileLink,
	wf.private AS privateFile,
	wf.type AS fileType,
	wft.id AS fileTypeID,
	wft.public AS publishable,
	wft.learning AS learningFile
FROM ProgItems AS pi
LEFT JOIN Works AS w
	ON w.authorID = pi.authorID AND w.workID = pi.workID AND w.mvtID = pi.mvtID
LEFT OUTER JOIN Works as w2
	ON (w2.authorID = w.authorID AND w2.workID = w.workID AND w2.mvtNum = 0)
JOIN Programs AS p
	ON p.progID = pi.progID
LEFT JOIN WorkFiles AS wf
	ON wf.authorID = w.authorID AND wf.workID = w.workID and wf.mvtID = w.mvtID
LEFT JOIN WorkfileTypes AS wft
	ON wft.type = wf.type
WHERE
	pi.progID = progID
ORDER BY pi.rank, wft.id
LIMIT 1000
;

avec entre autres une jointure d'un fichier sur lui-même.
Mais ça ne permet que de lister les informations concernant UN programme (titre, date) et celles qui concernent les œuvres au programme.
A noter qu'on pourrait modifier la requête pour qu'elle donne les informations de TOUS les programmes, mais ça semble exploser les capacités de la base de données.
Il y a une deuxième requête pour les "documents" attachés à l'ensemble des programmes et une troisième qui permet de récupérer les infos de tous les auteurs.
Le but est de reproduire la page https://www.alma-musica.net/html/private/repertoire.php qui est fabriquée à partir d'une techno complètement différente, le changement de techno, comme je l'ai déjà dit, est dû au fait que je ne peux pas transférer l'administration du site sans passer à la techno que les gens connaissent.
Edit: j'ai fait un essai en retrouvant TOUS les programmes en une seule requête, ça ne fait plus exploser la base de données
Le résultat est donc de n'avoir que 3 requête pour retrouver les données de ma page, je pense qu'on de doit pas pouvoir réduire encore ce nombre. Et du reste quel serait l’avantage ???

Modifié par PapyJP (28 Jun 2020 - 15:50)

Salut,
Habituellement, les données de la base sont converties en objets par des programmes intermédiaires appelés ORM, comme Doctrine que l'on retrouve dans les frameworks comme Symfony ou Laravel.

Que ces données soient récupérées avec une ou 50 requêtes dépend de la quantité de données et de l'infrastructure matérielle. Avec une requête, la charge sera plutôt sur PHP puisqu'il devra traiter un grand tableau pour le convertir en objets. Avec plusieurs requête, la latence viendra plutôt du réseau ou de la base, car il faudra faire de nombreux aller-retour entre php et la base de données. Le choix dépend donc du contexte.

Tu ne pourras pas récupérer des lignes de natures différentes. Il faut voir le résultat d'une requête comme un tableau avec des colonnes et chaque colonnes doit se trouver sur chaque ligne. Même s'il existe des solutions pour remplir des cellules avec des listes ou des tableaux sous forme de chaîne, c'est du bricolage pour cacher le fait que le SQL n'est pas adapté à travailler autrement qu'avec de simples tableaux.

Dans ton cas, la question est de savoir pourquoi tu dois afficher autant d'information sur une même page. Souvent la solution vient d'une reformulation du problème. Ta page est protégée par mot de passe, donc non accessible.

Par ailleurs, ton schéma n'est pas complet, il manque des cardinalités d'un côté des relations. Selon les cardinalités, la construction des tables sera différente.

Pardon, j'ai donné la page côté "privé" il existe la même page (avec des infos en moins) côté "public": https://www.alma-musica.net/html/repertoire.php
Cette page est celle qui est la plus longue et je pense qu'elle est rarement utilisée, mais c'est pour cela que je l'ai choisie: qui peut le plus peut le moins.

Pour la cardinalité, effectivement il est tellement évident pour moi que c'est "1,1" que je ne les pas indiquées.
Mes excuses, ça fait plus de 30 ans que je n'ai pas pratiqué ce genre de schéma, et encore je ne les faisais pas moi-même, c'était simplement des moyens de discussion que je savais lire sans les créer moi-même
Je pense que ceci doit être un peu moins faux:

Comme on le voit, un "élément de programme" est un objet intermédiaire permettant de faire du "many to many", mais également des modifications à la marge: une même œuvre peut être donnée parfois avec accompagnement l'orgue dans une église, de piano dans une petite salle de concert, d'orchestre dans des circonstances exceptionnelles. De telles modifications sont indiquées dans les objets "élément de programme".

J'ai pas mal cafouillé hier, cela faisait très longtemps que je n'avais pas pratiqué le SQL et je ne l'avais pas pratiqué beaucoup à cette époque. Mon savoir faire était plutôt dans les systèmes d'exploitation (à cette époque en en faisait beaucoup, c'était avant que tout le monde ou presque adopte UNIX), les protocoles d'échanges entre systèmes, et autres choses de même nature.

edit: en ce qui concerne les "tables hétérogènes", c'est une erreur de ma part: mon correspondant m'avait dit qu'il était préférable de ne faire qu'une seule requête et j'ai désespérément tenté de faire quelque chose qui n'était pas possible. Ce n'est sans doute pas ce qu'il voulait réellement dire, je suppose, mais il ne s'est plus manifesté depuis.
Modifié par PapyJP (28 Jun 2020 - 19:32)