On Wed, Feb 02, 2005 at 04:47:31PM +0100, 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 ?
I work at a social networking company. We store the social network as an edge graph too, but do not use SQL to run queries on it. It'd be just way too slow in huge networks. There's a custom application creted in-house written in C to do that. Hope this was at least a little useful. Also there's somebody from Friendster here, he might be able to help you also. Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
