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:
select
f1.user_id,
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
else
case when f2.freund_id is not null and f3.freund_id is null then 2
else
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 😉
