Forum OpenACS Q&A: Re: Friendster style community
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:
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
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
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