Discussion :

[Chasseur d'étoiles]

Bon j'explique la difficulté.

J'ai trois tables de base [global_messages] et [global_messages_read] et [users].

J'essaie ici d'obtenir les informations de global_messages dans certains critères, en somme quand le message global n'est pas lu. Pour ce faire j'ai pensé utilisé un système simple, pour chaque message flaggué comme lu, j'insère dans global_messages_read l'id de l'article et l'id de l'utilisateur.

Ainsi si la ligne n'est pas présente dans global_messages_read alors théoriquement on peut considérer que l'utilisateur n'a pas lu le message. Néanmoins j'ai un mal fou à créer la requête pour ça.

La structure ressemble à ça :

Code :

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

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
63
 
CREATE TABLE `global_messages` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(60) character set latin1 NOT NULL,
  `content` text character set latin1 NOT NULL,
  `creation_date` int(11) NOT NULL,
  `options` text character set latin1 NOT NULL,
  `deleted` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `global_messages`
--
 
INSERT INTO `global_messages` (`id`, `title`, `content`, `creation_date`, `options`, `deleted`) VALUES
(1, 'title', 'content jjj', 1215548156, '', 0),
(2, 'annonce 2', ' ouverture !!!', 1215548492, '', 0);
 
-- --------------------------------------------------------
 
--
-- Structure de la table `global_messages_read`
--
 
CREATE TABLE `global_messages_read` (
  `id` bigint(20) NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `global_message_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
 
--
-- Contenu de la table `global_messages_read`
--
 
INSERT INTO `global_messages_read` (`id`, `user_id`, `global_message_id`) VALUES
(1, 1, 1),
(2, 2, 1),
(4, 1, 2);
 
-- --------------------------------------------------------
 
--
-- Structure de la table `users`
--
 
CREATE TABLE `users` (
  `id` bigint(11) NOT NULL auto_increment,
  `username` varchar(100) character set latin1 NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
 
--
-- Contenu de la table `users`
--
 
INSERT INTO `users` (`id`, `username`) VALUES
(1, 'chasseurdetoile'),
(2, 'fili'),

[qi130]

j'ai un mal fou à créer la requête

la requête qui fait quoi ? l'INSERT ? le SELECT ?

[CinePhil]

Conceptuellement, pourquoi avoir ajouté un id à la table `global_messages_read` alors qu'il s'agit d'une table de jointure qui pourrait avoir comme clé primaire le couple (`user_id`, `global_message_id`).
Avec cet id ajouté, rien n'interdit qu'un même utilisateur ait lu plusieurs fois le même message. Ce n'est pas illogique sur le plan 'real life' mais à quoi bon enregistrer plusieurs fois l'information ? Surtout qu'aucune autre information n'est contenue dans cette table.

A part ce point conceptuel, où est ta difficulté ?

[Chasseur d'étoiles]

Bonne idée pour la structure je n'y avait pas songé.

sinon c'est pour la requête select la difficulté, j'arrive pas à créer une requête qui puisse me donner l'information.

J'avais songé à un

Code :

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

1
2
3
SELECT *
FROM global_messages
WHERE global_messages.id NOT IN (SELECT global_message_id FROM global_messages_read WHERE user_id = 1 ) ;

néanmoins je me dit que si les messages deviennent nombreux il risque d'y avoir unn bon temps d'attente pour cette requête .... bref bonjour les ressources gourmandes. :S

[CinePhil]

Si j'ai bien compris, tu cherches les messages qui n'ont pas été lus par l'utilisateur X ?

Code :

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

1
2
3
4
5
SELECT gm.*, gmr.global_message_id
FROM global_messages_read gmr
RIGHT JOIN global_messages gm ON gm.id = gmr.global_message_id
INNER JOIN users u ON u.id = gmr.user_id
WHERE u.username = 'X' AND gmr.global_message_id IS NULL

Le RIGHT JOIN va donner tous les enregistrements de global_messages et comme il n'y aura pas de correspondance dans global_message_read, la colonne global_message_id sera à NULL.

[Chasseur d'étoiles]

Ça ne semble pas fonctionner.

Avec la structure actuelle ça devrais normalement me retourner normalement la première annonce. En considérant que global_messages_read possèdes les info suivantes :

Code :

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

1
2
3
 
[id] [user_id] [global_message_id]
x      1             2

donc global_message_id = 1 n'est pas présent dans la table avec l'user_id 1. Devrais donc être sélectionné mais ce n'est pas le cas. (euh j'suis pas sur d'être clair).

[CinePhil]

Effectivement ça ne fonctionne pas parce que tous les messages sont dans global_messages_read donc il trouve toujours au moins une correspondance.

Je ne vois pas d'autre solution que la dernière que tu as proposée.

[Chasseur d'étoiles]

À moins que j'utilise une autre structure ? J'ai la chance d'être au départ du projet donc on peut changer la structure sans difficulté. Honnêtement je vois mal comment faire un système qui puisse gérer keke milliers de membres (potentiels) pour la lecture des annonces.

À la limite j'avais songé à utiliser des cookies pour marquer les annonces lues et une date d'expiration à l'annonce, sauf que le problème avec ça c'est que si le gars se connecte depuis un autre ordinateur (et c'est fort probable) l'annonce vas toujours paraitre comme non-lue.

[CinePhil]

Non la structure est conceptuellement logique :
Users -0,n----Lire----0,n- Global_messages

Il manque en MySQL l'opérateur MINUS entre deux SELECT qui aurait permis de faire la différence entre la table Global_messages et la sélection des messages lus par l'utilisateur X.

A lire : les conseils de SQLPro sur l'optimisation des bases de données. Notamment dans le tableau situé à peu près à la moitié du document, la ligne 11 : 'évitez les sous requêtes avec IN... lorsque vous pouvez utiliser EXISTS'

Bon courage