Discussion :

[grinder59]

Bonjour,

J'ai développé une application de facturation et dans un écran, je souhaite, en une seule requête, récupérer l'état de ma facturation. Je lance donc la requête suivante :

Code :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
select 
	f.CleFacture,
	f.DocumentFacture,
	f.DateFacture,
	tf.TypeFactureLibelle,
	f.NumerFacture,
	c.NomClient AS NomClient,
	sum((case when ((coalesce(te.CleTypeEcriture, 0) = 1) and (e.EcritureSupprimee = 0)) then ((e.QuantiteFacturee * e.PrixFacture) * te.Multiplicateur) else 0 end)) AS `Somme1`,
	sum((case when ((coalesce(te.CleTypeEcriture, 0) = 2) and (e.EcritureSupprimee = 0)) then ((e.QuantiteFacturee * e.PrixFacture) * te.Multiplicateur) else 0 end)) AS `Somme2`,
	sum((case when ((coalesce(te.CleTypeEcriture, 0) = 3) and (e.EcritureSupprimee = 0)) then ((e.QuantiteFacturee * e.PrixFacture) * te.Multiplicateur) else 0 end)) AS `Somme3`,
	sum((case when ((coalesce(te.CleTypeEcriture, 0) = 4) and (e.EcritureSupprimee = 0)) then ((e.QuantiteFacturee * e.PrixFacture) * te.Multiplicateur) else 0 end)) AS `Somme4`,
	coalesce(f.TotalFacture, 0) AS TotalFacture,
	coalesce(f.TotalFactureTTC, 0) AS TotalFactureTTC,
	coalesce(
		(
			select sum(coalesce(r.MontantReglement, 0)) 
				from 
					reglement 
				where 
					(
						(r.CleFacture = f.CleFacture) and 
						(r.ReglementActif = 1) and 
						(coalesce(r.ReglementSupprime, 0) = 0))
		), 0
	) AS MontantRegle,
	(
		f.TotalFactureTTC - 
		coalesce(
			(
				select sum(coalesce(r.MontantReglement, 0)) 
				from reglement 
				where 
					(
						(r.CleFacture = f.CleFacture) and 
						(r.ReglementActif = 1) and 
						(coalesce(r.ReglementSupprime, 0) = 0))
				), 0)
	) AS MontantRestant,
	f.StatutFacture,
	f.Domaine,
	coalesce(a.MontantAvoir, 0) as MontantAvoir,
	coalesce(a.MontantAvoirTTC, 0) as MontantAvoirTTC,
	f.MontantFactureInitial,
	f.MontantFactureTTCInitial,
	(
		select r.DateReglement
		from reglement 
		where 
		(
			(r.CleFacture = f.CleFacture) and 
			(r.ReglementActif = 1) and 
			(coalesce(r.ReglementSupprime, 0) = 0)
		) 
		order by r.DateReglement desc limit 0, 1
	) AS DateReglement,
	m.LibelleModeleFacture,
	f.NomFacture,
	a.NomAvoir
from 
	facture f left join 
	ecriture on e.CleFacture = f.CleFacture left join 
	typefacture tf on f.CleTF = tf.CleTF left join 
	typeEcriture te on e.CleTypeEcriture = te.CleTypeEcriture left join 
	client c on f.CleClient = c.CleClient left join 
	reglement r on f.CleFacture = r.CleFacture left join 
	avoir a on f.CleFacture = a.CleFacture left join 
	modeleFacture on f.CleModele = m.CleModele
 
	where (f.StatutFacture > 0)  AND (f.CleFacture = 133)
	group by 
		f.NumeroFacture,
		f.Domaine;

Cette requête fonctionne très bien SAUF que lorsque j'ai plusieurs enregistrement dans reglement pour la même facture, la valeur de Somme1 est doublée... le truc improbable quoi...

Après plusieurs décompositions de cette requête, il semble que ce soit cette partie là qui pose souci :

Code :

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

1
2
left join 
	reglement r on f.CleFacture = r.CleFacture

Pourtant je fais bien un left join entre la cle de ma facture et les écriture de la tables règlements qui contiennent la clé de ma facture...

Je cherche depuis un moment, mais en vain... Des yeux extérieurs verraient-ils quelque chose ? (parce que les miens commencent à vriller...)

Merci de votre avis !

[punkoff]

bonjour,

Cette requête fonctionne très bien SAUF que lorsque j'ai plusieurs enregistrement dans reglement pour la même facture, la valeur de Somme1 est doublée... le truc improbable quoi...

Niveau MCD quelle est la relation entre l'entité facture et les entitées reglement / avoir ?

Pourquoi un réglément et une ecriture ne sont pas reliées dans vos jointures (idem pour avoir) ?

edit: sinon d'un point de vu générale votre requête est pleine de sous-requete scalaire, ce qui peut devenir une catastrophe si vous faites des extractions globales

[grinder59]

relation Facture <=> Reglement :
une facture peut avoir 0 ou n règlements. Un règlement appartient à une seule facture. la clé de la facture est dans la table Reglement

relation Facture <=> Avoir : idem que précédemment
une facture peut avoir 0 ou n avoirs. Un avoir appartient à une seule facture. La clé de la facture est dans la table Avoir

Les écritures sont associées à une Facture. L'entité facture est vraiment au centre des autres entités. Il n'y a pas de lien direct entre les écritures, les règlements et les avoirs. Les écritures permettent l'édition d'une facture sur laquelle on enregistre des règlements et/ou des avoirs.

Merci de ton aide en tous cas...

[grinder59]

Après recherches, la solution est toute bête... pas besoin de faire de liaison sur la table reglement puisqu'aucun champs de cette table n'est directement récupéré...

Merci de t'être penché dessus en touts cas... et quels conseils me donnerais-tu pour éviter les sous requêtes scalaires ?

[punkoff]

Bonjour,

Ce serai intéresant que vous prenniez le temps de comprendre comment les jointures fonctionnent ainsi que les group by.

Ca vous éviterai de perdre du temps (c'est votre 2eme poste avec les même problème il me semble) dans votre travail.


Avec votre correction vous n'avez résolu qu'une partie du problème, car si une facture à plusieurs avoir vous aurez le même cas qui va se présenter.


Un exemple, adaptable à votre structure :

Code :

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

1
2
3
4
5
6
7
8
 
create table facture (id integer, montant integer);
create table ecriture (id_fac integer, mont_ecr integer);
create table avoir (id_fac integer, mont_av integer);
 
insert into facture values (1, 150);
insert into ecriture values (1, 50), (1, 100);
insert into avoir values (1, 10), (1, 20);

Code :

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

1
2
3
4
5
6
7
8
9
 
select *
from facture f
inner join ecriture e on f.id = e.id_fac
 
id  montant  id_fac  mont_ecr
---------------------------------
1  150  1  50
1  150  1  100

Maintenant rajoutons les avoirs :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select *
from facture f
inner join ecriture e on f.id = e.id_fac
inner join avoir a on a.id_fac = f.id;
 
id  montant id_fac  mont_ecr  id_fac  mont_av
-------------------------------------------------
1  150  1  50  1  10
1  150  1  100  1  10
1  150  1  50  1  20
1  150  1  150  1  20

Comme vous pouvez le voir votre résultat est dédoublé, ce qui est logique vu que vous avez 2 écritures et 2 avoirs pour une même facture.

Du coup si vous faites un group by sur la PK de facture et que vous faites un sum du montant des écritures vous allez avoir 2 fois le même montant pris en compte.


Pour moi ceci est un problème de modélisation, vu que fonctionnellement une écriture devrait correspondre soit à un réglement, soit à un avoir (il y a un très bon topic dans la partie modélisation/schema sur la gestion des factures http://www.developpez.net/forums/d11...-credit-debit/)


Sinon pour votre 2eme question, pour éviter les requêtes scalaire il faut générallement redescendre dans une sous-requête la partie incriminée.

Ceci n'est pas toujours optimale selon ce que vous voullez faire.
Une requête scalaire n'est pas mauvaise en soit si vous n'avez que peu de donnée selectionnée.

Par contre ceci devient vite un problème lorsque vous avez une grosse selection.

On peut vori dans votre select que vous utilisez 3 fois cette requêtes scalaire :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT sum(coalesce(r.MontantReglement, 0)) 
				FROM reglement 
				WHERE 
					(
						(r.CleFacture = f.CleFacture) AND 
						(r.ReglementActif = 1) AND 
						(coalesce(r.ReglementSupprime, 0) = 0))

Ceci veut dire que pour chaque ligne selectionenr le SGBD va devoir éxécuter 3 fois cette requête...

du coup vous pourriez faire un left join dessus (si ca marche sous mysql) :

Code :

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

1
2
3
4
5
6
 
SELECT CleFacture, sum(r.MontantReglement) as MontantReglement,
max(DateReglement) as DateReglement
FROM reglement 
WHERE r.ReglementActif = 1 AND ReglementSupprime = 0
group by CleFacture

[grinder59]

Merci de ta réponse.

Je sais comment fonctionne les jointures et les group by, mais il est vrai que je n'ai pas eu le temps de me pencher sur l'optimisation de la requête, mon client souhaitant un résultat immédiat, quitte à revenir dessus par la suite, étape par laquelle je dois encore passer.

Cette requête a subi plusieurs évolutions (demandes de mon client) sans que je prenne la peine (erreur de ma part) de retirer dans la version n+1 les éléments dont je n'avais plus besoin. Pour preuve, les jointures sur les tables règlements et avoir qui dans une version précédente présentait un intérêt mais qui pour cette version de l'application n'ont pas de nécessité (aucun colonne de ces table n'est affichée) voire pire provoque une erreur de calcul du fait des doublons. Et cette requête date d'avant ton conseil sur les éléments à inclure dans les group by (tous les éléments unitaires, qui ne pas soumis à une fonction de calcul de type sum, count...)

L'erreur rencontrée sur la table règlement ne pourra se reproduire sur les avoir car il n'est pas aujourd'hui possible d'enregistrer plusieurs avoirs. La base est prête pour cela, mais la fonctionnalité est bridée au niveau du code et des boutons affichés.

J'ai également bien compris la problématique des sous requêtes et même si je n'ai pas beaucoup d'éléments à sélectionner, je reconnais que la solution n'est pas optimale. Je m'étais rabattu sur cette façon de faire car je n'avais pas réussi à réaliser cette requête avec des jointures simples, mais cela provient peut être du fait que j'ai des tables en trop ou de colonnes manquantes dans mon group by. Je vais la retravailler dessus histoire de prendre en compte tes remarques.

En tous cas, je te remercie de tous les temps que tu m'a consacré ! Si je donne l'impression d'avoir posté un peu vite du fait du problème (une table qui n'a rien à faire là), c'est juste que j'ai cherché longtemps, mais pas dans la bonne direction. Merci donc pour tous tes conseils que je vais mettre à profit pour réaliser une vraie requête digne de ce nom...

encore merci !