From: Peter Brawley
Date: February 2 2005 4:53pm
Subject: Re: Social Networking querys
List-Archive: http://lists.mysql.com/mysql/179332
Message-Id: <420105A7.6080207@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>is, there is some method to iterate in this relation 
>to avoid joining the table in itself each time ?

Exactly the problem with trying to model an anything-goes network, a 
"world of ends", in a relational model of a directed graph. I think you 
need an XML layer in there, eg see 
http://iswc2004.semanticweb.org/demos/03/.

PB

-----

listsql listsql wrote:

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


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.4 - Release Date: 2/1/2005