Requête SQL classement championnat de football [Résolu]

[micky86]

Bonjour.

Je suis en train de créer un site web entre amis sur le pronostic de match de football (de ligue 1 essentiellement).

J'ai crée mon MCD et j'ai le MPD ci dessous.



Equipe :
equipe_id : identifiant (clé primaire)
equipe_lib : Libellé de l'équipe
equipe_val : booléen pour savoir si l'équipe est bien en ligue 1
equipe_logo : adresse url du logo de l'équipe

Matchs :
matchs_id : identifiant (clé primaire)
equipe_id_dom : clé étrangère de l'équipe qui joue a domicile
equipe_id_ext : clé étrangère de l'équipe qui joue a l'extérieur
matchs_journee : numéro de la journée
matchs_date : date du matchs
matchs_report : booléen pour savoir si le match est reporté

Resultat:
resultat_id : identifiant (clé primaire)
matchs_id : clé étrangère du matchs
resultat_but_ext : nombre de but de l'équipe qui joue a l'extérieur
resultat_but_dom : nombre de but de l'équipe qui joue a domicile

Pour le moment je bloque pour faire ma requête sql qui m'affichera le classement directement. Je sais que je devrais utiliser des conditions (when, case, en cas de victoire ou de defaite pour ajouter 3 points, 1 ou 0), mais pour le moment je n'en suis pas là.

Je voudrais pour le moment simplement afficher ma liste d'équipe avec tous les matchs joués (donc qui sont dans resultat), leurs nombres de but marqué a domicile et a l'extérieur et le nombre de matchs joué. Mon soucis se place surtout dans la jointure entre equipe et matchs, je ne sais pas trop comment la faire sachant que j'ai deux clés étrangères. Quelqu'un pourrait-il me mettre dans la bonne voie ?

Merci d'avance

[punkoff]

Salut,

Je n'arrive pas bien à voir l'utilité d'une table de résutlat, sachant qu'il n'y a qu'un seul résutlat par match possible.

Par contre modéliser un classement avec des saisons serai interessant non ?

[CinePhil]

Je voudrais pour le moment simplement afficher ma liste d'équipe avec tous les matchs joués (donc qui sont dans resultat), leurs nombres de but marqué a domicile et a l'extérieur

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT e1.equipe_id, m.match_journee,
	CASE
		WHEN m.equipe_id_dom = e1.equipe_id THEN r.resultat_but_dom
	END AS but_dom,
	CASE
		WHEN m.equipe_id_ext = e1.equipe_id THEN r.resultat_but_ext
	END AS but_ext
FROM Equipe e1
LEFT OUTER JOIN Match m 
	ON m.equipe_id_dom = e1.equipe_id 
	OR m.equipe_id_ext = e1.equipe_id
	INNER JOIN resultat r ON r.match_id = m.match_id
WHERE e1.equipe_val = 1

et le nombre de matchs joué

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT e.equipe_lib, COUNT(tmp.match_journee) AS nb_matchs_joues
FROM Equipe e
INNER JOIN
(
	SELECT e1.equipe_id, m.match_journee,
		CASE
			WHEN m.equipe_id_dom = e1.equipe_id THEN r.resultat_but_dom
		END AS but_dom,
		CASE
			WHEN m.equipe_id_ext = e1.equipe_id THEN r.resultat_but_ext
		END AS but_ext
	FROM Equipe e1
	LEFT OUTER JOIN Match m 
		ON m.equipe_id_dom = e1.equipe_id 
		OR m.equipe_id_ext = e1.equipe_id
		INNER JOIN resultat r ON r.match_id = m.match_id
	WHERE e1.equipe_val = 1
) tmp ON tmp.equipe_id = e.equipe_id

Au passage, tu devrais nommer les tables au singulier. match_id est l'identifiant d'un seul match !
Et dans le nom d'une colonne, ce qui est important n'est pas tellement le nom de la table mais la partie qui décrit ce que contient la colonne ; c'est donc plutôt celle-là qu'il faut mettre en entier.
Perso, j'utilise seulement des acronymes pour rappeler le nom de la table. Par exemple, j'écrirais plutôt ainsi quelques colonnes de ton modèle :
mch_id_equipe_exterieur
eqp_est_en_ligue_1
mch_est_reporte

[CinePhil]

Envoyé par punkoff

Je n'arrive pas bien à voir l'utilité d'une table de résutlat, sachant qu'il n'y a qu'un seul résutlat par match possible.

Je pense que c'est parce qu'on peut saisir tous les matches en début de saison avant d'avoir les résultats.
match -0,1----avoir----1,1- resultat

On pourrait par contre faire uen légère amélioration avec une identification relative :
match -0,1----avoir----(1,1)- resultat

Ce qui supprime la colonne resultat_id et passe la colonne resultat.match_id en clé primaire.

[StringBuilder]

Envoyé par CinePhil

Je pense que c'est parce qu'on peut saisir tous les matches en début de saison avant d'avoir les résultats.
match -0,1----avoir----1,1- resultat

On pourrait par contre faire uen légère amélioration avec une identification relative :
match -0,1----avoir----(1,1)- resultat

Ce qui supprime la colonne resultat_id et passe la colonne resultat.match_id en clé primaire.

Au pire, dans ce cas on peut laisser les champs de résultat nullables.

[micky86]

Merci pour toute vos réponses qui m'ont été d'une grande aide

Envoyé par punkoff

Je n'arrive pas bien à voir l'utilité d'une table de résutlat, sachant qu'il n'y a qu'un seul résutlat par match possible.

Par contre modéliser un classement avec des saisons serai interessant non ?

Comme le dis CinePhil c'est parce que je souhaite enregistrer tous les matchs en début de saison et ensuite ajouter les résultats au fur et a mesure. J'ai dans "l'idée" (ou espoir ^^) que lors d'un enregistrement du résultat, je verifie tous les paris faits sur ce match et si cela correspond bien, j'ajoute les points aux membres. Je veux que sa soit "automatique", mais un problème a la fois pour le moment ^^

Pour la table "Matchs" avec un s, c'est tous simplement parce que je travaille sous MySql et les requêtes avec Match ca pose soucis puisque Match est un Prédicat qui permet de comparer un ensemble de valeur de ligne à un ensemble de lignes retourné par une sous-requête. J'aurai pu appeler ma table Rencontre a la place oui, mais comme c'est pour du domaine privé, cela ne me dérange pas de mettre un S, je sais pourquoi je l'ai fait

Pour ce qui est des matchs en fonction des saisons, j'ai la table saison qui est bien relié a la table Matchs. J'ai juste viré les champs pour simplifié ma question. J'ai aussi un champ Championnat au cas où je voudrais faire le championnat de Ligue 2. J'ai aussi le pays relié a la table championnat pour changer de pays aux besoins ^^


Merci pour tes requêtes CinePhil, elles m'ont beaucoup aidé. Je faisais deux jointures personnellement (oui je sais c'est idiot ^^). En gros une jointure equipe_id = equipe_id_dom et equipe_id = equipe_id_ext... d'où mes soucis... Je ne savais pas qu'on pouvais utiliser la condition OR avec les jointures, très pratique, du coup j'ai pu faire tous mon classement du championnat avec une seule requête. Je la poste et je l'explique pour ceux qui sont intéressés, Attention, c'est la requête version BETA, elle peut largement être amélioré ^^

Code :

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
SELECT equipe_lib,sum(case when equipe_id = equipe_id_dom AND resultat_but_dom > resultat_but_ext 
    then 3
         when equipe_id = equipe_id_dom AND resultat_but_dom = resultat_but_ext
    then 1
         when equipe_id = equipe_id_dom AND resultat_but_dom < resultat_but_ext
    then 0
when equipe_id = equipe_id_ext AND resultat_but_dom < resultat_but_ext 
    then 3
         when equipe_id = equipe_id_ext AND resultat_but_dom = resultat_but_ext
    then 1
         when equipe_id = equipe_id_ext AND resultat_but_dom > resultat_but_ext
    then 0
end) AS Points ,
max(matchs_journee) AS J,
sum(case when equipe_id = equipe_id_dom then resultat_but_dom
     when equipe_id = equipe_id_ext then resultat_but_ext
end) AS Bp,
sum(case when equipe_id = equipe_id_dom then resultat_but_ext
         when equipe_id = equipe_id_ext then resultat_but_dom
end) AS Bc,
((sum(case when equipe_id = equipe_id_dom then resultat_but_dom
     when equipe_id = equipe_id_ext then resultat_but_ext
end)) - 
(sum(case when equipe_id = equipe_id_dom then resultat_but_ext
         when equipe_id = equipe_id_ext then resultat_but_dom
end))) AS Diff
 
 FROM equipe e
JOIN matchs m ON m.equipe_id_ext = e.equipe_id
              OR m.equipe_id_dom = e.equipe_id
JOIN resultat r ON r.matchs_id = m.matchs_id
GROUP BY equipe_id
ORDER BY Points DESC,Diff DESC, Bp DESC

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
sum(case when equipe_id = equipe_id_dom AND resultat_but_dom > resultat_but_ext 
    then 3
         when equipe_id = equipe_id_dom AND resultat_but_dom = resultat_but_ext
    then 1
         when equipe_id = equipe_id_dom AND resultat_but_dom < resultat_but_ext
    then 0
when equipe_id = equipe_id_ext AND resultat_but_dom < resultat_but_ext 
    then 3
         when equipe_id = equipe_id_ext AND resultat_but_dom = resultat_but_ext
    then 1
         when equipe_id = equipe_id_ext AND resultat_but_dom > resultat_but_ext
    then 0
end) AS Points

Ajoute les points en fonction du score du match. Je vérifie si l'équipe joue a domicile ou a l'extérieur pour connaitre quel résultat lui correspond et je compare, puis j'ajoute les points en fonction du résultat.

Code :

max(matchs_journee) AS J

Tous simple, ca me dit la journée la plus haute enregistré (avec la jointure il prend forcement les matchs avec un résultat et donc que le match a été joué)

Code :

1
2
3
sum(case when equipe_id = equipe_id_dom then resultat_but_dom
     when equipe_id = equipe_id_ext then resultat_but_ext
end) AS Bp

Pour voir tous les buts mis par l'équipe. Comme pour les points, je vérifie si l'équipe joue a domicile ou a l'extérieur.

Code :

1
2
3
sum(case when equipe_id = equipe_id_dom then resultat_but_ext
         when equipe_id = equipe_id_ext then resultat_but_dom
end) AS Bc

Pareil que précédemment, mais pour les buts encaissés.

Code :

1
2
3
4
5
6
((sum(case when equipe_id = equipe_id_dom then resultat_but_dom
     when equipe_id = equipe_id_ext then resultat_but_ext
end)) - 
(sum(case when equipe_id = equipe_id_dom then resultat_but_ext
         when equipe_id = equipe_id_ext then resultat_but_dom
end))) AS Diff

Pour faire la différence entre les buts marqué et encaissé. J'ai essayé d'utiliser les Alias Bp-Bc, mais MySql ne reconnais pas les colonnes.

Il me reste a traduire tous ca pour Doctrine. En faite je fais mon site sous Symfony avec l'ORM Doctrine. Je ne l'ai pas encore utilisé avec Case, When et compagnie

[CinePhil]

Envoyé par StringBuilder

Au pire, dans ce cas on peut laisser les champs de résultat nullables.

fsmrel dirait que le bonhomme NULL est interdit de séjour au Relationland !

Envoyé par micky86

Pour la table "Matchs" avec un s, c'est tous simplement parce que je travaille sous MySql et les requêtes avec Match ca pose soucis puisque Match est un Prédicat qui permet de comparer un ensemble de valeur de ligne à un ensemble de lignes retourné par une sous-requête. J'aurai pu appeler ma table Rencontre a la place oui, mais comme c'est pour du domaine privé, cela ne me dérange pas de mettre un S, je sais pourquoi je l'ai fait

Une méthode pour éviter d'utiliser des mots réservés du langage SQL est de se fixer une norme de nommage qui empêche de le faire, par exemple celle de SQLPro qui préfixe le nom des tables par un acronyme permettant de savoir de quel type de table il s'agit :
te_match => table issue d'une entité du MCD qui contient des matches.
Sur son site professionnel, il y a une norme plus complète.

Il me reste a traduire tous ca pour Doctrine. En faite je fais mon site sous Symfony avec l'ORM Doctrine. Je ne l'ai pas encore utilisé avec Case, When et compagnie

Ne t'embête pas avec la syntaxe pseudo-sql d'un ORM ! Tu vas t'arracher les cheveux pour faire une telle requête ! Soumet ta requête en SQL natif à Doctrine. Maintenant que ta requête est faite, inutile de perdre du temps à la traduire dans un sous langage.