Friend Connection Query

[ensnare]

Hey everyone --

I currently have a table friends:

member_id (int 10),
connect_id (int 10),
active (char 1)

which contains friend relationships. When active is set to 1, the
relationship is confirmed. When it is set to 0, by default, it is not
yet confirmed.

For example:

member_id connect_id active
1 2 1 // 1 and 2 are friends
2 3 1 // 1 and 3 are friends
3 1 0 // 3 and 1 are not yet
friends.

I'm looking for an SQL query that would return a member and his
friends 3 levels deep. For example:

1 <-> 2 <-> 3

What would this query look like? What is the most efficient way to
write it? How would it change if it were 4 levels deep?

Now, to make things more complicated, I also have a table, members,
which basically looks like:

member_id (int 10),
username (varchar 20).

How could I also join the members table onto the query so I can get a
detailed result, such as:

ensnare <-> michael <-> tyra

Thanks much for your help! Have a great day.

[Geoff Berrow]

I noticed that Message-ID:
<6e************ **************@ posting.google. com> from ensnare contained
the following:

What would this query look like? What is the most efficient way to
write it? How would it change if it were 4 levels deep?

I don't think you will do it in a single query. I think you will have
to get a list of the members and for each member (in a while loop)get a
list of the friends. Repeat for each level.

You don't need an active column. If they are not friends, don't put an
entry in the table.

The last bit is a simple join.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[michel]

You don't need an active column. If they are not friends, don't put an
entry in the table.

What if the active field is used to temporarily deactivate someone from the
friend list.
You'd need the entry, but do not want them to show.
In that case...active is a valid solution.

Mich

[Chung Leong]

"Geoff Berrow" <bl******@ckdog .co.uk> wrote in message
news:6q******** *************** *********@4ax.c om...

I noticed that Message-ID:
<6e************ **************@ posting.google. com> from ensnare contained
the following:

What would this query look like? What is the most efficient way to
write it? How would it change if it were 4 levels deep?

I don't think you will do it in a single query. I think you will have
to get a list of the members and for each member (in a while loop)get a
list of the friends. Repeat for each level.

Well, you can actually, by left joining the table to itself repeatedly.

SELECT * FROM friends the_dude
LEFT JOIN friends his_friend
ON the_dude.connec t_id = his_friend.memb er_id
LEFT JOIN friends his_friends_fri end
ON his_friend.conn ect_id = his_friends_fri end.member_id
WHERE the_dude.member _id = %d
AND the_duce.active = 1
AND his_friend.acti ve = 1
AND his_friends_fri end.active = 1

[Geoff Berrow]

I noticed that Message-ID: <zb************ ********@comcas t.com> from
Chung Leong contained the following:

Well, you can actually, by left joining the table to itself repeatedly.

SELECT * FROM friends the_dude
LEFT JOIN friends his_friend
ON the_dude.connec t_id = his_friend.memb er_id
LEFT JOIN friends his_friends_fri end
ON his_friend.conn ect_id = his_friends_fri end.member_id
WHERE the_dude.member _id = %d
AND the_duce.active = 1
AND his_friend.acti ve = 1
AND his_friends_fri end.active = 1

I think I'm going to stay /well/ away from that. :-}
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[ensnare]

"michel" <[e-mail] > wrote in message news:<cb******* ***@news.cistro n.nl>...

You don't need an active column. If they are not friends, don't put an
entry in the table.

What if the active field is used to temporarily deactivate someone from the
friend list.
You'd need the entry, but do not want them to show.
In that case...active is a valid solution.

Mich

Yes, active is necessary because if a person adds a friend, the second
person must confirm that indeed persons 1 and 2 are friends. This
sets active equal to 1.

What would this query look like ?

[ensnare]

"Chung Leong" <ch***********@ hotmail.com> wrote in message news:<zb******* *************@c omcast.com>...

"Geoff Berrow" <bl******@ckdog .co.uk> wrote in message
news:6q******** *************** *********@4ax.c om...

I noticed that Message-ID:
<6e************ **************@ posting.google. com> from ensnare contained
the following:

What would this query look like? What is the most efficient way to
write it? How would it change if it were 4 levels deep?

I don't think you will do it in a single query. I think you will have
to get a list of the members and for each member (in a while loop)get a
list of the friends. Repeat for each level.

Well, you can actually, by left joining the table to itself repeatedly.

SELECT * FROM friends the_dude
LEFT JOIN friends his_friend
ON the_dude.connec t_id = his_friend.memb er_id
LEFT JOIN friends his_friends_fri end
ON his_friend.conn ect_id = his_friends_fri end.member_id
WHERE the_dude.member _id = %d
AND the_duce.active = 1
AND his_friend.acti ve = 1
AND his_friends_fri end.active = 1

The problem with this is that it only considers when member_id adds
connect_id as a friend. Friendship is bi-directional. A is friends
with B if A adds B as a friend and confirms or B adds A as a friend
and confirms.

How would this affect the query?

Thanks again.

[Andy Hassall]

On 29 Jun 2004 20:21:10 -0700, en*****@gmail.c om (ensnare) wrote:

For example:

member_id connect_id active
1 2 1 // 1 and 2 are friends
2 3 1 // 1 and 3 are friends
3 1 0 // 3 and 1 are not yet
friends.

I'm looking for an SQL query that would return a member and his
friends 3 levels deep. For example:

1 <-> 2 <-> 3

How do you want the result, in one row?
What about the equivalent rows 1,3,2 2,1,3 2,3,1 etc.?

Or given a member_id do you want one row per friend or friend-of-friend to x
levels?

--
Andy Hassall <an**@andyh.co. uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

[Chung Leong]

"ensnare" <en*****@gmail. com> wrote in message
news:6e******** *************** ***@posting.goo gle.com...

"Chung Leong" <ch***********@ hotmail.com> wrote in message

news:<zb******* *************@c omcast.com>...

"Geoff Berrow" <bl******@ckdog .co.uk> wrote in message
news:6q******** *************** *********@4ax.c om...

I noticed that Message-ID:
<6e************ **************@ posting.google. com> from ensnare contained the following:

>What would this query look like? What is the most efficient way to
>write it? How would it change if it were 4 levels deep?

I don't think you will do it in a single query. I think you will have
to get a list of the members and for each member (in a while loop)get a list of the friends. Repeat for each level.

Well, you can actually, by left joining the table to itself repeatedly.

SELECT * FROM friends the_dude
LEFT JOIN friends his_friend
ON the_dude.connec t_id = his_friend.memb er_id
LEFT JOIN friends his_friends_fri end
ON his_friend.conn ect_id = his_friends_fri end.member_id
WHERE the_dude.member _id = %d
AND the_duce.active = 1
AND his_friend.acti ve = 1
AND his_friends_fri end.active = 1

The problem with this is that it only considers when member_id adds
connect_id as a friend. Friendship is bi-directional. A is friends
with B if A adds B as a friend and confirms or B adds A as a friend
and confirms.

Database joins are uni-directional unfortunately :-) You would need to
either put in two records per friendship, or use three more queries to fetch
the reverse relationship.

Probably makes more sense to run a query at each level:

SELECT * FROM friends the_dude, friends his_friend
WHERE ((the_dude.acti ve = 1 AND the_dude.connec t_id = his_friend.memb er_id)
OR (his_friend.act ive = 1 AND his_friend.conn ect_id = the_dude.member _id))
AND the_dude.member _id IN ( ... )

[ensnare]

Andy Hassall <an**@andyh.co. uk> wrote in message news:<vp******* *************** **********@4ax. com>...

On 29 Jun 2004 20:21:10 -0700, en*****@gmail.c om (ensnare) wrote:

For example:

member_id connect_id active
1 2 1 // 1 and 2 are friends
2 3 1 // 1 and 3 are friends
3 1 0 // 3 and 1 are not yet
friends.

I'm looking for an SQL query that would return a member and his
friends 3 levels deep. For example:

1 <-> 2 <-> 3

How do you want the result, in one row?
What about the equivalent rows 1,3,2 2,1,3 2,3,1 etc.?

Or given a member_id do you want one row per friend or friend-of-friend to x
levels?

this is the full current query:

$query = "SELECT m1.username as member_username ,
ss1.symbol as member_symbol,
m2.username as friend1_usernam e,
ss2.symbol as friend1_symbol,
m3.username as friend2_usernam e,
ss3.symbol as friend2_symbol
FROM ".$CONFIG['tbl_members']." m1,
".$CONFIG['tbl_organizati ons']." o1,
".$CONFIG['tbl_s_symbols']." ss1

LEFT JOIN ".$CONFIG['tbl_friends']." f1
ON ( ( m1.member_id =
f1.member_id OR
m1.member_id =
f1.connect_id ) AND
f1.active = 1 )
LEFT JOIN ".$CONFIG['tbl_members']." m2
ON ( ( m2.member_id =
f1.member_id OR
m2.member_id =
f1.connect_id ) AND
m1.member_id !=
m2.member_id )
LEFT JOIN ".$CONFIG['tbl_s_symbols']." ss2
ON m2.symbol_id =
ss2.symbol_id
LEFT JOIN ".$CONFIG['tbl_friends']." f2
ON ( ( m2.member_id =
f2.member_id OR
m2.member_id =
f2.connect_id ) AND
f2.active = 1 )
LEFT JOIN ".$CONFIG['tbl_members']." m3
ON ( ( m3.member_id =
f2.member_id OR
m3.member_id =
f2.connect_id ) AND
m2.member_id !=
m3.member_id )
LEFT JOIN ".$CONFIG['tbl_s_symbols']." ss3
ON m3.symbol_id =
ss3.symbol_id

WHERE m1.random_key = '$random_key'
AND o1.subdomain = '$subdomain'
AND o1.organization _id =
m1.organization _id
AND ss1.symbol_id = m1.symbol_id";
but for some reason it returns duplicates.

the idea is this:

for the specified member (found by random_key), find his friends and
then for each of those friends, find their friends.

and, for each friend, we fetch the username frmo the members table,
the organization from the organizations table, and the symbol from the
symbols table.

but why is this returning duplicates?