Forum OpenACS Q&A: Re: Friendster style community

Posted by David Kuczek on
I think that we should really have a working "social networking" package. It would give us a competitive adventage towards other products. I do run a friendster clone in Germany and it really is interesting how social networking works or doesn't work within a different cultural set... I believe that this whole social networking arena will grow pretty sweetly in the future. I mean concerning innovative products, not only dating ;-)

My current approach (some wiiiiild tcl hack ;-) is not too efficient, so after reading Tilmann's post I set up a quick query that returns a relationship matrix up to the 3rd depth including permissions. Funny that this idea didn't appear to me earlier... well:


My table looks like this:

              Table "freunde"
    Column    |    Type    |  Modifiers
user_id      | integer      |
freund_id    | integer      |
approved_p  | character(1) | default 'f'
f_approved_p | character(1) | default 'f'


My new query looks like this:

f1.freund_id as freund_id_1,
f2.freund_id as freund_id_2,
f3.freund_id as freund_id_3,
case when f2.freund_id is null and f3.freund_id is null then 1
case when f2.freund_id is not null and f3.freund_id is null then 2
case when f2.freund_id is not null and f3.freund_id is not null then 3
end end end
) as depth
from freunde f1
  left outer join freunde f2 ON f1.freund_id = f2.user_id
    and f2.freund_id != f1.user_id
    and f2.approved_p = 't'
    and f2.f_approved_p = 't'
  left outer join freunde f3 ON f2.freund_id = f3.user_id
    and f3.freund_id != f2.user_id
    and f3.freund_id != f1.user_id
    and f3.approved_p = 't'
    and f3.f_approved_p = 't'
where f1.user_id = 3
and  f1.approved_p = 't'
and  f1.f_approved_p = 't'
order by depth asc


Question 1:
I was just thinking about adding an extra column called unique_freund_id, which would show the last freund_id in a row whenever this freund_id has not yet been show in a previous row. How could you do that? This way you would automatically have a list of freund_ids (=user_ids) that are in your network up to depth xy

Question 2:
Would this scale with lets say 100.000 users :-)??? If not, how could we tune this query?


Cheerio. I get back to studying for my financial markets exam ;-)