List:General Discussion« Previous MessageNext Message »
From:listsql listsql Date:February 2 2005 3:47pm
Subject:Social Networking querys
View as plain text  
Since I read about Foaf [ http://www.foaf-project.org/ ], I become
interested with Social Networking, and readed a lot about it.
I 've been playing as well with mysql and join querys to represent
network's of people. As I made some queries in google and didn't came
with a lot interesting info about this, now I will comment here my own
querys.

urelation table:  (this kind of relation is called the adjacency list
model, or recursion)
+-----+-----+
| uid | fid |
+-----+-----+
|   1 |   2 |
|   2 |   3 |
|   1 |   3 |
|   3 |   2 |
|   3 |   0 |
|   0 |   2 |
|   3 |   1 |
+-----+-----+
This represent's the id of  people and the id of their friend ('s)

uprofile table: 
+-----+-----------+
| uid | name      |
+-----+-----------+
|   0 | martin 0  |
|   1 | pedro 1   |
|   2 | pablo 2   |
|   3 | matias 3  |
|   4 | mateo 4   |
|   5 | claudio 5 |
+-----+-----------+



So if I want to get the friend's and friend's of friend's  of  pablo:

SELECT p1.name  p1,p2.name  p2
FROM uprofile p1,uprofile p2
left join urelation r1 ON r1.fid=p1.uid 
and r1.uid =2 
left join urelation r2 ON r2.fid=p2.uid 
where r2.uid =r1.fid 

+----------+----------+
| p1       | p2       |
+----------+----------+
| matias 3 | martin 0 |
| matias 3 | pedro 1  |
| matias 3 | pablo 2  |
+----------+----------+

And I add logically one join more if I want to get deeper in the network.
The obvious problem is that for the first table p1 I will get the
repeating Id, but that is not an issue now.

Where I wanted to get more feedback is, there is some method to
iterate in this relation to avoid joining the table in itself each
time ?
Or also:
Is this the best way to store human-like social relations, or there is
a better way to do this ?

Thanks in advance,
-- 
Martin
 ("Welcome to saving hierarchical data in mysql: Recursion until your
head explodes")
Thread
Social Networking queryslistsql listsql2 Feb
  • Re: Social Networking querysPeter Brawley2 Feb
    • Re: Social Networking querysNick Arnett2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
  • Re: Social Networking querysNick Arnett2 Feb
  • Re: Social Networking querysMichael Dykman2 Feb
Re: Social Networking querysBruce Douglas2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
Re: Social Networking querysBruce Douglas2 Feb
Re: Social Networking querysBruce Douglas2 Feb
RE: Social Networking querysDathan Pattishall2 Feb
RE: Social Networking querysBruce Douglas2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
    • Re: Social Networking querysMark Papadakis2 Feb
RE: Social Networking querysDathan Pattishall2 Feb
Re: Social Networking querysBruce Douglas2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
Re: Social Networking queryslistsql listsql2 Feb