Discussion :

[identifiant_bidon]

Bonjour,

J'ai un sérieux problème de conception qui me pose souci depuis maintenant quelques jours et j'avoue que je m'arrache vraiment les cheveux. Je vais essayer de le décrire du mieux possible.

Imaginons un réseau social du style Facebook sauf que les users "amis" d'un user 1 sont rangés dans des groupes (1:Mes amis, 2:Ma famille, ...). Un user peut donc avoir plusieurs murs (groupes). Prenons un exemple. Soit 3 users 1, 2 et 3.

Le user 1 demande 2 et le range dans le groupe 1-Mes amis (dont il est owner)
Le user 2 répond favorablement et range 1 dans le groupe 2-Mes amis (dont il est owner)

Le user 1 demande 3 et le range dans le groupe 3-Ma famille (dont il est owner)
Le user 3 répond favorablement et range 1 dans le groupe 4-Mes amis (dont il est owner)

J'ai 3 tables :
GROUPE(id_group, name_group et id_owner)
1 Mes amis 1
2 Mes amis 2
3 Ma famille 1
4 Mes amis 3

MEMBRES_GROUPES(id_group, id_user)
1 1
1 2
2 2
2 1
3 3
3 1
4 3
4 1

Lorsqu'un message est ajouté dans un mur,une ligne est ajoutée à la table MESSAGES(id_message, text, id_group).
Par exemple, l'utilisateur laisse un message dans le groupe 1 --> 1 / texte message / 1

Ma question est donc la suivante :
Sachant que l'on a en paramètres d'entrée l'idUser laissant le message et l'idGroup dans lequel va le message, quelle requête permet d'afficher pour chaque user les messages selon ses groupes ?

Merci pour votre aide. Je commence à trop fumer de la tête ...

[Dendrite]

Tu vas un peu vite je trouve...
J'ai fait ça, et je te promets que je reviens très vite avec une requête;

user (id, prenom)
1 Alain
2 Béa
3 Corinne
4 Denis

group (id, lib, code)
1 amis A
2 famille F
3 connaissance C
4 indésirables I
5 pots de colle P

group_groupowner (id, owner_id, groupe_code)
1 1 A (alain a un groupe d'amis)
2 1 I (alain a un groupe d'indésirables)
3 2 F (Béa a un groupe famille)
4 3 P (Corinne a un groupe de pots de colle)
5 4 A (Denis a tous les groupes)
6 4 F
7 4 C
8 4 I
9 4 P

group_groupuser (id, user_id, group_groupowner_id)
100 4 1 (Denis est dans le groupe d'amis d'Alain)
101 3 2 (Corinne est dans le groupe indésirables d'Alain)
102 2 6 (Béa est dans le groupe Famille de Denis)
103 1 4 (Alain est dans le groupe pots de colle de Corinne)


message (id, titre, text, group_groupuser_id)
1000 "mon pote" "Denis dit à alain : toi t es mon pote" 100
1001 "mur du mépris" "Corine dit à alain : alors tu ne me désires pas MDRRRRRRRR" 101
1002 "Comment va maman" "Béa dit à Denis : elle est sortie de l'hosto maman ?" 102
1003 "Glu super glu" "alain dit à Corinne : De toute façon, tu me trouves pot de colle alors je ne t'aime pas. suis vexé !" 103

[Dendrite]

Ayé !
Ceci devrait fonctionner...
edit de 10H12 j'ai terminé. Tu remarqueras que tu n'as besoin que d'un seul paramètre, l'id de ton group_groupuser, qui contient par définition l'id de ton émetteur et de ton récepteur.

Code SQL :

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

1
2
3
4
5
6
7
8
select user.prenom as from_prenom, owner.prenom as to_prenom, g.lib as groupe, m.titre, m.text, m.quand
from message m
inner join group_groupuser ggu on m.group_groupuser_id=ggu.id and ggu.id=?
inner join user on ggu.user_id=user.id
inner join group_groupowner ggo on ggu.group_groupowner_id=ggo.id
inner join user owner on ggo.owner_id=owner.id
inner join group g on ggo.groupe_code=g.code
order by quand desc

PS : tu devrais changer ton titre.
ce n'est pas de l'algorithmique, (programmation).
C'est de la modélisation (comment ranger les données en SGBD).

Je remets ici les tables, car j'ai ajouté un "quand" au message

user (id, prenom)
1 Alain
2 Béa
3 Corinne
4 Denis

group (id, lib, code)
1 amis A
2 famille F
3 connaissance C
4 indésirables I
5 pots de colle P

group_groupowner (id, owner_id, group_code)
1 1 A (alain a un groupe d'amis)
2 1 I (alain a un groupe d'indésirables)
3 2 F (Béa a un groupe famille)
4 3 P (Corinne a un groupe de pots de colle)
5 4 A (Denis a tous les groupes)
6 4 F
7 4 C
8 4 I
9 4 P

group_groupuser (id, user_id, group_groupowner_id)
100 4 1 (Denis est dans le groupe d'amis d'Alain)
101 3 2 (Corinne est dans le groupe indésirables d'Alain)
102 2 6 (Béa est dans le groupe Famille de Denis)
103 1 4 (Alain est dans le groupe pots de colle de Corinne)


message (id, titre, text, group_groupuser_id, quand)
1000 "mon pote" "Denis dit à alain : toi t es mon pote" 100 2018-07-01 08:00:00
1001 "mur du mépris" "Corine dit à alain : alors tu ne me désires pas MDRRRRRRRR" 101 2018-07-02 12:00:00
1002 "Comment va maman" "Béa dit à Denis : elle est sortie de l'hosto maman ?" 102 2018-07-03 18:00:00
1003 "Glu super glu" "alain dit à Corinne : De toute façon, tu me trouves pot de colle alors je ne t'aime pas. suis vexé !" 103 2018-07-04 22:00:00

[identifiant_bidon]

Hello Dendrite,

Merci beaucoup pour ton retour. Je suis très impressionné !!
Je vais implémenter ça et vérifier de ce pas pour voir si cela fonctionne.

Si c'est le cas, tu n'imagines même pas l'énorme épine que tu m'ôtes du pied.

Concernant ta remarque, tu as raison. Sur ce coup, j'ai eu un gros gros fail de modélisation.

[identifiant_bidon]

Petite réflexion tout de même Dendrite.

Selon ta modélisation, un groupe est partagé entre plusieurs utilisateurs.
Par exemple,
2 1 I (alain a un groupe d'indésirables)
...
5 4 I (Denis a tous les groupes)

Pour moi, le groupe d'indésirables d'alain ne doit pas être le même que le groupe d'indésirables de Denis.

[Dendrite]

Pour moi, le groupe d'indésirables d'alain ne doit pas être le même que le groupe d'indésirables de Denis.

Bien entendu.
C'est pourquoi ils n'ont pas le même id (ici, 2 et 5).
Très important, ton premier champ id sur toutes les tables doit toujours être integer, clé primaire et auto-increment.
La table group est simplement une table de nomenclature, les tables suivantes sont des tables de liaison.
C'est d'ailleurs un très bon exemple de poupées russes (d'imbrication). N'oublie pas de poser les clés d'unicité, les clés d'indexation, les clés étrangères... et si tu n'as aucune idée de quoi je parle, on verra ça plus tard.

En tout cas, quand tu auras testé ta requête et que tout fonctionnera comme tu aimes, tu pourras la transformer en vue.
Ca te fera des requêtes très intuitives dans ton script PHP.

Code de création de la vue (à lancer une seule fois dans ta console SQL)

Code SQL :

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

1
2
3
4
5
6
7
8
create or replace view v_messages as
select m.id as message_id, ggu.id as gg_user_id, ggo.id as gg_owner_id, user.id as from_id, user.prenom as from_prenom, owner.id as to_id, owner.prenom as to_prenom, g.lib as groupe, m.titre, m.text, m.quand
from message m
inner join group_groupuser ggu on m.group_groupuser_id=ggu.id
inner join user on ggu.user_id=user.id
inner join group_groupowner ggo on ggu.group_groupowner_id=ggo.id
inner join user owner on ggo.owner_id=owner.id
inner join group g on ggo.groupe_code=g.code

Et dans ton script PHP :

Code :

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

1
2
3
4
5
//tous les messages postés PAR Denis
$sql='select * from v_messages where from_id=4 order by quand desc';
 
//tous les messages postés POUR Corinne
$sql='select * from v_messages where to_id=3 order by quand desc';

[identifiant_bidon]

Merci beaucoup Dendrite. Je te suis vraiment reconnaissant du temps que tu passes sur mon problème.

Je ne comprends juste pas ce que tu as écrit ici :

Code :

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

C'est pourquoi ils n'ont pas le même id (ici, 2 et 5).

2 et 5 ???

Je vais plancher ce soir à tête reposée sur le problème et te dis si c'est bon.

Merci encore !!

[Dendrite]

Heu 2 et 8...

1 c'est Alain, 4 c'est Denis, et I, c'est le code pour le groupe indésirable...

On distingue les 2 groupes par leur identifiant respectif, 2 et 8...

group_groupowner (id, owner_id, group_code)

2 1 I (alain a un groupe d'indésirables)


8 4 I (denis aussi a un groupe d'indésirables)

Je vais plancher ce soir à tête reposée sur le problème et te dis si c'est bon.

Oui, surtout prends ton temps, et mets pas mal de données cohérentes dans les tables SQL.
Comme ça tu auras un bon jeu de données pour vérifier.

[identifiant_bidon]

Hello Dendrite,

Tout ne se passe pas comme prévu. J'ai légèrement simplifié tes requêtes et voici mon (petit) jeu de données (J'ai créé des séquences que je ne présente pas ici pour + de lisibilité).

Code :

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

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS public."TEST_USER";
CREATE TABLE public."TEST_USER" (
    test_user_id SERIAL PRIMARY KEY,
    test_user_prenom character varying
);
INSERT INTO public."TEST_USER"(test_user_prenom) VALUES ('Roger');
INSERT INTO public."TEST_USER"(test_user_prenom) VALUES ('Henri');
INSERT INTO public."TEST_USER"(test_user_prenom) VALUES ('Bernard');
INSERT INTO public."TEST_USER"(test_user_prenom) VALUES ('Marc');
INSERT INTO public."TEST_USER"(test_user_prenom) VALUES ('Nathalie');

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS public."TEST_GROUPE";
CREATE TABLE public."TEST_GROUPE" (
    test_groupe_id SERIAL PRIMARY KEY,
    test_groupe_code integer,
    test_groupe_name character varying
);
INSERT INTO public."TEST_GROUPE"(test_groupe_code, test_groupe_name) VALUES ('1', 'Mes amis_R');
INSERT INTO public."TEST_GROUPE"(test_groupe_code, test_groupe_name) VALUES ('2', 'Mes amis_H');
INSERT INTO public."TEST_GROUPE"(test_groupe_code, test_groupe_name) VALUES ('3', 'Mes amis_B');
INSERT INTO public."TEST_GROUPE"(test_groupe_code, test_groupe_name) VALUES ('4', 'Mes amis_M');
INSERT INTO public."TEST_GROUPE"(test_groupe_code, test_groupe_name) VALUES ('5', 'Mes amis_N');

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS public."TEST_GROUPE_GROUPOWNER";
CREATE TABLE public."TEST_GROUPE_GROUPOWNER" (
    test_groupe_groupowner_id SERIAL PRIMARY KEY,
    test_groupe_groupowner_owner_id integer,
    test_groupe_groupowner_groupe_code integer
);
INSERT INTO public."TEST_GROUPE_GROUPOWNER"(test_groupe_groupowner_owner_id, test_groupe_groupowner_groupe_code) VALUES ('1', '1'); -- Roger a le groupe Mes amis_R
INSERT INTO public."TEST_GROUPE_GROUPOWNER"(test_groupe_groupowner_owner_id, test_groupe_groupowner_groupe_code) VALUES ('2', '2'); -- Roger a le groupe Mes amis_H
INSERT INTO public."TEST_GROUPE_GROUPOWNER"(test_groupe_groupowner_owner_id, test_groupe_groupowner_groupe_code) VALUES ('3', '3'); -- Roger a le groupe Mes amis_B
INSERT INTO public."TEST_GROUPE_GROUPOWNER"(test_groupe_groupowner_owner_id, test_groupe_groupowner_groupe_code) VALUES ('4', '4'); -- Roger a le groupe Mes amis_M
INSERT INTO public."TEST_GROUPE_GROUPOWNER"(test_groupe_groupowner_owner_id, test_groupe_groupowner_groupe_code) VALUES ('5', '5'); -- Roger a le groupe Mes amis_N

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS public."TEST_GROUPE_GROUPUSER";
CREATE TABLE public."TEST_GROUPE_GROUPUSER" (
    test_groupe_groupuser_id SERIAL PRIMARY KEY,
    test_groupe_groupuser_user_id integer,
    test_groupe_groupuser_groupe_groupowner_id integer
);
INSERT INTO public."TEST_GROUPE_GROUPUSER"(test_groupe_groupuser_user_id, test_groupe_groupuser_groupe_groupowner_id) VALUES ('4', '1'); -- Marc est dans Mes amis_R
INSERT INTO public."TEST_GROUPE_GROUPUSER"(test_groupe_groupuser_user_id, test_groupe_groupuser_groupe_groupowner_id) VALUES ('2', '1'); -- Henri est dans Mes amis_R
INSERT INTO public."TEST_GROUPE_GROUPUSER"(test_groupe_groupuser_user_id, test_groupe_groupuser_groupe_groupowner_id) VALUES ('5', '1'); -- Nathalie est dans Mes amis_R
INSERT INTO public."TEST_GROUPE_GROUPUSER"(test_groupe_groupuser_user_id, test_groupe_groupuser_groupe_groupowner_id) VALUES ('1', '4'); -- Roger est dans Mes amis_M
INSERT INTO public."TEST_GROUPE_GROUPUSER"(test_groupe_groupuser_user_id, test_groupe_groupuser_groupe_groupowner_id) VALUES ('1', '2'); -- Roger est dans Mes amis_H 
INSERT INTO public."TEST_GROUPE_GROUPUSER"(test_groupe_groupuser_user_id, test_groupe_groupuser_groupe_groupowner_id) VALUES ('1', '5'); -- Roger est dans Mes amis_N

Code :

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

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS public."TEST_MESSAGE";
CREATE TABLE public."TEST_MESSAGE" (
    test_message_id SERIAL PRIMARY KEY,
    test_message_text character varying,	
    test_groupe_group_groupuser_id integer
);
INSERT INTO public."TEST_MESSAGE"(test_message_text, test_groupe_group_groupuser_id) VALUES ('Coucou Nathalie, c Roger', '6');
INSERT INTO public."TEST_MESSAGE"(test_message_text, test_groupe_group_groupuser_id) VALUES ('Coucou Henri, c Roger', '5');
INSERT INTO public."TEST_MESSAGE"(test_message_text, test_groupe_group_groupuser_id) VALUES ('Coucou Roger, c Henri', '2');
INSERT INTO public."TEST_MESSAGE"(test_message_text, test_groupe_group_groupuser_id) VALUES ('Coucou Roger; c Marc', '1');

Code :

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

1
2
3
4
5
6
select tu.test_user_prenom as from_prenom, g.test_groupe_name as groupe, m.test_message_text
from public."TEST_MESSAGE" m
inner join public."TEST_GROUPE_GROUPUSER" ggu on m.test_groupe_group_groupuser_id=ggu.test_groupe_groupuser_id and ggu.test_groupe_groupuser_id=6
inner join public."TEST_USER" tu on ggu.test_groupe_groupuser_user_id=tu.test_user_id
inner join public."TEST_GROUPE_GROUPOWNER" ggo on ggu.test_groupe_groupuser_groupe_groupowner_id=ggo.test_groupe_groupowner_id
inner join public."TEST_GROUPE" g on ggo.test_groupe_groupowner_groupe_code=g.test_groupe_code

Mon souci concerne surtout les 2 derniers messages. Lorsque je veux afficher les messages du groupe 1 ( dans lequel se trouvent Marc et Henri); je n'ai qu'un seul de 2 messages que je devrai voir normalement affiché ...

PS : Je n'ai pas compris d'où tu as sorti owner dans la requête que tu m'avais proposé ...

[Dendrite]

Bonjour IB.
Effectivement, je vais développer une dernière fois.

1) ta table group n'a rien à voir avec la mienne.
Ne fais pas autre chose que ce que je te demande de tester... Relis et applique.

Si tu ne comprends pas, pour l'instant, prends les mêmes données que moi... Ne change rien de rien à mes propositions.
Tu changeras plus tard, quand tu auras compris. Tu veux encore sauter des étapes. Et tu ne peux pas te le permettre pour l'instant.

la table group est la nomenclature des différents groupes possibles pour chaque owner... Elle est toute petite. Appelle là group_type si tu comprends mieux. Ce sont les groupes théoriques, attribués à personne. C'est ce que tu as le moins compris, ta table group n'ayant rien à voir.

2) Du coup, c'est seulement la table group_groupowner qui organise la distribution (la liaison) de ces différents group_types avec les différents owners.

3) Que signifie cette partie de ma requête ? Encore une fois, ne change rien pour l'instant.

Code :

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

1
2
3
 
inner join user on ggu.user_id=user.id
inner join user owner on ggo.owner_id=owner.id

Je duplique la table user...
Une fois je la prends telle quelle pour faire remonter les infos de l'émetteur (user), une fois je la duplique, mais sur un autre critère, pour faire remonter les infos du récepteur (owner). Du coup, pour la lisibilité du code SQL, je la renomme owner...

Bon courage, mais n'essaie pas d'être créatif à ce stade.
Tu prendras un peu de liberté quand tu seras certain que la requête que je te propose fonctionne parfaitement.
Sinon, je vais me répéter, et encore me répéter, ad nauseam... et j'aime po ça.

[identifiant_bidon]

Hello Dendrite.
Merci beaucoup pour ta patience.
J'ai effectué cette modification de la table Groupe car il me semble qu'il y a une limite pour le cas où un user voudrait changer le nom d'un de ses groupes sans que cela n'impacte les groupes d'autres users.

Mais tu as raison. Je veux griller les étapes. Je vais appliquer stricto sensu ce que tu as donné dans un premier temps.
Merci beaucoup

[Dendrite]

Excuse-moi, mais je ne pratique pas du tout les réseaux sociaux.
C'est pas figé les types de groupe (amis, famille, indésirables) ? Chaque owner peut baptiser un groupe comme il veut genre "les blaireaux", "mes loulous" etc... ???

si c'est le cas, on règlera ça après. voyons déjà si tout marche comme ça...

[identifiant_bidon]

Oui, j'aimerai bien qu'un user puisse mettre le nom qu'il veut pour un groupe mais ce point ne sera pas sorcier je pense.

Alors, j'ai appliqué à la lettre ce que tu avais donné. Tout se passe bien.
J'ai ensuite ajouté Bea dans le groupe d'Amis d'Alain
--> Ajout de la ligne suivante dans group_groupuser :
104 2 1

J'ai ajouté le message suivant de Bea à Alain
--> Ajout de la ligne suivante dans message
1004 "message de Bea" "Coucou Alain, c Bea !!" 5 2018-07-03 18:00:00

Puisque dans le groupe d'Amis d'Alain, j'ai Denis et Bea, en tant qu'Alain je voudrais bien voir leurs messages à tous les deux. Malheuresuement ce n'est pas le cas ...

Edit : j'ai trouvé la solution à mon problème
il suffit de changer la condition (non plus filtrer sur le user mais sur le groupe)
En gros, remplacer ceci
and ggu.test_groupe_groupuser_id=5
par
and ggo.test_groupe_groupowner_id=6

[Dendrite]

Et donc ?
Tu considères que c'est résolu ?
Tu veux bien qu'on passe à la suite ? Poser les clés d'unicité et les index d'abord ?

Créee d'abord une nouvelle base que tu nommes par exemple ressoc (contraction de resau social)

Partons de la table user, il faut l'étoffer ainsi :

tale user
id, mail, login, mdp, genre, nom, prenom, naissance, inscription_date, inscription_fin

id => integer clé primaire auto increment
mail =>varchar 100 index simple (on peut imaginer que M. et Mme Dupont partagent le même mail, c'est très fréquent)
login => varchar 100 unicité (c'est ton identifiant unique)
mdp => text sera bien évidemment crypté avec password_hash
genre => enum 'H', 'F', 'I'
nom => varchar 100 index simple
prenom => var 100
naissance => date
inscription_date => datetime
inscription_fin => datetime (null autorisé)

Par convention, tu mets tous les champs en minuscule...
Ensuite quand tout est nickel, tu saisis par exemple les 23 joueurs de l'équipe de France (et leur famille), ou tous les personnages d'une série que tu aimes (les Simpsons ou Kaamelott) etc... selon ce que tu connais le mieux.
Pourquoi inscription_fin ? soit tu bannis quelqu'un, soit il te demande de le désinscrire.

Voilà pour la première table... si tu vois des trucs essentiels que j'oublie...