Discussion :

[Bernard B]

N'ayant aucune expérience dans les bases de données, je vous expose mon problème qui va certainement vous paraître trivial.

Application développée avec Delphi 11.3 Community en 32 bits pour windows en VCL. Base de données MySQL connexion avec les objets FireDac (fonctionne très bien).

J'ai une table d'une base qui contient ID d'un objet pour s'y retrouver dans la base, 3 champs integer qui contiennent des notes de 0 à 20, un champ 'notetotale' que j'ai déjà calculé et un champ integer 'classement' que je veux remplir avec la place de l'objet dans le classement par rapport à notetotale .

Je fais un FDQuery avec une commande SQL pour remplir cette table et je l'ordonne par rapport à 'notetotale'. Ca c'est bon.
Je voudrais maintenant balayer le FDQuery pour calculer le classement et remplir le champ 'classement', je pense pouvoir le faire via FieldByName et/ou FieldValues.

Mais là je n'aurai modifié que la table en mémoire et pas la table de la base (il me semble) comment faire pour enregistrer les modification dans la table ?

Merci.
Mon approche n'est peut-être pas la bonne...

[Bernard B]

Mon approche ne doit pas être la bonne car au premier essai d'écriture d'une place on me dit que mon ensemble de données n'est pas en mode Edition ou Insertion...

[SergioMaster]

Si tu fournissais la structure de la table ?

un champ 'notetotale' que j'ai déjà calculé et un champ integer 'classement'

ces deux champs me choquent, c'est pour cela que j'en fais la demande.

Notetotale c'est N1+N2+N3 donc pas la peine de la stocker.
Il existe en MySQL des fonctions de fenêtrage ( RANK() OVER() ) qui permettrait l'obtention du classement

[ShaiLeTroll]

L'explication n'est pas forcément évidente à comprendre

L'erreur c'est l'absence de Edit() ou Insert() qui provoquera une erreur sur la modification d'un champ

Soit tu passes par Edit / FieldByName / Post (et cela utilise les mécaniques de mise à jour de FireDAC)
Soit tu passes par un AUTRE SQLde type UPDATE SET WHRE s'utilise via ParamByName et non FieldByName


Et effectivement, ce travail doit pouvoir se faire entièrement en SQL sans devoir coder de boucle en Delphi

[Bernard B]

La structure de la table est très simple :


Le total pourraient être calculé à la volée effectivement mais j'avance pas à pas !

Je vais essayer de voir les fonctions RANK et OVER de Sergio, mais j'ai encore beaucoup de mal à créer mes commandes SQL.

Edit / FieldByName / Post cela me rappelle des choses ce sera peut être ma solution.

Merci, je verrai ça demain !!

[SergioMaster]

C'est bien ce que je pensais, d'un point de vue administrateur de base de données, trois colonnes sont inutiles et même potentiellement peuvent être fausses !

Ce premier SQL pour montrer comment obtenir deux de ces colonnes (totalcalcule, Ecart)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT  Photo_id,note1,note2,note3,note1+note2+note3 AS totalcalcule,GREATEST(note1,note2,note3)-LEAST(note1,note2,note3) AS Ecart
FROM notes

- En lettres capitales, les mots clés
AS, est optionnel, par contre nommer l'alias est fortement recommandé
GREATEST
LEAST

Le Rang est un peu plus complexe (je maitrise peu MySQL et encore moins les fonctions de fenêtrage, quel que soit le SGBD)
un petit essai s'impose, je n'ai pas tout à fait respecté les indications, mais bon, je maitrise mal l'outil HeidiSQL (écrit en Delphi ) utilisé. [Edit] utilisation de DBeaver pour coller plus à la demande

Ci-dessous, un script comme je les aime pour proposer un test à quelqu'un :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE TABLE `notes` (
  `photo_id` int(11) NOT NULL AUTO_INCREMENT,
  `Note1` tinyint(4) DEFAULT 0,
  `Note2` tinyint(4) DEFAULT 0,
  `Note3` tinyint(4) DEFAULT 0,
  PRIMARY KEY (`photo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- Nota bene : une table devrait toujours avoir un index primaire 
COMMIT;
-- j'utilise le fait d'avoir un autoincrément pour ne pas me soucier de photo_id
INSERT INTO notes (Note1,Note2,Note3) VALUES
	 (10,8,5),
	 (5,8,3),
	 (9,8,7);
COMMIT;

L'image écran confirme mes suppositions de départ (avant normalisation de la table


enfin pas tout à fait puisque les lignes avec le meilleur totalcalcule sont en dernier, il suffit de jouer sur l'ORDER BY pour obtenir le résultat souhaité

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT 
  RANK() OVER (ORDER BY Totalcalcule DESC) AS place,
  photo_id,note1,note2,note3,note1+note2+note3 AS totalcalcule, 
  GREATEST(note1,note2,note3)- LEAST(note1,note2,note3) AS Ecart
FROM notes

Voilà comment, sans une ligne de code on aura, un classement toujours au point dès qu'une modification sera apportée à la table.


* Image DBeaver

[Bernard B]

Merci Sergio pour ce petit cours de SQL. Je découvre ou redécouvre plein de choses.

Concernant

trois colonnes sont inutiles et même potentiellement peuvent être fausses !

Je comprend ce que tu veux dire, mais cette base est locale, je suis le seul utilisateur et je suis sûr que les données ne seront pas modifiées pendant ma session.
Je n'ai pas les bons reflexes pour les colonnes calculées.

J'essaie de mettre ça en pratique !!

[Bernard B]

Après des essais avec HeidiSQL que je ne connaissais pas et avec phpMyAdmin, il semble que RANK OVER ne soit pas connu de mysql 5.7.11 qui fait partie des figures imposées de mon utilisation.

Par contre merci Sergio, pour ton aide j'envisage une autre piste que je pense viable pour arriver à mes fins. Par contre pour mon besoin qui arrive 2 fois par an, pour ce mois de mars c'est trop tard. J'ai jusqu'à novembre pour agir !!

Merci de votre aide

[SergioMaster]

il semble que RANK OVER ne soit pas connu de mysql 5.7.11

effectivement c'est à partir de la version 8.0

qui fait partie des figures imposées de mon utilisation.

Ça je comprends moins, car, je te cite plus ou moins, "il s'agit d'une version locale mono-utilisateur". Faire une mise à jour devrait être possible.

En exemple, mon installation Xampp (pas Wamp)

Utilise ma dernière version installée de MariaDB (qu'il faut d'ailleurs que j'upgrade, car commence à dater !)

P.S. DBeaver est aussi un très bon GUI même s'il nécessite Java

[Bernard B]

C'est une version locale mono-utilisateur dans la manière dont je l'utilise mais cela fait partie d'une application utilisée et maintenue à l'échelon national par une fédération et j'ai développé une application locale qui me permet de compléter les outils mis à disposition. C'est pour cela que la version est imposée.

[SergioMaster]

Avec Firebird 2.5 j'avais une autre solution pour obtenir le rang, reste à savoir si c'est possible avec MySQL.
Il s'agissait d'utiliser une variable session je vais chercher s'il existe un équivalent.
Si au moins, je t'ai convaincu qu'une colonne calculée n'avait pas sa place dans une table, ce serait déjà un bon point.

[popo]

Il y a également le fait qu'avec ce système, tu ne peut pas donner plus de 3 notes.

[Bernard B]

Effectivement cela peut paraître étriqué comme solution, mais il y a aussi un élément que vous n'avez pas : c'est que j'essaie seulement de corriger un "bug" de l'application mise à notre disposition donc dans l'urgence (3 semaines au plus pour pallier) et que je souhaite que la prochaine fois c'est à dire en novembre le pb sera corrigé.

Par contre, Sergio tu as gagné, tu m'as convaincu et la prochaine fois que j'aurais à me frotter à cette bête étrange qu'est la base de données, je réfléchirais différemment.

Une dernière question :
J'ai fait un Query sur une table, le contenu de ce Query est une photo de cette table à l'instant de l'exécution de cette demande, donc je peux utiliser ce contenu pour faire des updates de la table elle-même. Pouvez vous me le confirmer ?

Merci.

[ShaiLeTroll]

Pour MySQL 5, sans l'avoir testé, a vérifier si le double SELECT est nécessaire, je ne souviens plus à quel moment intervient le ORDER

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SET @v_row_number = 0;
 
SELECT (@v_row_number:=@v_row_number + 1) as place, NotesTmp.*
FROM
  (
    SELECT 
      photo_id,note1,note2,note3,note1+note2+note3 AS totalcalcule, 
      GREATEST(note1,note2,note3)- LEAST(note1,note2,note3) AS Ecart
    FROM notes
    ORDER BY totalcalcule DESC
  ) AS NotesTmp

[Bernard B]

Effectivement le ORDER BY est mal digéré par ma version 5.7, mais je vois mal où le mettre ailleurs.

[SergioMaster]

Presque @ShaiLeTroll ,
c'est à cela que je pensais quand je parlais de variable de session

Après test, il faut écrire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SET @v_row_number = 0;
 
SELECT (@v_row_number:=@v_row_number + 1) as place, Notestmp.*
FROM
  (
    SELECT 
      photo_id,note1,note2,note3,note1+note2+note3 AS totalcalcule, 
      GREATEST(note1,note2,note3)- LEAST(note1,note2,note3) AS Ecart
    FROM notes  
  ) AS NotesTmp
   ORDER BY totalcalcule DESC

et comme je ne suis pas un fan de SELECT FROM SELECT, cela devient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SET @v_row_number = 0;
 
SELECT (@v_row_number:=@v_row_number + 1) as place,
       photo_id,note1,note2,note3,
       note1+note2+note3 AS totalcalcule, 
       GREATEST(note1,note2,note3)- LEAST(note1,note2,note3) AS Ecart 
FROM notes  
ORDER BY totalcalcule DESC

[SergioMaster]

J'ai fait un Query sur une table, le contenu de ce Query est une photo de cette table à l'instant de l'exécution de cette demande, donc je peux utiliser ce contenu pour faire des updates de la table elle-même. Pouvez-vous me le confirmer ?

Pas comme ça, sans voir la query mais s'il s'agit de quelque chose du genre :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT id_photo,photo where id_photo=:aid

si photo est un blob graphique, il faudra faire une petite gymnastique (voir FAQ https://delphi.developpez.com/faq/?p...p-de-type-Blob ) mais oui

S'il s'agit d'une requête plus complexe style

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SET @v_row_number = 0;
 
SELECT (@v_row_number:=@v_row_number + 1) as place,
       n.photo_id,n.note1,n.note2,n.note3,
       n.note1+n.note2+n.note3 AS totalcalcule, 
       GREATEST(n.note1,n.note2,n.note3)- LEAST(n.note1,n.note2,n.note3) AS Ecart,
       p.photo -- la photo  
FROM notes n JOIN photos p on p.photo_id=n.photo_id
ORDER BY totalcalcule DESC

je recommanderai plus l'ajout d'un FDUpdateSQL voir une intervention plus directe dans la base via un FdConnection.ExecSQL

[Bernard B]

Les doubles SELECT versions Sergio ou ShaiLeTroll ne sont pas digérés par MySQL 5.7.

Je vais mettre ce sujet en standby, n'ayant plus le temps de m'en occuper cette semaine.

Encore merci pour vos contributions !!!

[SergioMaster]

Les doubles SELECT versions Sergio ou ShaiLeTroll ne sont pas digérés par MySQL 5.7.

Il n'y a pas de double select dans ma version 2 du message. Elle passe ou pas (à essayer avec HeidiSQL ou autre avant de passer à fDquery) ?

[Andnotor]

Les doubles SELECT versions Sergio ou ShaiLeTroll ne sont pas digérés par MySQL 5.7.

Surprenant, ça fonctionne en 5.1.