Discussion :

[davcha]

Je voudrais supprimer les messages invisibles d'une messagerie.

La messagerie en elle même est composée de trois tables liées entre elles : msg_content, msg_dest et une troisième : users, qui ne nous intéresse pas beaucoup dans le problème présent.

msg_content
id : bigint(20)
auteur : varchar(20)
...
hidden : enum('yes','no')

msg_dest
msg_id : bigint(20)
user_id : varchar(20)
...
hidden : enum('yes','no')

Le principe de cette messagerie est de permettre à ses différents utilisateurs de supprimer les messages qu'ils ont envoyé ou reçu, sans pour autant supprimer le message de la base de donnée, ceci permettant aux autres utilisateurs concernés par le message, de conserver le message s'il le souhaitent.
Le champ "hidden" sert cette idée. Simplement : quand "hidden" est égal à "yes", alors le message n'est pas affiché.

Il arrive un moment où un message devient invisible pour tout le monde : quand l'auteur du message supprime le message (msg_content.hidden='yes') et quand tous ses destinataires suppriment le message : (msg_dest.hidden='yes'), alors le message devient totalement invisible et alourdit inutilement la base de donnée.

On peut facilement selectionner ces messages de cette façon :

Code :

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

1
2
3
4
5
6
7
8
SELECT id
FROM msg_content
LEFT JOIN msg_dest on (msg_content.id = msg_dest.msg_id)
WHERE msg_content.hidden = \'yes\'
GROUP BY id
HAVING SUM(
    IF(msg_dest.hidden=\'yes\',0,1)
) = 0

On selectionne tous les messages qui ont été supprimés par l'auteur, ainsi que par tous les destinataires.

Maintenant, il faut pouvoir supprimer ces messages. J'ai donc naturellement encapsulé cette requête dans un DELETE :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DELETE FROM msg_dest
WHERE msg_id IN (
    SELECT id
    FROM msg_content
    LEFT JOIN msg_dest on (msg_content.id = msg_dest.msg_id)
    WHERE msg_content.hidden = \'yes\'
    GROUP BY id
    HAVING SUM(
        IF(msg_dest.hidden=\'yes\',0,1)
    ) = 0
)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DELETE FROM msg_content
WHERE id IN (
    SELECT id
    FROM msg_content
    LEFT JOIN msg_dest on (msg_content.id = msg_dest.msg_id)
    WHERE msg_content.hidden = \'yes\'
    GROUP BY id
    HAVING SUM(
        IF(msg_dest.hidden=\'yes\',0,1)
    ) = 0
)

Mais dans ce cas, j'obtiens cette erreur :

Code :

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

#1093 - You can't specify target table 'msg_dest' for update in FROM clause

A noter que j'utilise MySql 4.1.9-max

Si quelqu'un a une solution pour m'éviter de devoir supprimer les lignes une par une, ça m'aiderait bien.

Merci.

[SQLpro]

Le message d'erreur concerne un UPDATE :
"#1093 - You can't specify target table 'msg_dest' for update in FROM clause"
Rien à voir donc avec les DELETE que vous faites.

A +

[souellet]

Est-ce qu'il y aurait un trigger qui est déclenché pendant le DELETE?

[soazig]

Ton problème ne viendrait t-il pas que dans la sous requête il n'arrive pas à faire la différence entre la table msg_content du delete et celle du select.
Donc en mettant un alias sur la table dans la sous requete, cela pourrait marcher.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DELETE FROM msg_content 
WHERE id IN ( 
    SELECT id 
    FROM msg_content c
    LEFT JOIN msg_dest on (c.id = msg_dest.msg_id) 
    WHERE c.hidden = \'yes\' 
    GROUP BY id 
    HAVING SUM( 
        IF(msg_dest.hidden=\'yes\',0,1) 
    ) = 0 
)

Je n'ai pas mis d'alias pour le from du delete car je crois que ce n'est pas autorisé par la norme.
A+
Soazig

[davcha]

J'avais pensé à ce problème d'alias, mais en définitive, le problème reste le même.

J'ai l'impression que c'est dû au fait que je supprime des données d'une table en me servant de cette même table pour les sélectionner, et que ça pose donc problème, un conflit de données en quelque sorte (je m'exprime mal, mais l'idée est là).
Je pense que je vais faire mes suppressions en deux temps. Sélectionner les messages à supprimer, en faire une liste séparée par des virgules, puis la fournir dans une requête delete.
Ca sera un poil plus lent, mais bon. Comme c'est de la maintenance, ça n'arrive pas tous les jours.

Edit : Pendant que j'y pense, il y a moyen, je crois de faire une suppression de lignes dans deux tables à la fois, non ?
Quelque chose comme ça :

Code :

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

DELETE FROM t1,t2 USING t1,t2 WHERE t1.id = t2.t1id AND t1.id IN (1,2,3,4)

Je me trompe ?

C'est bon, c'est réglé.