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")
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
