UPDATE mysql dans une boucle: alternative?

[Roworll]

Je ne comprends pas ton code, où fais tu la récursion/boucle sur les villages suivants ?

Bon, visiblement, l'utilisation des sous requêtes n'est pas si claire.
Je décompose pour mieux expliquer.

Reprenons les données de base.
J'ajoute également une village supplémentaire pour l'exemple

on doit diminuer de 1000 ressources:

le village 1 en contient 300, on diminue celui ci de 300
le village 2 en contient 400, on diminue celui ci de 400
le village 3 en contient 1000, on diminue celui ci de 300 seulement
le village 4 en contient 500, on ne diminue pas

Cela signifie que on doit retirer le maximum de ressources possible de chaque village jusqu'à ce que le montant total demandé ait été prélevé. Pour commencer, il me faut donc savoir combien de ressources sont disponibles sur les villages précédents.
Ces valeurs sont déterminées par la sous requête la plus profonde de mon exemple.
Dans un premier temps, j'enlève les GREATEST pour plus de compréhension

SELECT 

	ID, 

	(

		SELECT IFNULL(SUM(Res),0)

		FROM Village V2 

		WHERE V2.ID < V1.ID

	) 

FROM Village V1

Cela va donner les résultats suivants
Village 1 : 0
Village 2 : 300
Village 3 : 700
Village 4 : 1700

Cela se traduit par

Je dois retirer du village 1 la quantité demandée
Je dois retirer du village 2 la quantité demandée - 300 (le contenu du village 1)
Je dois retirer du village 3 la quantité demandée - 700 (le contenu du village 1 + village 2)
Je dois retirer du village 4 la quantité demandée - 1700 (le contenu du village 1 + 2 + 3)

Note : sur cet exemple de code, je travaille avec les ID des villages mais on peut imaginer travailler avec un champ Village_Sequence qui permettrait d'organiser les prélèvements dans l'ordre souhaité.

J'utilise la fonction GREATEST pour appliquer deux règles supplémentaires.
- Je ne peux pas retirer plus de ressources qu'il en existe dans un village
- Une fois que toutes les ressources nécessaires ont été récupérées, inutiles d'en prendre plus

SELECT 

	ID, 

	GREATEST(Res - GREATEST(1000-(

		SELECT IFNULL(SUM(Res),0) 

		FROM Village V2 

		WHERE V2.ID < V1.ID),0),0)

FROM Village V1

Avec cette requête, je récupère les nouvelles valeurs de ressource de chaque village.
Je décompose les calculs pour chaque ligne affectées par MySQL
Village 1 : GREATEST(300 - GREATEST(1000 - 0,0) , 0) -> 0
Village 2 : GREATEST(400 - GREATEST(1000 - 300,0), 0) -> 0
Village 3 : GREATEST(1000 - GREATEST(1000 - 700,0) , 0) -> 300
Village 4 : GREATEST(500 - GREATEST(1000 - 1700,0) , 0) -> 500

Comme dans ma requête principale, ces résultats sont stockés dans une table temporaire, avec l'ID du village et du joueur, un simple update avec jointure me permet, en une requête, de mettre à jour les ressources de tous les villages.

Pas besoin de boucle par village !

Il est à noter qu'il est également possible de mettre à jour tous les joueurs d'un coup pour un même montant de ressource via cette méthode.
Il suffit de remplacer la clause WHERE JID=1 de mon exemple par un GROUP BY JID

De la même manière, si on veut retirer un nombre de ressource différent à chaque joueur en une seule passe, c'est tout à fait possible.
Il suffit d'avoir une table contenant l'ID du joueur et la quantité de ressource à prendre et de faire une jointure appropriée dans la requête la plus profonde

SELECT 

	ID, 

	GREATEST(Res - GREATEST(G.Res-(

		SELECT IFNULL(SUM(Res),0) 

		FROM Village V2 

		WHERE V2.ID < V1.ID),0),0)

FROM Village V1 INNER JOIN GetRes G ON V1.JID = GetRes.JID

Simple, efficace et tout en une requête.
Donc, ces histoires de boucle, de fraction et de transaction sont inutiles.

[php_addict]

salut

très ingénieux quoiqu'un peu ardu pour moi, je vais reprendre un café et relire ceci...

[niahoo]

Ah oui je pige bien, c'est sympa comme façon de faire. Mais je ne saurais pas remplacer le select par un update.

[Roworll]

Mais je ne saurais pas remplacer le select par un update.

Lapin compris...

C'est quoi ton soucis avec ce remplacement de select par un update ?

[niahoo]

Ben pour l'instant, tu récup les données à modifier, mais tu ne les modifie pas. Lui il veut faire plusieurs updates en une seule requête. Enfin c'est ce sur quoi je pensais qui tu étais parti.

---

Mais j'avais zappé ton post précédent ou il y a l'update, je vais essayer de piger comment fonctionne la jointure avec update

[Roworll]

Si tu reprends la première requête que j'ai posté, tu verras que ce que j'ai décomposé correspond à tout ce qui se trouve entre parenthèse après le INNER JOIN.
En effet, la requête entre parenthèse sert à construire cette fameuse table temporaire (TmpRes) qui est utilisée dans l'update, ce qui au final, permet de faire la mise à jour tant souhaitée en une seule passe.

Pour conclure sur une parenthèse, il ne faut pas sous-estimer la puissance des moteurs SQL en général. Avec un peu d'astuce et de technique, on peut passer outre une grande partie des problèmes.
MySQL permet heureusement l'utilisation de sous requêtes (très utile dans le cas présent) mais on est encore bien loin des formidables possibilités des 'gros' moteurs.
Pour parler de ce que je connais, les CTE (Common Table Expression) et l'utilisation des directives CROSS APPLY sur SQL Server ouvrent des possibilités faramineuses dans le traitement de problèmes faisant appel aux boucles et/ou à la récursivité.

D'ailleurs, si vous avez un petit quart d'heure (et quelques aspirines si vous n'avez pas l'habitude du SQL), je vous conseille la lecture de cet excellent article qui présente quelques possibilités des CTE pour travailler récursivement (le chapitre IV-B et les exemples 24 & 25 sur le problème du voyageur de commerce sont particulièrement bluffants)

[niahoo]

C'est vrai que juste ton exemple est impressionnant, par contre l'opacité du code est terrible, c'est totalement illisible.

[Roworll]

C'est juste une question d'habitude.
Passer de PHP sous notepad++ au C# dans visual Studio par exemple demande un temps d'adaptation.
Passer de MySQL a du SQL de bon niveau, que ce soit sous SQL Server, Oracle ou autre, c'est pareil.
Mais une fois bien maîtrisé, c'est phénoménal.

[niahoo]

Je veux bien te croire, j'ai toujours eu du mal avec SQL, ça ne me plaît pas, histoire de goûts hein.

En tout cas je crois que ta solution est toute trouvée php_addict, si tu prends les villages dans l'ordre arbitrairement comme dans ton exemple.

[php_addict]

En tout cas je crois que ta solution est toute trouvée php_addict, si tu prends les villages dans l'ordre arbitrairement comme dans ton exemple.

oui c'est ce que je vais faire, peut être pas avec ce type de requête un peu obscure pour moi, mais ca vallait le coup de poster cette question...

chapeau Roworll, t'es balaise en SQL, merci