Using a Friends Lookup Table

[hyuugurt]

Hi,

I'm building out a social network system, and when trying to pull user info for a user's friends, I'm stumbling into a problem with the query. The issue is I'm trying to keep the lookup table from listing redundant entries. For instance,

FRIENDS TABLE
-------------
 uid  | fid 
-------------
 1    | 2   
 2    | 1

That's what I'm trying to avoid, so I want the query to look for the user's id in either the uid field and return the fid or find it in the fid and return the uid. Here's what I have. I've tried a bunch of different things, but so far nothing's worked.

SELECT username FROM users WHERE id = (SELECT fid as id FROM friends WHERE uid = {$this->id}) OR (SELECT uid as id FROM friends WHERE fid = {$this->id})

Is there any way to do this or do I need to just use redundant listings in my lookup table?

[finito]

SELECT username FROM users Inner Join friends ON  users.id = freinds.uid WHERE uid = {$this->id} OR fid = {$this->id}

There may be a syntax error here. I am assuming that you have only one primary key on both users and friends.

[raghuram1987]

one way would be

select username from users where uid in (select uid from friends where fid=this.id union select fid from friends where uid=this.id)

Replace this.id with the id for which you want to check for

[urtrivedi]

this will give friends list of this->uid

SELECT username FROM users WHERE id in (SELECT fid as id FROM friends WHERE uid = {$this->id})

if you want to list name of this->uid with friends then try following

SELECT username FROM users WHERE id in (SELECT fid as id FROM friends WHERE uid = {$this->id}) or id={$this->id}

[tcatt]

First time posting here, signed up to respond to this thread as I'm working out a solution to this very same question. I have no idea how this post will look ;P

So, the tables I'm using look something like this..

My User Accounts table (simplified):

CREATE TABLE `user_accounts` (
  `id`               serial,
  `user`             varchar(255)   NOT NULL,
  `pass`             varchar(80)    NOT NULL,
  `disabled`         tinyint(1)     DEFAULT 0,
   PRIMARY KEY      (`id`)
);

My User Contacts table (simplified):

CREATE TABLE `user_contacts` (
  `friend`           bigint(20)     UNSIGNED NOT NULL DEFAULT 0,
  `userid`           bigint(20)     UNSIGNED NOT NULL DEFAULT 0,
  `block`            tinyint(1)     DEFAULT 0,
  `class`            varchar(8)     DEFAULT 'friend',
   PRIMARY KEY `id` (`userid`,`friend`),
   INDEX `contact`  (`friend`,`userid`)
);

There are advantages to using a paired association, as the OP illustrated:
(1) The pair of id associations *must* exist for the relationship to exist.
(2) I can use this structure to alert User2 that User1 wishes to create a contact.
(3) Each user will be able to classify their contacts independently of one another.

The disadvantage is of course that the queries are a little more difficult :)

A query to retrieve an id list of my contacts (assuming my id is 1):

SELECT uc1.friend AS id 
FROM user_contacts uc1 
INNER JOIN user_contacts uc2 ON uc2.userid=uc1.friend 
LEFT JOIN user_accounts ua ON ua.id=uc1.friend 
WHERE ua.disabled=0 
AND (uc1.userid=1 AND uc2.friend=1) 
AND (uc1.block=0 AND uc2.block=0)

I've found this query works well enough for me when I utilize it as a subquery for performing other tasks, such as pulling content from a table based on who my friends are. Something like this (again, assuming my id is 1):

SELECT id,userid,heading,content,created 
FROM user_content 
WHERE userid IN (
  using the exact query from above
) ORDER BY created

This will extract content owned by anyone in my contact list. There are probably more efficient/elegant means of accomplishing all this, I'm certainly not an expert.. if anyone knows a better way, let me know!

Cheers