Discussion :

[cmoi70]

Bonjour à tous,

Je galère depuis un 2 jours sur une requête que je n'arrive pas à faire, merci de me donner quelques indications ou conseils .

Je schématise la base :
---------------------------


--
-- Structure de la table `users`
--

Code :

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

1
2
3
4
5
6
7
CREATE TABLE `users` ( 
  `user_id` mediumint(8) NOT NULL default '0', 
  `user_photo` varchar(100) default NULL, 
  `user_nom` varchar(255) default NULL, 
  `user_prenom` varchar(100) default NULL, 
    PRIMARY KEY  (`user_id`), 
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `users_friends`
--

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE `users_friends` ( 
  `friend_id` mediumint(8) NOT NULL auto_increment, 
  `user_id` mediumint(8) NOT NULL default '0', ==> celui qui demande à être un ami 
  `posterfriend_id` mediumint(8) NOT NULL default '0', ==> celui qui accepte d'être un ami 
  `comments_friend` text NOT NULL, 
  `confirm_friend` mediumint(1) NOT NULL default '0', 
  `time_friend` int(11) default NULL, 
  PRIMARY KEY  (`friend_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

Je schématise l'exemple :
------------------------------

A est ami avec B
A est ami avec C

D est ami avec B
D est ami avec C

Mon souhait est de faire une requête ( imbriquées ou pas ) qui propose à A s'il connaît D car ils ont 2 amis en commun ( proposer d'être ami avec qq1 s'ils ont au moins 2 amis en commun )


Alors, après tes tentatives qui sont restées vaines je suis arrivé à celle ci et qui ne marche pas :


Code:

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
SELECT uc . * , u.user_id, u.user_photo, u.user_nom, u.user_prenom 
FROM users_friends uc, users u 
WHERE 
(uc.user_id 
 
IN ( 
 
<! debut de la liste du connecté qui marche !> 
 
SELECT u.user_id 
FROM users_friends uc, users u 
WHERE u.user_id <> -1 
AND ( 
uc.user_id =3178    ==> l'id = du connecté 
OR uc.posterfriend_id =3178 
) 
AND ( 
u.user_id = uc.posterfriend_id 
OR u.user_id = uc.user_id 
) 
AND u.user_id !=3178 
AND uc.confirm_friend =1 
<! fin de la liste du connecté qui marche !> 
 
) 
 
OR 
uc.posterfriend_id 
 
IN ( 
 
<! debut de la liste du connecté qui marche !> 
 
SELECT u.user_id 
FROM users_friends uc, users u 
WHERE u.user_id <> -1 
AND ( 
uc.user_id =3178 
OR uc.posterfriend_id =3178 
) 
AND ( 
u.user_id = uc.posterfriend_id 
OR u.user_id = uc.user_id 
) 
AND u.user_id !=3178 
AND uc.confirm_friend =1 
<! fin de la liste du connecté qui marche !> 
 
) 
) 
 
AND ( 
uc.user_id !=3178 
OR uc.posterfriend_id !=3178 
) 
ORDER BY u.user_id DESC

Elle est tirée par les cheveux mais je suis en plein désespoir, merci de vos lumières .

[CinePhil]

Pas simple en effet !
Ce qui suit est à tester étape par étape. Je ne sais pas s'il y a plus simple mais ça doit approcher la solution.

1) Amis du connecté 3178

Code :

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

1
2
3
SELECT a.posterfriend_id AS AmiDuConnecte
FROM users_friends a
WHERE a.user_id = 3178 AND a.confirm_friend = 1

2) Nombre d'amis communs (>1) des users et de 3178 par user

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT u.user_id, COUNT(*) AS NbAmisCommuns
FROM users_friends u
INNER JOIN (
    SELECT a.posterfriend_id AS AmiDuConnecte
    FROM users_friends a
    WHERE a.user_id = 3178 AND a.confirm_friend = 1
) t1 ON u.posterfriend_id = t1.AmiDuConnecte
WHERE u.user_id <> 3178 AND u.confirm_friend = 1
GROUP BY u.user_id
HAVING COUNT(*) > 1

3) Amis des users ci-dessus

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
SELECT b.user_id AS UserAConnaitre, b.posterfriend_id AS AmiDuUserAConnaitre
FROM users_friends b
WHERE b.confirm_friend = 1 AND b.user_id IN (
    SELECT user_id 
    FROM (
        SELECT u.user_id, COUNT(*) AS NbAmisCommuns
        FROM users_friends u
        INNER JOIN (
            SELECT a.posterfriend_id AS AmiDuConnecte
            FROM users_friends a
            WHERE a.user_id = 3178 AND a.confirm_friend = 1
        ) t1 ON u.posterfriend_id = t1.AmiDuConnecte
        WHERE u.user_id <> 3178 AND u.confirm_friend = 1
        GROUP BY u.user_id
        HAVING COUNT(*) > 1
    ) uac
)

4) Amis communs de 3178 et des users ci dessus

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
SELECT t2.UserAConnaitre AS ConnaissancePotentielle, t1.AmiDuConnecte AS AmiCommun
FROM (
    SELECT a.posterfriend_id AS AmiDuConnecte
    FROM users_friends a
    WHERE a.user_id = 3178 AND a.confirm_friend = 1
) t1
INNER JOIN (
    SELECT b.user_id AS UserAConnaitre, b.posterfriend_id AS AmiDuUserAConnaitre
    FROM users_friends b
    WHERE b.confirm_friend = 1 AND b.user_id IN (
        SELECT user_id 
        FROM (
            SELECT u.user_id, COUNT(*) AS NbAmisCommuns
            FROM users_friends u
            INNER JOIN (
                SELECT a.posterfriend_id AS AmiDuConnecte
                FROM users_friends a
                WHERE a.user_id = 3178 AND a.confirm_friend = 1
            ) t1 ON u.posterfriend_id = t1.AmiDuConnecte
            WHERE u.user_id <> 3178 AND u.confirm_friend = 1
            GROUP BY u.user_id
            HAVING COUNT(*) > 1
        ) uac
    ) 
) t2 ON t1.AmiDuConnecte = t2.AmiDuUserAConnaitre
ORDER BY t2.UserAConnaitre, t1.AmiDuConnecte

Ouf !

[Antoun]

Je vais prendre le problème d'une autre manière : la difficulté est que les relations sont orientées (il y a un demandeur et un receveur), tandis que dans la requête souhaitée, on ne tient pas compte de cette orientation. Je vous propose donc de faire une vue qui annule cette orientation en doublonnant chaque couple (user, posterfriend) par le couple inverse (posterfriend, user) :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE VIEW friendships
AS
SELECT user_id, posterfriend_id
FROM users_friends
WHERE confirm_friend = 1 
UNION ALL
SELECT posterfriend_id, user_id
FROM users_friends
WHERE confirm_friend = 1

A partir de là, il s'agit simplement d'aller chercher les amis d'amis :

Code :

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

1
2
3
4
5
6
SELECT F1.user_id, F2.posterfriend_id, COUNT(DISTINCT F2.user_id) as nb_common_friends
FROM friendships F1
  INNER JOIN friendships F2 ON F1.posterfriend_id = F2.user_id
WHERE F1.user_id = 3178 
GROUP BY F1.user_id, F2.posterfriend_id
HAVING nb_common_friends >= 2

En fait, je pense que le COUNT(DISTINCT) pourrait être remplacé par un COUNT(*), mais je ne suis pas complètement sûr...

NB : Si pour une raison ou pour une autre, on ne peut pas utiliser de vue, ça reste possible mais beaucoup moins esthétique :

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
SELECT F1.user_id, F2.posterfriend_id, COUNT(DISTINCT F2.user_id) as nb_common_friends
FROM (
  SELECT user_id, posterfriend_id
  FROM users_friends
  WHERE confirm_friend = 1 
  UNION ALL
  SELECT posterfriend_id, user_id
  FROM users_friends
  WHERE confirm_friend = 1
) F1
  INNER JOIN (
  SELECT user_id, posterfriend_id
  FROM users_friends
  WHERE confirm_friend = 1 
  UNION ALL
  SELECT posterfriend_id, user_id
  FROM users_friends
  WHERE confirm_friend = 1
) F2 ON F1.posterfriend_id = F2.user_id
WHERE F1.user_id = 3178 
GROUP BY F1.user_id, F2.posterfriend_id
HAVING nb_common_friends >= 2

[cmoi70]

Merci de vous être penchés sur le problème :


==> Antoun : tu as bien eu raison de soulever que une personne dans une relation amicale peut être le demandeur comme il peut être le receveur d'où la liste d'amis de CinePhil :

Code :

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

1
2
3
SELECT a.posterfriend_id AS AmiDuConnecte
FROM users_friends a
WHERE a.user_id = 3178 AND a.confirm_friend = 1

ne sort que les amis dont user_id = 3178 est le demandeur que j'ai changé par celle ci qui marche :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT u.user_id AS AmiDuConnecte
FROM users_friends uc, users u
WHERE 
(
uc.user_id =3178
OR uc.posterfriend_id =3178
)
AND (
u.user_id = uc.posterfriend_id
OR u.user_id = uc.user_id
)
AND u.user_id !=3178
AND uc.confirm_friend =1

En conclusion une fois avoir modifié celle de CinePhil :

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
SELECT DISTINCT t2.UserAConnaitre AS ConnaissancePotentielle
FROM (
 
SELECT u.user_id AS AmiDuConnecte
FROM users_friends uc, users u
WHERE (
uc.user_id =3178
OR uc.posterfriend_id =3178
)
AND (
u.user_id = uc.posterfriend_id
OR u.user_id = uc.user_id
)
AND u.user_id !=3178
AND uc.confirm_friend =1
)t1
INNER JOIN (
 
SELECT b.user_id AS UserAConnaitre, b.posterfriend_id AS AmiDuUserAConnaitre
FROM users_friends b
WHERE b.confirm_friend =1
AND b.user_id
IN (
 
SELECT user_id
FROM (
 
SELECT u.user_id, COUNT( * ) AS NbAmisCommuns
FROM users_friends u
INNER JOIN (
 
SELECT u.user_id AS AmiDuConnecte
FROM users_friends uc, users u
WHERE (
uc.user_id =3178
OR uc.posterfriend_id =3178
)
AND (
u.user_id = uc.posterfriend_id
OR u.user_id = uc.user_id
)
AND u.user_id !=3178
AND uc.confirm_friend =1
)t1 ON u.posterfriend_id = t1.AmiDuConnecte
WHERE u.user_id <>3178
AND u.confirm_friend =1
GROUP BY u.user_id
HAVING COUNT( * ) >1
)uac
)
)t2 ON t1.AmiDuConnecte = t2.AmiDuUserAConnaitre
ORDER BY t2.UserAConnaitre, t1.AmiDuConnecte

Qui me donne la personne dont au moins 2 de mes amis avec lequel il est ami.

Il y a peut être possibilité de la simplifier, à creuser. Déjà que j'ai un résultat cohérent c'est bien maintenant il faut que je vérifie dans des cas de figure différents pour être sure de cette requête .

[cmoi70]

Oups, dans le cas ou il y a deux personnes qui sont amis avec au moins deux de mes amis, la requête m'en sort qu'un seul .

Ps : on aurait dit un sketch de Devos

[cmoi70]

Celle d'Antoun se rapproche le plus mais elle me sort aussi "lui même ", elle me propose aussi d'être ami de moi même :

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
SELECT F1.user_id, F2.posterfriend_id, COUNT(DISTINCT F2.user_id) AS nb_common_friends
FROM (
  SELECT user_id, posterfriend_id
  FROM users_friends
  WHERE confirm_friend = 1 
  UNION ALL
  SELECT posterfriend_id, user_id
  FROM users_friends
  WHERE confirm_friend = 1
) F1
  INNER JOIN (
  SELECT user_id, posterfriend_id
  FROM users_friends
  WHERE confirm_friend = 1 
  UNION ALL
  SELECT posterfriend_id, user_id
  FROM users_friends
  WHERE confirm_friend = 1
) F2 ON F1.posterfriend_id = F2.user_id
WHERE F1.user_id = 3178 
GROUP BY F1.user_id, F2.posterfriend_id
HAVING nb_common_friends >= 2

Je cherche encore !!