requêtes imbriquées !

05 févr. 2009, 17:26

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 :
---------------------------

Code : Tout sélectionner

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

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`
-- 

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 : Tout sélectionner

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 .

06 févr. 2009, 18:17

Petite solution:

Ma conception des tables:

Code : Tout sélectionner

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`)
  );

CREATE TABLE `friends` (
  `user1_id` mediumint(8) NOT NULL,
  `user2_id` mediumint(8) NOT NULL,
    PRIMARY KEY  (`user1_id`,  `user2_id`)
  );

En fait, j'ai simplifié la structure de la table "friends" pour la compréhension. Ce qui donne à la table "friends" le rôle de collecteur de couples d'amis. User1_id désigne le user qui a comme ami un user désigné par User2_id.

User1_id et User2_id sont donc des clés étrangères qui se mettent en relation avec la table "users" pour obtenir des informations supplémentaires sur les users amis.

Pour la requête qui determine les users pouvant être amis car ils partagent des amis communs, il faut procéder par découpage.

1. La requête qui détermine les amis de X (qui est l'id d'un user donné):

Code : Tout sélectionner

select user1_id, user2_id
from friends
where user1_id = X

2. La requête qui détermine les amis des amis de X (qui est l'id d'un user donné):

Code : Tout sélectionner

select distinct r1.user1_id, f.user1_id
from 	
	( select user1_id, user2_id
		from friends
		where user1_id = X) as r1
join friends as f ON r1.user2_id = f.user2_id
AND r1.user1_id <> f.user1_id
order by 1,2

La sous-requête r1, détermine les amis de X et la requête principale détermine les amis communs aux amis de X, puis affiche les users trouvés y compris X.
Le mot réservé DISTINCT du select permet d'éliminer les doublons dûs aux amis communs et la condition : r1.user1_id <> f.user1_id permet déviter de considérer un user devant être ami à lui-même.

Conclusion: La sélection affiche des couples de user pouvant être amis car ils ont des amis en commun.

Attention, je n'ai pas encore pris en compte la règle sur le nombre minimal d'amis en commun.

Jeu d'exemple:

Code : Tout sélectionner

insert into users values  
 (1, 'photo de dupont jean-pierre.jpg', 'Dupont', 'Jean-pierre'),
 (2, 'photo de leroy alain.jpg', 'Leroy', 'Alain'),
 (3, 'photo de duval aline.jpg', 'Duval', 'Aline'),
 (4, 'photo de petit alexandre.jpg', 'Petit', 'Alexandre');

insert into friends values 
 (1, 2),
 (1, 3),
 (4, 2),
 (4, 3);

Ce qui veut dire, que Dupont et Petit doivent être affichés par la requête car ils ont des amis en commun qui sont : Leroy et Duval.

Et effectivement en exécutant la requête avec le paramètre X=1 qui désigne Dupont, on obtient bien le couple : 1 et 4. Ce qui permet de dire que c'est Petit (4) qui partage des amis avec Dupont.

Simplification:
Une fois comprise, cette requête qu'on a composé en deux requêtes jointes pour la pédagogie, on peut la simplifier en remarquant que les 2 requêtes puisent de la même table "friends", elles font en faite une simple jointure réflexive entre 2 lots de la même table "friends" et qui permet de créer des couples selon 2 conditions :

1. Le champ 2 qui désigne l'ami d'un user dans un premier lot de "friends" est comparé au champ 2 correspondant à un ami d'un autre user dans un second lot de "friends"
2. La deuxième condition veille à ce que la comparaison faite par la première condition ne prenne pas en considération un ami désignant le même user traité.

La requête devient alors :

Code : Tout sélectionner

SELECT DISTINCT f1.user1_id, f2.user1_id
FROM friends AS f1
JOIN friends AS f2 ON f1.user2_id = f2.user2_id
AND f1.user1_id <> f2.user1_id
ORDER BY 1 , 2

Ici, j'ai enlevé la spécification du paramètre X, pour que la requête fasse la recherche pour tous les users et non seulement pour un user donné.

Dans l'absolu, cette requête affiche tous les couples possibles ayant des amis en commun.

Reste à compter et empêcher l'affichage des couples dont les amis communs sont < 2

07 févr. 2009, 15:03

Merci de t'être cassé la tête dessus

la difficulté est que les relations sont orientées (il y a un demandeur et un receveur),

J'ai fini avec de l'aide par arriver à cette requête qui propose au connecté ( session ) des amis de ses amis dont au mois 2 de ses amis qui le sont .

Code : Tout sélectionner

SELECT u.user_id, u.user_photo,u.user_nom, u.user_prenom
FROM users u
WHERE user_id IN (
SELECT F2.posterfriend_id
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 = $l_login_id AND F2.posterfriend_id <> $l_login_id
GROUP BY F1.user_id, F2.posterfriend_id
HAVING COUNT(DISTINCT F2.user_id) >= 2
)
 
	      ORDER BY u.user_id DESC 
		LIMIT 2

requêtes imbriquées !

Qui est en ligne