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.com...

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.connect_id = his_friend.member_id
LEFT JOIN friends his_friends_friend
ON his_friend.connect_id = his_friends_friend.member_id
WHERE the_dude.member_id = %d
AND the_duce.active = 1
AND his_friend.active = 1
AND his_friends_friend.active = 1

[Geoff Berrow]

I noticed that Message-ID: <zb********************@comcast.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.connect_id = his_friend.member_id
LEFT JOIN friends his_friends_friend
ON his_friend.connect_id = his_friends_friend.member_id
WHERE the_dude.member_id = %d
AND the_duce.active = 1
AND his_friend.active = 1
AND his_friends_friend.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.cistron.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********************@comcast.com>...

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

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.connect_id = his_friend.member_id
LEFT JOIN friends his_friends_friend
ON his_friend.connect_id = his_friends_friend.member_id
WHERE the_dude.member_id = %d
AND the_duce.active = 1
AND his_friend.active = 1
AND his_friends_friend.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.com (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.google.c om...

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

news:<zb********************@comcast.com>...

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

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.connect_id = his_friend.member_id
LEFT JOIN friends his_friends_friend
ON his_friend.connect_id = his_friends_friend.member_id
WHERE the_dude.member_id = %d
AND the_duce.active = 1
AND his_friend.active = 1
AND his_friends_friend.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.active = 1 AND the_dude.connect_id = his_friend.member_id)
OR (his_friend.active = 1 AND his_friend.connect_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.com (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_username,
ss2.symbol as friend1_symbol,
m3.username as friend2_username,
ss3.symbol as friend2_symbol
FROM ".$CONFIG['tbl_members']." m1,
".$CONFIG['tbl_organizations']." 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?

[FLEB]

Regarding this well-known quote, often attributed to Chung Leong's famous
"Thu, 1 Jul 2004 07:28:35 -0400" speech:

"ensnare" <en*****@gmail.com> wrote in message
news:6e**************************@posting.google.c om...

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

news:<zb********************@comcast.com>...

"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:6q********************************@4ax.com...
> 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.connect_id = his_friend.member_id
LEFT JOIN friends his_friends_friend
ON his_friend.connect_id = his_friends_friend.member_id
WHERE the_dude.member_id = %d
AND the_duce.active = 1
AND his_friend.active = 1
AND his_friends_friend.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.active = 1 AND the_dude.connect_id = his_friend.member_id)
OR (his_friend.active = 1 AND his_friend.connect_id = the_dude.member_id))
AND the_dude.member_id IN ( ... )

Would it work better just to have 2 entries (one each way) for each
"friendship", in a simple table? To make or break a friendship would take a
bit more work, but the (probably) more common act of searching for links
would be easier.

--
-- Rudy Fleminger
-- [e-mail]
(put "Hey!" in the Subject line for priority processing!)
-- http://www.pixelsaredead.com