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]

Re: Friend Connection Query

I noticed that Message-ID:
<6ec19901.04062 91921.259aae0a@ posting.google. com> from ensnare contained
the following:
[color=blue]
>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?[/color]

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]

Re: Friend Connection Query

[color=blue]
> You don't need an active column. If they are not friends, don't put an
> entry in the table.[/color]


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]

Re: Friend Connection Query

"Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
news:6qp4e0lsdi crq593la3loenq2 4620ckpjs@4ax.c om...[color=blue]
> I noticed that Message-ID:
> <6ec19901.04062 91921.259aae0a@ posting.google. com> from ensnare contained
> the following:
>[color=green]
> >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?[/color]
>
> 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.[/color]

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]

Re: Friend Connection Query

I noticed that Message-ID: <zbednfSvXeXHdn _dRVn-iQ@comcast.com> from
Chung Leong contained the following:
[color=blue]
>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[/color]

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]

Re: Friend Connection Query

"michel" <no@spam.please > wrote in message news:<cbtt3e$p7 0$1@news.cistro n.nl>...[color=blue][color=green]
> > You don't need an active column. If they are not friends, don't put an
> > entry in the table.[/color]
>
>
> 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[/color]


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]

Re: Friend Connection Query

"Chung Leong" <chernyshevsky@ hotmail.com> wrote in message news:<zbednfSvX eXHdn_dRVn-iQ@comcast.com> ...[color=blue]
> "Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
> news:6qp4e0lsdi crq593la3loenq2 4620ckpjs@4ax.c om...[color=green]
> > I noticed that Message-ID:
> > <6ec19901.04062 91921.259aae0a@ posting.google. com> from ensnare contained
> > the following:
> >[color=darkred]
> > >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?[/color]
> >
> > 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.[/color]
>
> 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[/color]


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]

Re: Friend Connection Query

On 29 Jun 2004 20:21:10 -0700, ensnare@gmail.c om (ensnare) wrote:
[color=blue]
>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[/color]

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 <andy@andyh.co. uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

[Chung Leong]

Re: Friend Connection Query


"ensnare" <ensnare@gmail. com> wrote in message
news:6ec19901.0 406302321.59687 424@posting.goo gle.com...[color=blue]
> "Chung Leong" <chernyshevsky@ hotmail.com> wrote in message[/color]
news:<zbednfSvX eXHdn_dRVn-iQ@comcast.com> ...[color=blue][color=green]
> > "Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
> > news:6qp4e0lsdi crq593la3loenq2 4620ckpjs@4ax.c om...[color=darkred]
> > > I noticed that Message-ID:
> > > <6ec19901.04062 91921.259aae0a@ posting.google. com> from ensnare[/color][/color][/color]
contained[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > list of the friends. Repeat for each level.[/color]
> >
> > 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[/color]
>
>
> 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.[/color]

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]

Re: Friend Connection Query

Andy Hassall <andy@andyh.co. uk> wrote in message news:<vp19e09ah td8kf2uqh48ehb3 kt67saro4e@4ax. com>...[color=blue]
> On 29 Jun 2004 20:21:10 -0700, ensnare@gmail.c om (ensnare) wrote:
>[color=green]
> >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[/color]
>
> 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?[/color]


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?

[FLEB]

Re: Friend Connection Query

Regarding this well-known quote, often attributed to Chung Leong's famous
"Thu, 1 Jul 2004 07:28:35 -0400" speech:
[color=blue]
> "ensnare" <ensnare@gmail. com> wrote in message
> news:6ec19901.0 406302321.59687 424@posting.goo gle.com...[color=green]
>> "Chung Leong" <chernyshevsky@ hotmail.com> wrote in message[/color]
> news:<zbednfSvX eXHdn_dRVn-iQ@comcast.com> ...[color=green][color=darkred]
>>> "Geoff Berrow" <blthecat@ckdog .co.uk> wrote in message
>>> news:6qp4e0lsdi crq593la3loenq2 4620ckpjs@4ax.c om...
>>> > I noticed that Message-ID:
>>> > <6ec19901.04062 91921.259aae0a@ posting.google. com> from ensnare[/color][/color]
> contained[color=green][color=darkred]
>>> > 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[/color][/color]
> a[color=green][color=darkred]
>>> > 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[/color]
>>
>>
>> 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.[/color]
>
> 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 ( ... )[/color]

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
-- sp@mmers.and.ev il.ones.will.bo w-down-to.us
(put "Hey!" in the Subject line for priority processing!)
-- http://www.pixelsaredead.com