Requete SQL avec tri suivant le contenu de la requete

ebouilleur · 15-12-2012 18:56:27

Jc a écrit :

Dans ta modélisation actuelle donc, quel est le critère qui doit être retenu en interne pour dire parmi x dates d'envoi quel est la date qui doit être considérée comme étant la date d'envoi officielle pour la facture? Si c'est la dernière, ton MAX est justifié, si c'est la première, ton MAX est faux. Si en interne il suffit de vérifier qu'une date d'envoi existe pour dire qu'elle a été envoyée, alors tu peux t'en sortir avec ta modélisation actuelle et adapter ta requête en conséquence.

C'est en effet la 1ère date qui m'intéresse, donc pas de max, ok.
Et oui il me suffit d'avoir 1 date pour dire que c'est envoyé.
Mais pour ce qui est de la récupération de date, ca fonctionne, ce qui ne fonctionne pas correctement, c'est le montant de prixFacture qui est doublé, triplé, a priori en fonction du nombre d'enregistrement dans la table suivi. Je vais continuer a travailler dessus.

Et pour le reste, ce qui ne me parlait pas était juste le terme : 3FN

Merci JC
Bon dimanche à toi.

Jc · 15-12-2012 19:08:25

Bonjour,

Concernant le problème de date
Il faut bien comprendre que de dire c'est la première date qui fait foi et qu'il suffit qu'il y en ait une pour dire que la facture est envoyée, sont deux cahiers des charges distincts qui se traduisent par deux requêtes distinctes selon que l'on ait besoin de connaître la date d'envoi de la facture ou non.
Si c'est la première que tu as besoin, c'est ok, c'est noté.

Maintenant, concernant le problème du prix de la facture
Je pensais que ce problème de doublement de prix était dores et déjà réglé, mais je me suis visiblement trompé.
Déjà je te rappelle que tu as un souci de modélisation avec ta tva définie en TINYINT. Faudrait que tu m'expliques la pertinence de ce choix, car pour moi c'est tout sauf pertinent et donc source d'erreur.
Ensuite, si ta table fact_prestation n'est pas normalisée correctement, ce qui est maintenant fortement possible vu la table fact_suivi, si tu as de la redondance dans les montants payés pour une facture payée, le résultat anormal que tu obtiens est tout à fait normal. Peux-tu me donner pour une facture donnée qui possède plusieurs enregistrements dans fact_prestation, les lignes de données correspondantes que je puisse me faire une idée stp?

ebouilleur · 15-12-2012 22:03:53

Pour la date, on s'en fout un peu en fait (si c'est la 1ere date ou juste testé si la date existe), mais c'est plus cohérent la 1ère date

Pour la tva, c'est la tva belge : 0, 6 ou 21% donc ca va TINYINT non?

Sinon pour info, que la facture ai 1 ou plusieurs prestations, le prixFacture n'est pas bon
Exemple
table fact_facture :

id_facture id_client id_membre numero_facture date_facture date_echeance date_payement montant_paye provenance prestataire note
13 123 1 2012/80 2012-10-29 2012-11-03 2001-01-01 12.00 Notaire derb Iso-peb test

Table prestation
id_prestation id_facture type autre date_prestation adresse    cp ville pays quantite prix_htva tva
10    13    14       2012-11-26 Rue Verte, 999    7000 Mons Belgique 1.0000 2.0000    21
11    13       12       2012-12-15 Rue l\'Averte, 999 7000 Mons Belgique    1.0000    10.0000    0

Table suivi (ici 2 envois de facture et 0 de doc):
id_suivi id_facture date_send who_send how_send type
4 13 2012-12-01 A toto 3 1
5 13 2012-12-01 au pape 2 1

Merci

Jc · 16-12-2012 01:44:12

SELECT f.id_facture, f.numero_facture, f.date_facture, f.date_payement, f.montant_paye, c.nom, c.ville, c.societe, SUM(t.total) AS prixFacture, 

            s2.date_send_doc, s1.date_send_fact

FROM fact_facture AS f

INNER JOIN fact_client AS c ON f.id_client = c.id_client 

LEFT JOIN (SELECT id_facture, date_send AS date_send_fact FROM fact_suivi WHERE TYPE=1 ORDER BY date_send) AS s1 ON f.id_facture=s1.id_facture

LEFT JOIN (SELECT id_facture, date_send AS date_send_doc FROM fact_suivi WHERE TYPE=2 ORDER BY date_send) AS s2 ON f.id_facture=s2.id_facture

INNER JOIN (SELECT id_facture, ((quantite*prix_htva) * (1+(tva/100))) AS total

                   FROM fact_prestation) AS t ON f.id_facture=t.id_facture

GROUP BY f.id_facture, f.montant_paye, date_send_fact, date_send_doc

HAVING f.montant_paye < SUM(t.total) OR date_send_fact IS NULL OR date_send_doc IS NULL

Voilà qui devrait résoudre tous tes problèmes.
Conseil : remplace type par stype ou autre car type est un mot réservé cf coloration syntaxique, passe en innoDB, et optimise moi tes indexs, place des contraintes de clefs étrangères et mets à jour ton modèle.

Dernière modification par Jc (16-12-2012 01:47:09)

ebouilleur · 17-12-2012 11:09:04

Salut,

J'ai fait des modif sur mes tables. j'ai changé type en stype, j'ai tout passé en InnoDB, créé un lien entre id_facture de la table fact_prestation et fact_facture et je voulais faire de même entre le id_facture de la table fact_facture et fact_suivi, mais ca me génère un erreur (que je n'arrive pas a résoudre). Est ce que l'on peut faire une contrainte de clés étrangères sur un champ qui est le meme sur plusieurs enregistrement?

Sinon pour ta requete : elle fonctionne (et me retourne le bon prixFacture) seulement si je n'ai que 1 ligne avec stype=1 et stype=2. si j'ai plusieurs lignes prixfacture n'est plus correcte. J'ai essayé plusieurs choses, (même des trucs idiots genre rajouter un LIMIT 1 dans les sous requete...) mais ca ne me donne toujours rien de bon

Jc · 18-12-2012 02:48:50

Bonjour,

comme je te l'ai déjà dit, cela viens du fait que ton id_facture dans fact_prestation est défini en INT(11) NOT NULL au lieu de INT(10) UNSIGNED NOT NULL comme il l'est ailleurs. On ne peut pas faire de correspondance entre des pommes et des poires, d'où l'erreur que tu obtiens.

ebouilleur a écrit :

elle fonctionne (et me retourne le bon prixFacture) seulement si je n'ai que 1 ligne avec stype=1 et stype=2. si j'ai plusieurs lignes prixfacture n'est plus correcte.

Tu peux traduire en français stp???

ebouilleur · 18-12-2012 10:07:57

Salut,

J'avais fait la correction des INT(10), mais a priori l'erreur était peut etre du a une facture qui n'existait plus dans la table fact_facture mais dont le suivi était toujours là.
Oublie la phrase que tu n'as pas compris, on va parti sur des copie d'écran.

Pour la requete :

    SELECT f.id_facture, f.numero_facture, f.date_facture, f.date_payement, f.montant_paye, c.nom, c.ville, c.societe, SUM(t.total) AS prixFacture,

    s2.date_send_doc, s1.date_send_fact

    FROM fact_facture AS f

    INNER JOIN fact_client AS c ON f.id_client = c.id_client

    LEFT JOIN (SELECT id_facture, date_send AS date_send_fact FROM fact_suivi WHERE stype=1 ORDER BY date_send) AS s1 ON f.id_facture=s1.id_facture

    LEFT JOIN (SELECT id_facture, date_send AS date_send_doc FROM fact_suivi WHERE stype=2 ORDER BY date_send) AS s2 ON f.id_facture=s2.id_facture

    INNER JOIN (SELECT id_facture, ((quantite*prix_htva) * (1+(tva/100))) AS total FROM fact_prestation) AS t ON f.id_facture=t.id_facture

    GROUP BY f.id_facture, f.montant_paye, date_send_fact, date_send_doc

    HAVING f.montant_paye < SUM(t.total) OR date_send_fact IS NULL OR date_send_doc IS NULL

J'obtient ca en résultat :
2012-12-18%2009-58-44_sqlprive.ovh.net%20_%2010.0.242.92%20_%20fact_peb_test%20_%20fact_facture%20_%20phpMyAdmin%202.11.png

2 lignes pour la factire id 13, alors qu'il m'en faudrait que 1 (si j'enleve le group by date, j'ai plus qu'une ligne, mais le prixFacture est alors de 24.84..
Car dans la table fact_suivi j'ai :

CREATETABLEIFNOTEXISTS`fact_suivi`(
`id_suivi`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,
`id_facture`INT(10)UNSIGNEDNOTNULL,
`date_send`VARCHAR(10)NOTNULL,
`who_send`VARCHAR(50)NOTNULL,
`how_send`VARCHAR(50)NOTNULL,
`stype` tinyint(3)UNSIGNEDNOTNULL,
UNIQUEKEY`id_suivi`(`id_suivi`),
KEY`id_facture`(`id_facture`),
KEY`stype`(`stype`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

--

-- Contenu de la table `fact_suivi`

--

INSERT INTO `fact_suivi` (`id_suivi`, `id_facture`, `date_send`, `who_send`, `how_send`, `stype`) VALUES

(13, 13, '2012-12-06', 'tata', 'courrier', 1),

(30, 13, '2012-12-12', 'toto', 'mail', 2),

(33, 13, '2012-12-17', 'titi', 'fax', 2);

--

-- Contraintes pour les tables exportées

--

--

-- Contraintes pour la table `fact_suivi`

--

ALTER TABLE `fact_suivi`

  ADD CONSTRAINT `fact_suivi_ibfk_1` FOREIGN KEY (`id_facture`) REFERENCES `fact_facture` (`id_facture`);

 

Si ça peut aider, je peux te donner un accès a phpmyadmin..
Merci

Jc · 18-12-2012 13:37:58

Tu commences à comprendre ce qu'on n'arrête pas de rabâcher avec MK sur ce forum avec les notions d'intégrité, consistance et qualité des données,
avec un problème d'intégrité référentielle pour ton problème d'erreur :

ebouilleur a écrit :

mais a priori l'erreur était peut etre du a une facture qui n'existait plus dans la table fact_facture mais dont le suivi était toujours là.

Les bases de données MyISAM en MySQL j'appelle ça des cache misère (dans plus de 90% des cas). Les imports de données rejetés à 40% par un modèle correct je connais que trop bien aussi. Donc merci, mais je ne veux surtout pas d'accès à phpmyadmin.
Pour revenir à ton problème de facture, imagine quand c'est le client qui est supprimé, la consistance des données est morte, on se retrouve avec des factures orphelines comme dans ton cas, et quand on a la chance qu'il n'y a pas un gros malin de dev qui passe par là, qui pense avoir l'idée géniale d'optimiser ses ids de clé primaire en les réutilisant (hors contexte d'une gestion cyclique des ids prévue et intégrée au cahier des charges au niveau admin), on se retrouve avec des factures appartenant avec des clients qui non jamais été facturé. Bref, tu ne peux plus te fier au contenu de tes données, car c'est l'arbre qui peut cacher la forêt. Quand ca arrive je ne touche à rien, et je demande au responsable de l'entreprise de me revalider l'ensemble des données à exporter et a réinjecter au propre dans un modèle normalisé conforme aux contraintes métier de l'entreprise. Rien de tel pour mettre une PME à genoux quand elle ne mets pas la clé sous la porte.

Les problèmes d'intégrité référentielle doivent faire l'objet d'une tolérance zéro, ca a déjà coûté des centaines de milliers voire des millions d'euros à certaines grandes entreprises, et ca continuera encore malheureusement.

Ensuite même s'il faut faire son maximum pour éviter de devoir indexer une colonne qui accepte les valeurs NULL, ton date_send en VARCHAR c'est pire, et ca peut avoir des effets de bord dans tes requêtes non négligeables. Ce genre de choses relève de la qualité de tes données.

Ensuite, de voir une facture avec une date de paiement effectué et avec un montant payé de 0 est l'illustre conséquence pourrait-on dire de tout ce que je viens de dire précédemment. A moins qu'il y ait un client qui s'est amusé à faire un chèque de zéro euro en paiement, ca fait tâche, et ca remet encore une fois en question la qualité et la consistance des données.

Pour tes deux lignes, je pense que tu es victime de ton modèle, en ayant utilisé la table de suivi comme d'un log action sur les factures. Ca te démontre aussi vu que le doc peut être envoyé plusieurs fois à des dates différentes, qu'il n'est pas pertinent d'en afficher la date d'envoi dans ton tableau contrairement à un nombre d'envoi par exemple.

Cependant pour n'avoir qu'une seule ligne sur ce genre de facture et en conservant le modèle actuel, la solution que je vois est de faire un group_concat sur la date dans la requête dérivée et faire un LEFT jusqu'au premier séparateur sur la date dans la requête principale.

++

Dernière modification par Jc (18-12-2012 13:50:53)

ebouilleur · 18-12-2012 14:18:03

Jc a écrit :

Ensuite, de voir une facture avec une date de paiement effectué et avec un montant payé de 0 est l'illustre conséquence pourrait-on dire de tout ce que je viens de dire précédemment. A moins qu'il y ait un client qui s'est amusé à faire un chèque de zéro euro en paiement, ca fait tâche, et ca remet encore une fois en question la qualité et la consistance des données.

En fait, l'idée c'est que si le client ne paye pas tout d'un coup, on pouvait indiqué la somme payé et la date du 1er payement

Jc a écrit :

Pour tes deux lignes, je pense que tu es victime de ton modèle, en ayant utilisé la table de suivi comme d'un log action sur les factures. Ca te démontre aussi vu que le doc peut être envoyé plusieurs fois à des dates différentes, qu'il n'est pas pertinent d'en afficher la date d'envoi dans ton tableau contrairement à un nombre d'envoi par exemple.

Je ne vois pas comment j'aurai pu créé la table suivi d'un autre manière, mais si tu as une idée je veux bien la connaitre.
Oui ça montre que le doc peut être envoyé plusieurs fois. Et là ou c'est pertinent, c'est de savoir quand/comment/a qui il a été envoyé. Pour prendre un exemple concret, imagine que tu relances un client pour une facture non payé et qu'il te dise "non je n'ai rien reçu", avec ma table suivi, tu saura quand, comment et a qui tu l'as envoyé, c'est ça le but. Donc dans mon tableau de récapitulatif de suivi, il n'est intéressant que de savoir si ça été envoyé ou non. A partir du moment ou ça été envoyé 1 fois, c'est que tu as fait ton boulot.

Je vais essayer de faire une requete avec les éléments que tu m'a fourni.
(edit de 17h32)
Avec la requete :

    SELECT f.id_facture, f.numero_facture, f.date_facture, f.date_payement, f.montant_paye, c.nom, c.ville, c.societe, SUM(t.total) AS prixFacture,

    s2.date_send_doc, s1.date_send_fact

    FROM fact_facture AS f

    INNER JOIN fact_client AS c ON f.id_client = c.id_client

    LEFT JOIN (SELECT id_facture, group_concat(date_send) AS date_send_fact FROM fact_suivi WHERE stype=1 ORDER BY date_send) AS s1 ON f.id_facture=s1.id_facture

    LEFT JOIN (SELECT id_facture, group_concat(date_send) AS date_send_doc FROM fact_suivi WHERE stype=2 ORDER BY date_send) AS s2 ON f.id_facture=s2.id_facture

    INNER JOIN (SELECT id_facture, ((quantite*prix_htva) * (1+(tva/100))) AS total FROM fact_prestation) AS t ON f.id_facture=t.id_facture

    GROUP BY f.id_facture, f.montant_paye, date_send_fact, date_send_doc

    HAVING f.montant_paye < SUM(t.total) OR date_send_fact IS NULL OR date_send_doc IS NULL

Ca fonctionne, j'ai en effet plus qu'une ligne. en plus j'ai utilisé group_concat que je n'avais jamais utilisé
Par contre je n'ai pas saisi ce que je devais faire avec le LEFT de la requete principale, surtout vu que ca fonctionne là?
(fin du edit de 17h32)

Sinon, pour InnoDB et les modif que j'ai effectué :
Sur ma table fact_suivi, j'ai donc relié mon id_facture a celui de la table fact_facture.
Pour le ON DELETE tu me conseilles de mettre CASCADE j'imagine?
ET pour le ON UPDATE, je pense qu'il ne faut rien mettre ?

Merci

Dernière modification par ebouilleur (18-12-2012 17:34:43)

Jc · 18-12-2012 21:39:09

Normalement c'est group_concat(date_send SEPARATOR ',') et ensuite tu fais un LEFT(date_send_doc,INSTR(date_send_doc,',')-1)

Mais si c'est juste le nombre d'envoi ca sera beaucoup moins gourmand en ressources avec un simple count(date_send).

Pour le ON DELETE ca ne s'improvise pas non plus. Tout dépends ce que tu souhaites faire. Déjà il faut savoir si tu veux rendre possible la suppression d'enregistrement ou non. Si c'est non, tu mets RESTRICT par défaut. Si c'est oui, il faut mettre en place ta stratégie et que tes dépendances fonctionnelles avec tes contraintes de clé étrangère soient bien placées. Ainsi tu éviteras les risques de références circulaires lors d'une suppression qui peuvent faire des dégâts, mais c'est assez puissant car admettons que sur une structure de 200 tables une référence client ait des dépendances dans 80 tables, en supprimant un client, tu peux supprimer en même temps tout ce qui se rattache à ce client dans le modèle sans rien oublier et en préservant la consistance et l'intégrité des données, sans développer une seule ligne de code supplémentaire.

++

ebouilleur · 19-12-2012 10:33:29

Je suis désolé JC, mais je n'arrive pas a placer ton LEFT dans la requete...
Mes tests ne m'ont retournés que des erreurs, et j'ai pas trouvé d'exemples sur google...
Si tu pouvais me montrer stp
Merci

Edit du 20/12
En fait on a oublié un GROUP BY dans les select des left join, car sinon il faut un group_concat de toute les dates sur 1 seul et meme enregistrement,
donc ca c'est mieux :

    SELECT f.id_facture, f.numero_facture, f.date_facture, f.date_payement, f.montant_paye, c.nom, c.ville, c.societe, SUM(t.total) AS prixFacture,

    s2.date_send_doc, s1.date_send_fact

    FROM fact_facture AS f

    INNER JOIN fact_client AS c ON f.id_client = c.id_client

    LEFT JOIN (SELECT id_facture, group_concat(date_send SEPARATOR ',') AS date_send_fact FROM fact_suivi WHERE stype=1 GROUP BY id_facture ORDER BY date_send) AS s1 ON f.id_facture=s1.id_facture

    LEFT JOIN (SELECT id_facture, group_concat(date_send SEPARATOR ',') AS date_send_doc FROM fact_suivi WHERE stype=2 GROUP BY id_facture ORDER BY date_send) AS s2 ON f.id_facture=s2.id_facture

    INNER JOIN (SELECT id_facture, ((quantite*prix_htva) * (1+(tva/100))) AS total FROM fact_prestation) AS t ON f.id_facture=t.id_facture

    GROUP BY f.id_facture, f.montant_paye, date_send_fact, date_send_doc

    HAVING f.montant_paye < SUM(t.total) OR date_send_fact IS NULL OR date_send_doc IS NULL

Dernière modification par ebouilleur (20-12-2012 10:39:37)

Jc · 05-01-2013 17:11:49

Bonjour,

Tous mes meilleurs voeux 2013 et surtout une bonne santé.
Pour le LEFT tu as tout ce qu'il te faut savoir dans la doc de MySQL. Si tu veux éviter des erreurs, et tout traiter au niveau de la requête (qui au final n'est peut être pas une bonne idée dans ton contexte) il te faudrait rajouter une clause CASE WHEN THEN ELSE END pour vérifier l'existence de la virgule séparatrice.
En gros s'il n'y a pas de virgule dans la colonne, on affiche la date telle qu'elle, s'il y a une virgule on affiche le contenu jusqu'à la première virgule.

++

Jc · 07-01-2013 19:07:42

Bonjour,

J'ai oublié de te répondre concernant ta table suivi. En fait en terme de dépendance fonctionnelle, cette table de log de suivi n'en est pas vraiment une, mais plutôt un log particulier de suivi concernant les expéditions seulement. Une vraie table de suivi consisterait à définir un type d'action sur la facture avec idéalement une table de référence pour gérer les types d'action possibles (validation comptable, archivage, etc...). Pour le faire d'une manière étendue et selon les besoins spécifiques métiers au niveau du modèle on pourrait y rajouter la création d'une facture d'acompte, d'un avoir, etc...

Ensuite au niveau du modèle, mais il n'y a pas de règle absolue à ce niveau car cela peut dépendre de beaucoup de choses, il vaut mieux à mon sens dissocier autant que possible l'accès aux logs dans ce genre de requête pour des questions de performance du modèle principalement. En effet, la volumétrie d'une table de log étant en généralement de l'ordre d'un facteur 100 (voire beaucoup plus) par rapport aux données qui lui sont référentes, il vaut mieux éviter de les utiliser en dehors d'un contexte de contrôle d'historique en production. Un tableau de bord facture se composant en général d'indicateur du style envoyé(e)? (oui/non), payé(e)? (oui/non), soldé(e)? oui/non etc..., il peut être préférable de créer une table statique en relation 1,1 avec la table facture dans ton cas constituée de colonnes en boolean pour gérer ses indicateurs. Ca a de plus l'avantage de pouvoir gérer les logs par trigger dans une transaction sans être confronté à d'éventuels problèmes d'existence de clé primaire référente au sein même de la transaction.

++

ebouilleur · 07-01-2013 22:30:45

Salut,

Une bonne année à toi aussi
Pour le LEFT, j'ai finalement compris ou tu voulais que je le met, en tombant sur une de mes requêtes ou j'en avait fait un...

Pour ma table suivi, je vois un peu ce que tu veux dire, mais j'ai du mal a voir ce que ca changerai par rapport a ma table, ca ne ferai que peu de différence je trouve, ou alors j'ai mal compris.

Merci en tt cas pour toujours me répondre

PHP|Débutant :: Forums

Advertisement

#26 15-12-2012 18:56:27

Re : Requete SQL avec tri suivant le contenu de la requete

#27 15-12-2012 19:08:25

Re : Requete SQL avec tri suivant le contenu de la requete

#28 15-12-2012 22:03:53

Re : Requete SQL avec tri suivant le contenu de la requete

#29 16-12-2012 01:44:12

Re : Requete SQL avec tri suivant le contenu de la requete

#30 17-12-2012 11:09:04

Re : Requete SQL avec tri suivant le contenu de la requete

#31 18-12-2012 02:48:50

Re : Requete SQL avec tri suivant le contenu de la requete

#32 18-12-2012 10:07:57

Re : Requete SQL avec tri suivant le contenu de la requete

#33 18-12-2012 13:37:58

Re : Requete SQL avec tri suivant le contenu de la requete

#34 18-12-2012 14:18:03

Re : Requete SQL avec tri suivant le contenu de la requete

#35 18-12-2012 21:39:09

Re : Requete SQL avec tri suivant le contenu de la requete

#36 19-12-2012 10:33:29

Re : Requete SQL avec tri suivant le contenu de la requete

#37 05-01-2013 17:11:49

Re : Requete SQL avec tri suivant le contenu de la requete

#38 07-01-2013 19:07:42

Re : Requete SQL avec tri suivant le contenu de la requete

#39 07-01-2013 22:30:45

Re : Requete SQL avec tri suivant le contenu de la requete

Pied de page des forums