Problème jointure de deux tables.

Version imprimable

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:

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'),

10/07/2008, 10h01
qi130

Citation:

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

la requête qui fait quoi ? l'INSERT ? le SELECT ?
10/07/2008, 10h34
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é ?
11/07/2008, 01h15
Invité
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:

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

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

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.

11/07/2008, 10h53
Invité
Ç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:

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).
11/07/2008, 11h41
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. :(
11/07/2008, 19h42
Invité

À 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.
12/07/2008, 00h25
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