[mysql] ordinare ricerca utenti, in base agli amici in comune

[fraermejo]

Salve a tutti!
Sul mio sito ho una tabella UTENTI, con id autoincrement, e una tabella AMICIZIE, strutturata così:
amico_di | id_amico

dove id_amico corrisponde all'id dell'utente che ha effettuato la richiesta di amicizia e amico_di all'id della persona di cui è amico.

Ora, sul mio sito vorrei rendere possibile effetuare la ricerca utenti, che io vorrei ordinare in base a quanti amici hanno in comune, con chi effettua la ricerca.

E' possibile fare una cosa del genere?

[fraermejo]

nessun suggerimento?

[nicola75ss]

Posta qualche coppia di id di esempio col risultato che vorresti ottenere.

[fraermejo]

Originariamente inviato da nicola75ss
Posta qualche coppia di id di esempio col risultato che vorresti ottenere.

Pincopallino (id 4) effettua una ricerca con il nome Tizio.
Ora la query è:

$query = mysql_query("SELECT id, nome FROM utenti WHERE nome LIKE \"%Tizio%\"") or die( "Errore ricerca nome: " . mysql_error() ); 

Ci sono più utenti che si chiamano Tizio, uno con id 6, un altro con id 8, 10, 23
Alcuni tra questi hanno degli AMICI in comune con Pincopallino, per esempio Tizio con id 6 ha in comune con Pincopallino Caio (id 2)
Nella tabella amicizie infatti ci sono le righe:
amico_di | id_amico
4 | 2
6 | 2
2 | 4
2 | 6

Ora io vorrei ordinare i risultati della query, in base al numero di amici comuni, magari visualizzando anche quali sono(COME AVVIENE SU FACEBOOK)

per esempio se Tizio(id 6) ha 4 amici in comune con Pincopallino, sarà prima di Tizio(id 8), che ne ha solo 2...

mi sono spiegato bene?

[nicola75ss]

Vedi se così va bene

CREATE TABLE `amici` (
  `id` int(11) NOT NULL auto_increment,
  `amico_di` int(11) default NULL,
  `id_amico` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

/*Data for the table `amici` */

insert into `amici` (`id`,`amico_di`,`id_amico`) values (1,1,2);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (2,1,3);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (3,1,4);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (4,1,5);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (5,1,6);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (6,2,3);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (7,2,4);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (8,3,10);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (9,4,2);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (10,4,3);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (11,5,2);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (12,5,3);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (13,5,4);
insert into `amici` (`id`,`amico_di`,`id_amico`) values (14,6,10);


select amico_di,group_concat(id_amico) as amici from (
select amico_di,id_amico
from amici where id_amico = any (select id_amico from amici where amico_di = 1)
and amico_di <> 1) as tab
group by amico_di

La query mostrerà tutti gli utenti che hanno amici comuni all'utente 1.

edit. Quest'altra mi sembra risolva anche i casi di reciprocità

select amico_di,group_concat(id_amico order by id_amico) as amici from (
select amico_di,id_amico from amici
union 
select id_amico,amico_di from amici 
order by id_amico ) as tab
where id_amico = any (select id_amico from amici where amico_di = 1 
		      union select amico_di from amici where id_amico = 1) 
and amico_di <> 1
group by amico_di
order by amico_di

Provale e facci sapere.

[fraermejo]

Premetto che non sono molto esperto di MySql...
Il risultato della query è:

amico_di | amici
2 |[BLOB - 5B]
3 |[BLOB - 5B]
4 |[BLOB - 5B]
5 |[BLOB - 5B]
10 |[BLOB - 3B]

E' giusto? BLOB che significa? e in php per visualizzare i risultati che scrivo?

[nicola75ss]

select amico_di,cast(group_concat(id_amico order by id_amico) as char) amici from (
select amico_di,id_amico from amici
union 
select id_amico,amico_di from amici 
order by id_amico ) as tab
where id_amico = any (select id_amico from amici where amico_di = 1 
		      union select amico_di from amici where id_amico = 1) 
and amico_di <> 1
group by amico_di
order by amico_di

[fraermejo]

il risultato è:

amico_di | amici
2 | 3,4,5
3 | 2,4,5
4 | 2,3,5
5 | 2,3,4
10 | 3,6

ti ringrazio per l'aiuto che mi hai dato , ma non ho capito bene cosa restituisce questa query...

Io dovrei ottenere una cosa di questo tipo:

Io, Luigi, cerco su RICERCA UTENTI: Francesco.
Siccome ci sono tantissime persone che si chiamano così, dovrebbe elencarle tutte, in base al numero di amici in comune che ogni Francesco ha con me. Il Francesco che ha come amici 4 dei miei amici, si troverà prima di un altro Francesco che ha come amici soltanto 2 dei miei...
Come estraggo i risultati dalla query?

[nicola75ss]

Originariamente inviato da fraermejo
il risultato è:

amico_di | amici
2 | 3,4,5
3 | 2,4,5
4 | 2,3,5
5 | 2,3,4
10 | 3,6

ti ringrazio per l'aiuto che mi hai dato , ma non ho capito bene cosa restituisce questa query...

Io dovrei ottenere una cosa di questo tipo:

Io, Luigi, cerco su RICERCA UTENTI: Francesco.
Siccome ci sono tantissime persone che si chiamano così, dovrebbe elencarle tutte, in base al numero di amici in comune che ogni Francesco ha con me. Il Francesco che ha come amici 4 dei miei amici, si troverà prima di un altro Francesco che ha come amici soltanto 2 dei miei...
Come estraggo i risultati dalla query?

Ti posto un altro dump con due tabelle, una relativa agli utenti e una alle amicizie

CREATE TABLE `amici` (
  `id` int(11) NOT NULL auto_increment,
  `amico_di` int(11) default NULL,
  `id_amico` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;


insert into `amici` (`id`,`amico_di`,`id_amico`) values 
(1,4,1),(2,4,2),(3,5,4),(4,4,6),(5,7,1),(6,2,7),(7,8,4),
(8,8,7),(9,9,1),(10,9,2),(11,9,3),(12,9,6),(13,6,4),
(14,6,7),(15,6,1),(16,6,2),(17,6,3),(18,1,2),(19,1,3),(20,4,3);


CREATE TABLE `utenti` (
  `id` int(11) NOT NULL auto_increment,
  `nome` varchar(50) default NULL,
  `cognome` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

insert into `utenti` (`id`,`nome`,`cognome`) values 
(1,'Francesco','Bianchi'),(2,'Francesco','Rossi'),(3,'Francesco','Verdi'),
(4,'Luigi','Bianchi'),(5,'Stefano','Verdi'),(6,'Francesco','Rossini'),
(7,'Mario','Rossi'),(8,'Antonio','Verdi'),(9,'Stefano','Bianchi');

La query che ho scritto è questa

select amico_di,u.nome,u.cognome,quanti from (
select amico_di,count(id_amico) as quanti from (
select amico_di,id_amico from amici
union 
select id_amico,amico_di from amici 
order by id_amico ) as tab
where id_amico = any (select id_amico from amici as a inner join utenti as u
		      on a.id_amico = u.id where u.nome = 'Francesco' and amico_di = 4 
		      union
		      select amico_di from amici as a inner join utenti as u 
		      on a.amico_di = u.id where u.nome = 'Francesco' and id_amico = 4) 
and amico_di <> 4
group by amico_di) as tab2
inner join utenti as u on tab2.amico_di = u.id
order by quanti desc

il 4 dell'esempio corrisponde all'utente che effettua la ricerca, nello specifico a Luigi Bianchi e conterà quanti amici comuni di nome Francesco ha con gli altri utenti.
Nelle mie tabelline ho messo 4 utenti di nome Francesco con id 1,2,3 e 6 e tutti sono amici di Luigi. L'utente con id 9 è amico di tutti e 4, quindi il conteggio sarà pari a 4, l'utente con id 7 invece è amico solo dei Francesco con id 1,2 e 6 e quindi avrà conteggio pari a 3 e così via.
Mi sembra che faccia esattamente ciò che hai chiesto.

[fraermejo]

grazie davvero!!