Problème de requête sql [Résolu]

[aouchachacha]

Bonjour à tous,
Ce n'est pas la première fois que je fais appelle à vos lumières et je suis certain que je ne serai pas déçu.
J'ai une requête un peu complexe à faire en sql et je bloque. Voici ce que j'ai déjà produit :

Code :

1
2
3
4
5
6
7
SELECT A.id 
FROM sujets A, derniereVisite B 
WHERE B.id_mec = 1 
  AND A.id_gpe = 144 
  AND A.id = B.id_sujet 
  AND B.quand < A.last_msg 
ORDER BY A.last_msg ASC

Cette requête me permet de sortir tous les sujets du groupe 144 qui contiennent des messages que le visiteur 1 n'a pas lus.

La table sujets a une colonne "id", une colonne "id_gpe" qui indique dans quel groupe il est, une colonne "last_msg" qui indique la date du dernier message posté.
La table derniereVisite a une colonne "id_mec" qui indique l'id de l'utilisateur, une colonne "id_sujet" qui indique l'id du sujet, une colonne "quand" qui indique la date de dernière visite de l'utilisateur dans le sujet.

J'aimerai rajouter dans ma requête les sujets que l'utilisateur n'a JAMAIS visités. Est-ce que quelqu'un a une idée? Je sèche là...

Merci pour l'aide que vous pourrez m'apporter.

[Hephaistos007]

A : Sélectionne la liste de tous les sujets.
B : Sélectionne la liste des sujets que l'utilisateur a visité.

Par conséquent, A - B te donne les sujets que l'utilisateur n'a PAS visité.

Il faut donc utiliser l'opérateur de soustraction en SQL. Attention, il n'existe pas sous MySQL par exemple.

[aouchachacha]

Ca paraît simple quand tu le dis!! :-)

Et du coup j'utilise mysql avec php. Je commence à m'intéresser à PDO, est-ce que ça pourrait être une alternative et donc m'autoriser la soustraction?

[Hephaistos007]

Envoyé par aouchachacha

Ca paraît simple quand tu le dis!! :-)

Et du coup j'utilise mysql avec php. Je commence à m'intéresser à PDO, est-ce que ça pourrait être une alternative et donc m'autoriser la soustraction?

Non, PDO n'a rien à voir dans ce problème (mais c'est une bonne chose de l'utiliser). Si tu souhaites vraiment conserver le SGBD MySQL, tu peux reformuler la soustraction ainsi :

"Sélectionner la liste des sujets QUI N'EXISTENT PAS dans la liste des sujets que l'utilisateur a visité". Mais j'ai également un doute sur l'existence de la clause NOT EXIST sous MySQL

Au pire, il existe une troisième technique basé sur une jointure externe sur laquelle on repérer des valeurs nulles (Cf. http://www.developpez.net/forums/d15...e-minus-mysql/)

[aouchachacha]

Merci je vais regarder ça. En attendant j'avais testé un truc comme ça :

Code :

1
2
3
4
5
6
7
SELECT A.id, B.id_gpe, C.quand 
FROM `sujets` A, `adhesion` B, `derniereVisite` C
WHERE B.id_mbr = 1
AND B.etat = 1
AND A.id_gpe = B.id_gpe
AND A.id = IF(EXISTS(C.id_mec = 1 AND C.id_sujet = A.id),C.id_sujet,A.id)
ORDER BY `quand` ASC

Ca ne marche pas mais je viens seulement de découvrir la clause EXISTS donc ma syntaxe est surement mauvaise

PS: je précise que j'ai rajouté une table 'adhesion' dans laquelle je retourne tous les groupes auxquels l'utilisateur adhère.

[CinePhil]

Envoyé par Hephaistos007

"Sélectionner la liste des sujets QUI N'EXISTENT PAS dans la liste des sujets que l'utilisateur a visité". Mais j'ai également un doute sur l'existence de la clause NOT EXIST sous MySQL

C'est NOT EXISTS et ça existe chez MySQL.

Au pire, il existe une troisième technique basé sur une jointure externe sur laquelle on repérer des valeurs nulles (Cf. http://www.developpez.net/forums/d15...e-minus-mysql/)

En principe, dans ce cas, le NOT EXISTS est plus performant que la jointure externe mais avec MySQL on ne sait jamais.

On peut avoir la définition des tables, un petit jeu de données et le résultat attendu car je ne suis pas bien sûr de comprendre la totalité du besoin exprimé dans le premier message ?

[aouchachacha]

J'ai trois tables :
- une indiquant les groupes auxquels les utilisateurs adhèrent, construite comme ça

TABLE adhesion

|id|id_gpe|id_mbr|etat|
|0 | 144 | 1 | 1 |

- une contenant les sujets

TABLE sujets

|id|id_gpe|last_msg|
|11| 144 | 1230000001|
|12| 144 | 1230000005|
|13| 144 | 1230000010|

- une indiquant la date de derniere visite de l'utilisateur dans chaque sujet

TABLE derniereVisite

|id|id_mec|id_sujet|quand|
|0 | 1 | 11 |1230000005|
|1 | 1 | 12 |1230000002|

Et je cherche à connaître les sujets qui contiennent des messages postés après la dernière visite.

Donc là on aurait le sujet 12 car la dernière visite est supérieure et le 13 car il n'a jamais visité ce sujet (absence de résultat pour ce sujet et cet utilisateur dans la table derniereVisite)

J'espère que ça sera plus clair, merci!

[aouchachacha]

J'ai déjà fait des tests avec NOT EXISTS mais ça n'avait rien donné, surement que je formulais mal ma requête.

[CinePhil]

Essaie cette requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT tmp.id
FROM
(
    SELECT a.id_mbr, s.id AS id_sujet, s.last_msg
    FROM sujet s
    INNER JOIN adhesion a ON a.id_gpe = s.id_gpe
    LEFT OUTER JOIN
    WHERE a.idmbr = 1
        AND a.etat = 1
        AND a.id_gpe = 144
) tmp 
LEFT OUTER JOIN derniereVisite d 
    ON d.id_mec = tmp.id_mbr
    AND d.id_sujet = tmp.id_sujet
WHERE tmp.last_msg > d.quand
    OR d.id IS NULL

La sous-requête doit donner tous les sujets du groupe 144 pour l'utilisateur 1 à l'état 1 puis on restreint cet ensemble aux sujets dont le dernier message est postérieur à la dernière visite de l'utilisateur ou à ceux qui n'ont jamais été visité.
Ça répond à ton besoin ?

Comme il y a deux conditions, il vaut mieux cette fois la jointure externe.

[aouchachacha]

MySQL a répondu:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE a.id_mbr = 1
AND a.etat = 1
AND a.id_gpe = 144
) tmp
LEFT' at line 8

J'ai peut-être oublié de préciser que j'étais sous MySql

Je n'ai pas le temps de décortiquer le code pour le moment mais je jette un oeil plus concentré ce soir pour essayer de comprendre la requête et l'erreur.

[CinePhil]

Effectivement, il y a une ligne en trop dans ma requête ! Voilà ce que c'est de ne pas se relire attentivement !
Supprime le premier LEFT OUTER JOIN.

[aouchachacha]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT tmp.id_sujet
FROM
(
    SELECT a.id_mbr AS mbr, s.id AS id_sujet, s.last_msg AS msg
    FROM sujets s
    INNER JOIN adhesion a ON a.id_gpe = s.id_gpe
    WHERE a.id_mbr = 1
        AND a.etat = 1
) tmp 
LEFT OUTER JOIN derniereVisite d 
    ON d.id_mec = tmp.mbr
    AND d.id_sujet = tmp.id_sujet
WHERE tmp.msg > d.quand
    OR d.id IS NULL
ORDER BY tmp.msg ASC

Avec 2-3 corrections: suppression de la ligne id_gpe = 144 (c'était pour l'exemple, maintenant on regarde dans tous les sujets de tous les groupes)
tmp.id_sujet au lieu de tmp.id, j'ai mis des alias sur toutes les colonnes de la première table.
Et j'ai rajouté un ORDER BY histoire que ça soit cohérent.

Et c'est maintenant que je te dis un gros gros gros merci!!! Je vais pouvoir décortiquer cette requête histoire de comprendre.
Merci infiniment!

[aouchachacha]

Bonsoir bonsoir!!

Je reviens à la charge après avoir fait évoluer ma requête! Aujourd'hui j'ai ç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
 
SELECT tmp.id_sujet AS id, tmp.titre AS titre, tmp.gpe AS gpe, tmp.evt AS evt
	FROM
	(
	SELECT
	    s.id AS id_sujet,
	    s.titre AS titre,
	    s.last_msg AS msg,
	    s.id_gpe AS gpe,
	    s.id_evt AS evt
	FROM
	    sujets s
	WHERE
	   s.id_gpe IN (SELECT id_gpe FROM adhesion WHERE id_mbr = '%s' AND etat = 1 AND alerte = 1)
	OR
	   s.id_evt IN (SELECT id_evt FROM evt_rep WHERE id_mbr = '%s' AND reponse = 1)
	ORDER BY s.id_gpe, s.id_evt ASC
	) tmp
	LEFT OUTER JOIN derniereVisite d
	ON d.id_mec = '%s'
	AND d.id_sujet = tmp.id_sujet
	WHERE tmp.msg > d.quand
	OR d.id IS NULL
	ORDER BY tmp.msg ASC

La requête est bonne j'obtiens tout pile ce que je veux mais le soucis c'est qu'elle est super lourde et donc super lente!! (Environ 7 secondes d'exécution...)

Je suis persuadé qu'il y a moyen de l'optimiser. J'avais une version avec des jointures qui me donnait une erreur de MAX_JOIN_SIZE rows surement parce qu'elle était fausse :

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 tmp.id_sujet AS id, tmp.titre AS titre
 FROM
 (
 SELECT
     a.id_mbr AS mbr,
     s.id AS id_sujet,
     s.titre AS titre,
     s.last_msg AS msg,
     s.id_gpe AS gpe,
     s.id_evt AS evt
 FROM
     sujets s
 JOIN
     adhesion a
 ON
     (a.id_gpe = s.id_gpe AND a.etat = 1 AND a.alerte = 1)
 JOIN
     evt_rep e
 ON
     (e.id_evt = s.id_evt AND e.reponse = 1)
 WHERE
     a.id_mbr = '%s'
 OR
     e.id_mbr = '%s'
 ORDER BY s.id_gpe, s.id_evt
 ) tmp
 LEFT OUTER JOIN derniereVisite d
 ON d.id_mec = tmp.mbr
 AND d.id_sujet = tmp.id_sujet
 WHERE tmp.msg > d.quand
 OR d.id IS NULL
 ORDER BY tmp.msg ASC

Mais je rame beaucoup plus avec les jointures, je les ai découvertes il y a 3 semaines ^^

Pour bien comprendre, je rajoute à la situation précédente des sujets contenus dans des événements auxquels l'utilisateur participe.

Avec "evt_rep"
| id | id_mbr | id_evt | reponse |
|key | INT | INT | 0/1/2 |

(reponse : 0 -> n'y va pas, 1 -> y va, 2 -> peut-être; on ne gardera que reponse = 1)

Et dans la table sujets on rajoute une colonne id_evt en précisant que quand id_gpe != 0, id_evt = 0 et inversement.

Merci de l'attention que vous porterez à mon message!!

[CinePhil]

Dans seconde requête avec les jointures :
1) Puisque tu fais des jointures internes (JOIN ou INNER JOIN), pourquoi y mettre des clauses de restriction ?
=> Mets les dans le WHERE.

2) Puisque dans la requête principale tu ne souhaites que tmp.id_sujet et tmp.titre, et que dans la jointure externe tu n'as besoin que de tmp.mbr en plus des deux premières colonnes citées, inutile de laisser dans la sous-requête les colonnes s.id_gpe, s.id_evt. C'est toujours ça en moins à faire pour le SGBD.

3) Le ORDER BY dans la sous-requête est inutile, surtout que ce tri est sur deux colonnes que tu ne ramènes pas dans le SELECT général. Encore du boulot en moins pour le SGBD.

4) Dans la requête générale, tu ordonnes par tmp.msg mais tu n'as pas cette colonne dans le SELCT. Ce tri est-il utile ?

5) Je suppose que les %s sont des variables passées à la requête ?
Puisque ce sont des identifiants, qui doivent normalement être de type entier, inutile de les mettre entre apostrophes.

Voici ta requête corrigée selon ce qui précède :

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
SELECT tmp.id_sujet AS id, tmp.titre AS titre
FROM
(
    SELECT
        a.id_mbr AS mbr,
        s.id AS id_sujet,
        s.titre AS titre,
        s.last_msg AS msg
    FROM sujets s
    INNER JOIN adhesion a
        ON a.id_gpe = s.id_gpe 
    JOIN evt_rep e
        ON e.id_evt = s.id_evt 
    WHERE a.id_mbr = '%s'
        OR e.id_mbr = '%s'
        AND a.etat = 1 
        AND a.alerte = 1
        AND e.reponse = 1
) tmp
LEFT OUTER JOIN derniereVisite d
    ON d.id_mec = tmp.mbr
    AND d.id_sujet = tmp.id_sujet
WHERE tmp.msg > d.quand
    OR d.id IS NULL
ORDER BY tmp.msg ASC

Ensuite, pour la performance, est-ce que toutes les colonnes figurant dans les conditions de jointure sont indexées ?

[DUCKY_]

Bonjour à tous,

Les posts s'entremêlent et je n'ai pas réussi à comprendre ce qui était recherché ...

L'un d'entre-vous peut-il résumer le résultats attendus avec les différentes tables et leur champs ?

Cordialement,

[aouchachacha]

Oui elles sont indexées, j'envisageais de passer ma table en innoDB pour pouvoir mettre des FOREIGN KEY. D'après ce que j'ai lu ça devrait rendre le boulot plus facile, donc ça pourrait rendre la requête exécutable. Parce qu'avec ta version corrigée il m'affiche encore le même message d'erreur...

Le tri sur tmp.msg est utile, je ressors les sujets par date du dernier message, sans avoir besoin d'afficher cette date.

Effectivement les %s sont l'identifiant de l'utilisateur que j'ai oublié d'enlever.

DUCKY je prépare un autre post avec la situation. Désolé d'être fouilli!

[aouchachacha]

Alors je cherche à connaître la liste des sujets dans lesquels des nouveaux messages ont été postés. Pour ça, j'ai les tables suivantes :

- derniereVisite
| id (INT, PRIMARY KEY) | id_mec (INT) | id_sujet (INT) | quand (INT) |

Elle enregistre la date de dernière visite de l'utilisateur dans chaque sujet.

- sujets
| id (INT, PRIMARY KEY) | id_gpe (INT) | id_evt (INT) | last_msg (INT) |
titre ( VARCHAR) |

Elle enregistre tous les sujets des groupes comme des événements, si id_gpe != 0, id_evt = 0 et inversement, un sujet ne peut pas être dans un groupe et dans un événement en même temps.

- adhesion
| id (INT, PRIMARY KEY) | id_mbr (INT) | id_gpe (INT) | etat (0/1) | alerte (0/1) |

Elle enregistre les adhésions aux groupes. Une adhésion est valide quand etat = 1 et on est abonné aux alertes quand alerte = 1.

- evt_rep
| id (INT, PRIMARY KEY) | id_mbr (INT) | id_evt (INT) | reponse (0/1) |

Elle enregistre les réponses aux événements. L'utilisateur recevra les messages uniquement s'il va à l'événement donc si reponse = 1


Au final je cherche à afficher une liste de sujets, regroupés par groupes/événements et classés par date de dernier message, qui contiennent des messages postés après ma dernière visite dans le sujet.
(je peux évidemment jongler avec php mais si sql me le fait entièrement ça serait top!)

Donc il faut sélectionner la liste des groupes auxquels l'utilisateur adhère (id_gpe dans adhesion avec id_mbr = '%s', etat = 1 et alerte = 1), sélectionner la liste des événements auxquels il participe (id_evt das evt_rep avec id_mbr = '%s', reponse = 1), sélectionner la liste des sujets ayant id_gpe = (liste des groupes) et id_evt = (liste des événements), et enfin comparer la date du dernier message posté à la date de dernière visite pour derniereVisite.id_sujet = sujets.id et dernereVisite.id_mec = '%s' et sélectionner la liste des sujets ayant sujets.last_msg > derniereVisite.quand ainsi que les sujets ayant derniereVisite.id = NULL (sujets jamais visités)

[CinePhil]

Envoyé par aouchachacha

Oui elles sont indexées, j'envisageais de passer ma table en innoDB pour pouvoir mettre des FOREIGN KEY. D'après ce que j'ai lu ça devrait rendre le boulot plus facile, donc ça pourrait rendre la requête exécutable.

C'est effectivement mieux d'avoir les tables en InnoDB pour pouvoir bénéficier des clés étrangères mais ça ne joue pas dans la rapidité de la requête de sélection.

Parce qu'avec ta version corrigée il m'affiche encore le même message d'erreur...

On peut voir le message exact et la requête exacte qui le provoque ?

[aouchachacha]

requête SQL:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT tmp.id_sujet AS id, tmp.titre AS titre
FROM (
 
 
SELECT a.id_mbr AS mbr, s.id AS id_sujet, s.titre AS titre, s.last_msg AS msg
FROM sujets s
INNER JOIN adhesion a ON a.id_gpe = s.id_gpe
JOIN evt_rep e ON e.id_evt = s.id_evt
WHERE a.id_mbr =1
OR e.id_mbr =1
AND a.etat =1
AND a.alerte =1
AND e.reponse =1
)tmp
LEFT OUTER JOIN derniereVisite d ON d.id_mec = tmp.mbr
AND d.id_sujet = tmp.id_sujet
WHERE tmp.msg > d.quand
OR d.id IS NULL 
ORDER BY tmp.msg ASC 
LIMIT 0 , 30

MySQL a répondu:

#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

[aouchachacha]

Dans la solution que tu proposes, il me semble (si j'ai bien compris les cours de jointures) que le SGBD va joindre les 3 tables : sujets, adhesion et evt_rep.
Qui ont respectivement 490, 4254, 460 enregistrements. Du coup on se retrouve avec 490*4254*460 résultats à un moment donné non?

C'est pas pour ça que ça pète?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
requête SQL:  
 
SELECT a.id_mbr AS mbr, s.id AS id_sujet, s.titre AS titre, s.last_msg AS msg
FROM sujets s
INNER JOIN adhesion a ON a.id_gpe = s.id_gpe
JOIN evt_rep e ON e.id_evt = s.id_evt
WHERE a.id_mbr =1
OR e.id_mbr =1
AND a.etat =1
AND a.alerte =1
AND e.reponse =1
LIMIT 0 , 30 
 
MySQL a répondu: 
 
#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

L'idée serait peut-être de faire une jointure entre sujets et adhesion et une autre entre sujets et evt_rep et d'ajouter les résultats pour joindre le tout avec derniereVisite.

Mais ça je ne sais pas trop comment le faire... UNION?