# Thread: Friend Query

1. Registered User
Join Date
Feb 2011
Location
Posts
85

## Friend Query

Let's say you're on facebook.com/<your friend's tag here>/friends

Buttons pop up that allow you to add each person as a friend, or tell you that you're already friends with certain people. How would that query work? I can understand how it would display the list of friends, but how would it also know whether you're friends with the person or not?

If I have personId 10, and I'm looking at personId 11's friend list, how would the query look that would get me whether or not I'm friends with each person in the friends list?

2. Registered User
Join Date
Jul 2013
Posts
29
Lets assume that 'friends' table has two columns: p1 and p2, both of same data type (unsigned integer) and both represent user ID. If we have a record in this table which contains p1 = 10 and p2 = 11 we can say that users with IDs 10 and 11 are friends. Also, when inserting "10,11" row we should automatically insert "11,10" row. It is called denormalization, but it is worth doubling the data because it allows to have much simplier queries in future.

To fetch IDs of users who are both your friends and friends of some userId you can do
Code:
```select A.p2 from friends A join friends B on A.p2 = B.p1
where A.p1 = <userId> and A.p2 != <myId> and B.p2 = <myId>```
You may extend the query to fetch user data instead of only IDs:
Code:
```select C.* from friends A join friends B on A.p2 = B.p1 join C users on C.id = A.p2
where A.p1 = <userId> and A.p2 != <myId> and B.p2 = <myId>```
Now if you remove last condition from the query and select extra field, you will obtain all users that are friends of a userId:
Code:
```select C.*, B.p2 myflag
from friends A join friends B on A.p2 = B.p1 join users C on C.id = A.p2
where A.p1 = <userId> and A.p2 != <myId>```
To tell if the user is your friend also you need to check if \$users->myflag == \$myId. You may use IF() function right in the query to select myflag as ready to use boolean field, but it doesn't give neither any advantage, neither disadvantage compared to checking the equality in application/script.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

"

"

## X vBulletin 4.2.2 Debug Information

• Page Generation 0.10993 seconds
• Memory Usage 2,840KB
• Queries Executed 15 (?)
Template Usage (33):
• (3)bbcode_code
• (1)footer
• (1)forumjump
• (1)forumrules
• (1)gobutton
• (2)memberaction_dropdown
• (1)navbar
• (1)navbar_moderation
• (1)navbar_noticebit
• (1)navbar_tabs
• (2)option
• (2)postbit
• (2)postbit_onlinestatus
• (2)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (26):
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/config.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./includes/functions_calendar.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/functions_notice.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php

Hooks Called (73):
• init_startup
• friendlyurl_resolve_class
• init_startup_session_setup_start
• database_pre_fetch_array
• database_post_fetch_array
• init_startup_session_setup_complete
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_postinfo_query
• fetch_postinfo
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• strip_bbcode
• friendlyurl_clean_fragment
• friendlyurl_geturl
• forumjump
• cache_templates
• cache_templates_process
• template_register_var
• template_render_output
• fetch_template_start
• fetch_template_complete
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit
• tag_fetchbit_complete
• forumrules
• navbits
• navbits_complete