Récupérer derniers messages, système de messagerie

[spike2]

Bonjour,

Je cherche depuis un moment mais ça ne donne rien...Voici ma relation message

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
+-----------------+--------------+------+-----+---------+----------------+
| FIELD           | Type         | NULL | KEY | DEFAULT | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | AUTO_INCREMENT |
| expediteur_id   | int(11)      | YES  | MUL | NULL    |                |
| destinataire_id | int(11)      | YES  | MUL | NULL    |                |
| parent_id       | int(11)      | YES  | MUL | NULL    |                |
| sujet           | varchar(255) | NO   |     | NULL    |                |
| message         | longtext     | NO   |     | NULL    |                |
| date            | datetime     | NO   |     | NULL    |                |
| vu              | int(11)      | NO   |     | NULL    |                |
| efface          | int(11)      | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

C'est donc des messages avec des réponses possibles qui font référence à leur parent via parent_id.

J'aimerai récupérer la dernière réponse de chaque message. Si on a par exemple:
Parent1 possède {reponses -> rep1, rep2, rep3}
Parent2 possède {reponses -> rep4, rep5}

Le résultat retourné serait être :
rep3 et rep5

Une idée ?
Merci!

[ledisciple]

tu as essayé d'utiliser max() soit sur ta date ?

[spike2]

Oui mais, pour le moment j'arrive juste à récupérer quelque chose de cette forme:

Code :

1
2
3
4
5
6
7
+--------+----+---------------------+--------+---------------------+
| sujet  | id | pdate               | xsujet | xdate               |
+--------+----+---------------------+--------+---------------------+
| coucou |  1 | 2011-04-16 10:48:35 | merci    | 2011-04-16 10:49:04 |
| coucou |  1 | 2011-04-16 11:48:35 | ok    | 2011-04-16 11:49:37 |
| yo     |  4 | 2011-04-16 14:45:03 | bye   | 2011-04-16 14:46:01 |
+--------+----+---------------------+--------+---------------------+

Avec ça:

Code :

1
2
3
4
SELECT p.sujet,p.id,p.date AS pdate, x.sujet AS xsujet, x.date AS xdate 
FROM message p JOIN message x ON p.id=x.parent_id 
WHERE p.id OR x.parent_id IN 
(SELECT m.id FROM message m WHERE m.parent_id IS NULL);

Il faudrait que j'arrive maintenant à extraire deux résultats:

Code :

1
2
3
4
5
6
+--------+----+----------------
| sujet  | date               |
+--------+----+----------------
| ok    | 2011-04-16 11:49:37 |
| bye   | 2011-04-16 14:46:01 |
+--------+----+----------------

[ericd69]

Pour faire simple tu veux construire une requête hiérarchique qui va parcourir ton arborescence de message et sortir ses feuilles (les derniers)

Certains sgbd comme Oracle implémentent ça en natif... mais pas mysql...

L'idée est de faire une procédure stockée qui va parcourir tous tes messages de premier niveau (avec null comme parent_id) et récupérer le sujet. on utilisera un curseur pour ça. puis pour chaque id on applique une fonction stockée qui va servir pour la récursion jusqu'à la feuille de la branche et rapatrier ce que tu veux (ici la date).

Ne pas oublier de mettre à jour la variable d'environnement mysql qui autorise le nombre de récursion et qui est à 0 de base... pour être sur autan le faire dans la procédure.

Pour le rendu, 2 grandes façons :

• tu ponds le résultat sous forme d'un set de résultats indépendants (1 par message trouvé).
• Tu concatènes les résultats sous forme xml
• Tu concatènes les résultats avec différents séparateurs pour les traiter dans une autre procédure ou en php après.

Au fait, des identifiant en int(4) ça te donne déjà plus de 2 milliards d'entrées à savoir, soit bien plus que ce que ne permet de traiter le pauvre mysql en un temps fini... De même, longtext ça fait plus que riche car text permet déjà plus de 64000 caractères, ça fait aussi déjà un très long message...

Les colonnes vu et efface ne sont elles pas de simples booléens plutôt que des int(11)?

Voilà un exemple qui fait ce que tu veux ... à noter que la limite de récursion est fixée à 255 dans mysql

Code sql :

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
DROP DATABASE IF EXISTS messagerie;
CREATE DATABASE messagerie DEFAULT character SET utf8 collate utf8_general_ci;
USE messagerie;
CREATE TABLE message(
	id						int(4) NOT NULL AUTO_INCREMENT,
	expediteur_id		int(4),
	destinataire_id	int(4),
	parent_id			int(4),
	sujet					varchar(255) NOT NULL,
	message				text NOT NULL,
	`date`				datetime NOT NULL,
	vu						bool NOT NULL DEFAULT false,
	efface				bool NOT NULL DEFAULT false,
	constraint pk_message PRIMARY KEY(id),
	KEY(expediteur_id,destinataire_id,parent_id)
)engine=myisam AUTO_INCREMENT=1;
 
INSERT INTO message(expediteur_id,destinataire_id,parent_id,sujet,message,`date`)VALUES
(1,2,NULL,"coucou","ça va?","2011-04-18 10:00:00"),
(2,1,1,"re:coucou","oui et toi","2011-04-18 12:00:00"),
(3,1,2,"re:re:coucou","moi aussi","2011-04-18 14:00:00"),
(3,2,NULL,"rdv","on peut se voir quand?","2011-04-18 10:00:00");
 
delimiter |
CREATE procedure last_response(IN id_message int(4),out l_id int(4))
begin
	declare test bool;
	declare id_m int(4);
	SELECT count(id)>0,id INTO test,id_m FROM message WHERE parent_id=id_message;
	IF test then
		call last_response(id_m,l_id);
	else
		SET l_id=id_message;
	end IF;
end|
CREATE procedure list_messages()
begin
	declare d datetime;
	declare done bool DEFAULT false;
	declare i,j int(4);
	declare s varchar(255);
	declare lit cursor FOR SELECT id,sujet,`date` FROM message WHERE isnull(parent_id);
	declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	SET @@session.max_sp_recursion_depth=255;
	open lit;
	repeat
		fetch lit INTO i,s,d;
		IF NOT done then
			call last_response(i,j);
			IF i=j then
				SELECT i AS "id",s AS "sujet",d AS "date";
			else
				SELECT i AS "id",s AS "sujet",d AS "date",j AS "rep id",m.sujet AS "rep sujet",m.`date` AS "rep date"
				FROM message m WHERE m.id=j;
			end IF;
		end IF;
	until done end repeat;
	close lit;
end|
delimiter ;
 
call list_messages();

A toi d'adapter les infos que tu veux rapatrier avec d'éventuelles jointures (left outer join) si besoin...