Pb de requête ou optimisation du schéma ? [Résolu]

[nu_tango]

Bonjour,

Un ami veut mettre en place un petit systême de candidature.
Les tables qu'il m'a fournies :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE IF NOT EXISTS `candidatures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` varchar(2000) NOT NULL,
  `email` varchar(60) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `date` datetime NOT NULL,
  `login` varchar(50) NOT NULL,
  `traite` tinyint(1) NOT NULL DEFAULT '0',
  `resultat` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
CREATE TABLE IF NOT EXISTS `candidatures_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idmembre` int(6) NOT NULL,
  `id_candidature` int(5) NOT NULL,
  `vote` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Pas de souci pour afficher les résultats des candidatures où l'on n'a déjà voté :

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
SELECT
    t.id,
    t.date,
    t.message,
    t.traite,
    t.resultat,
    t.login,
    p.idmembre,
    p.id_candidature,
    p.vote
FROM
    candidatures AS t
JOIN
    candidatures_votes AS p
ON
    t.id = p.id_candidature
WHERE
    t.date > DATE_ADD(NOW(), INTERVAL -2 DAY)
AND
    t.traite = 0
AND
    p.idmembre = id_session
GROUP BY
    t.id
ORDER BY
    t.date ASC

Par contre pour afficher les candidatures ou l'on n'a pas encore voté j'ai essayé ça :

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
SELECT
    t.id,
    t.date,
    t.message,
    t.traite,
    t.resultat,
    t.login,
    p.idmembre,
    p.id_candidature,
    p.vote
FROM
    candidatures AS t
JOIN
    candidatures_votes AS p
ON
    t.id = p.id_candidature
WHERE
    t.date > DATE_ADD(NOW(), INTERVAL -2 DAY)
AND
    t.traite = 0
AND
    p.idmembre != id_session
GROUP BY
    t.id
ORDER BY
    t.date ASC

Mais évidemment ça ne fonctionne pas car il n'y a pas d'enfants dans la table candidatures_votes pour les candidatures où personne n'a encore voté et j'avoue que je tourne un peu en rond

-> Est-il possible en une seule requête d'afficher les candidatures ou l'on n'a pas encore voté ?
-> N'aurait-il pas été préférable créer une 3ème table "votes" et modifier la table candidatures_votes pour en faire uniquement une table de jointure ?

Par avance merci.

[jbrasselet]

Tu peux le faire en recherchant toutes les candidatures pour lesquels il n'y a pas de lignes dans candidatures_votes.
Pour cela, regarde du coté du not exists

[nu_tango]

Merci pour ta réponse jbrasselet,

EXISTS et NOT EXISTS dans mes souvenirs c'est en effectuant une sous-requête qu'on les utilise ?

Je vais regarder de ce côté.

[jbrasselet]

C'est en effet avec une sous-requête

[nu_tango]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
	t.id, 
	t.date, 
	t.message, 
	t.traite, 
	t.resultat, 
	t.login
FROM
    candidatures AS t
WHERE
    NOT EXISTS(
		SELECT  
			p.id_candidature
		FROM 
			candidatures_votes AS p	
		WHERE
			t.id = p.id_candidature)
AND
    t.traite = 0	
ORDER BY
    t.date ASC

Merci encore pour ton aide, je suis sur la voix, mais cette requête me renvoit toutes les candidatures où personne n'a encore voté et non pas celle ou moi je n'ai pas voté, donc je suis pas encore bon là...

Il faut donc que je fasse intervenir le champ idmembre mais je n'ai pas de sous-ensemble si je fais le fais intervenir dans la clause WHERE de la sous-requête. A moins de faire une jointure dans celle-ci ?

[CinePhil]

Ajoute la restriction à ton idmembre dans la sous-requête.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
    t.id, 
    t.date, 
    t.message, 
    t.traite, 
    t.resultat, 
    t.login
FROM
    candidatures AS t
WHERE
    NOT EXISTS(
        SELECT * -- Inutile de préciser des colonnes ici
        FROM 
            candidatures_votes AS p    
        WHERE
            t.id = p.id_candidature
            AND p.idmembre = 12
    )
    AND t.traite = 0    
ORDER BY
    t.date ASC

Traduction de la requête :
"Sélectionner les candidatures pour lesquelles il n'existe pas de ligne dans la table des votes du membre 12 pour ces candidats "

C'est ce que tu veux ?

[nu_tango]

Merci CinePhil,

je me suis embrouillé tout seul à force de chercher compliqué.
C'est bien cette requête dont mon ami a besoin.

[jbrasselet]

Dans ta sous-requete tu peux même faire un select 'X' comme ça tu ne parcourt pas les colonnes et tu optimise un brin tes performances.

[CinePhil]

Envoyé par jbrasselet

Dans ta sous-requete tu peux même faire un select 'X' comme ça tu ne parcourt pas les colonnes et tu optimise un brin tes performances.

SELECT * va très bien. Dans ce cas, le SGBD ne cherche pas à ramener toutes les colonnes dans la sous-requête, il cherche seulement une ligne qui répond à la condition.

[NOT] EXISTS est même le seul cas où on peut écrire SELECT * sans craindre de déclencher la guerre des étoiles !

[jbrasselet]

OK, je ne savais pas.
Merci de la précision

[nu_tango]

Merci encore pour ces précisions. A priori je pensais aussi que le SELECT * renverrait toutes les colonnes...

C'est normalisé au niveau des différents SGBDR ça ?

[Waldar]

Avec une jointure externe vous couvrez tous vos besoins avec une seule requête.

[nu_tango]

J'avais pensé aussi à faire un OUTER JOIN mais je n'ai pas réussi à trouver la bonne requête, je vais regarder à nouveau de ce côté mais le gain en performance doit être limité, non ?

[Waldar]

Tout-à-fait, en terme de performances ça devrait être extrêmement proche.
L'avantage c'est que la même requête répond à vos deux besoins :

• les candidatures où l'on a déjà voté
• les candidatures ou l'on n'a pas encore voté