Friendfeed showing same relationship twice

I was building a feed system ala facebook just for the fun and training of it but I came up with an interesting problem. Imagine this scenario:

I am friend with nr1, nr2 and nr3. In my database it is also a user, nr4 in the开发者_如何学Go database who I'm not friend with. So let say nr1 befriend nr4, in that case I want my feed to get updated with nr1 is friend with nr4. This is basic not very hard to do (at least to get it working, I don't no s*** about performance :P). Then nr1 befriend nr2 but this is equivalent with nr2 befriend nr1 and that's my problem.

My friendship database works in the way that one friendship generates two records. I've read that's usually the faster way to do it.

Friendship table:

• user_id
• friend_id
• pending
• lastchange

User table:

• user_id
• user_name

I've came up with the following code:

SELECT
    u1.user_id AS friend_id,
    u1.user_name SA friend_name,
    u2.user_id AS friends_friend_id,
    u2.user_name AS friends_friend_name
FROM users AS u1
JOIN friendship AS f1
    ON u1.user_id = f1.friend_id
    && f1.user_id = {$user_id}
    && f1.friend_pending = 0
JOIN friendship AS f2
    ON u1.user_id = f2.user_id
    && f2.friend_pending = 0
JOIN users AS u2
    ON f2.friend_id = u2.user_id
WHERE TIMESTAMPDIFF(SECOND, f2.lastchange, '{$last_login}' ) < 0;

Okey what this basically does is that it get all the users I'm friend with who befriend other people since my last login. The only problem is that if I'm friend with two people who befriend each other both of them will be selected in this query. The difference (which makes it impossible to use SELECT DISTINCT) is that;

• the first time nr1 will be the friend and nr 2 the friends friend and

• the second time nr2 will be the friend and nr1 will be the friends friend.

I don't want that, I want one row per relationship.

You guys have any idea how to solve this? Any solution I came up with has ended with nothing being displayed :(

Thanks!

1st edit: I just came up with another problem which also is connected to this problem. In phpMyAdmin the above generate a correct table with the 4 columns. But in php (or rather codeigniter's query function) it only show two columns but adds more rows... this end up in I have no idea who befriend who..

2nd edit again: Okey I just solved the problem I presented in the first edit, I simply renamed the columns with AS. Still having the primary problem thou.

3rd edit: Stevie G was requesting some sample data, this is query result:

friend_id  |  friend_name  |   friends_friend_id  |  friends_friend_name
    1             nr1                  4                   nr4
    1             nr1                  2                   nr2     
    2             nr1                  1                   nr2

This is what I get if you follow my little story. the id = 1 is not "me", it's another user

4th edit Unfortunately I wasn't quite clear enough I think. What I want to get in the end is an array where each element contains:

• friend_id
• friend_name
• friends_friend_id
• friends_friend_name

Just like the sample data from edit 3 except the names I had before might have mislead you.. So my problem is, as you can see edit 3, that if two of my friends became friends I will get two rows for one relationship. What I want to get would be:

friend_id  |  friend_name  |   friends_friend_id  |  friends_friend_name
    1             nr1                  2                   nr2     
    2             nr1                  1                   nr2

I hope I made myself clearer! Thanks

So to me it looks like your requirements are incomplete, in that in the friend feed, if nr2 and nr1 befriend each other, do you want to:

1. See something that says "Your mutual friends nr2 and nr1 befriended each other!"
2. See something that says "Your friend nr2 befriended nr1!" based on who actually reached out for friendship first.

Scenario #1

You need another join checking if your friends' friends are also in your friends and if so, returning a boolean column named AlsoMyFriend. Then if AlsoMyFriend is 1, return a "Your mutual friends befriended each other", otherwise return a normal "Your friend befriended someone new."

Alternatively, you could do this in code, by simply checking to see if your friends' friends' ID is in your array of friends' IDs and flipping your feed statement based on that.

Scenario #2

Add a column to the friendship table *primary_mover* and set that value to the *user_id* that initiated the friendship. When looking up friends' friends filter for *where primary_mover = user_id* to only get 1 of the 2-sided relationship.

As a simple measure, you could simply add a filter to say only pull friends' friends where the friends' friend ID is greater than my friends' ID:

SELECT
    u1.user_id AS friend_id,
    u1.user_name SA friend_name,
    u2.user_id AS friends_friend_id,
    u2.user_name AS friends_friend_name
FROM users AS u1
JOIN friendship AS f1
    ON u1.user_id = f1.friend_id
    && f1.user_id = {$user_id}
    && f1.friend_pending = 0
JOIN friendship AS f2
    ON u1.user_id = f2.user_id
    && f2.friend_pending = 0
    && f2.friend_id > f2.user_id -- pulls nr1 befriending nr2, not nr2 befriending nr1
JOIN users AS u2
    ON f2.friend_id = u2.user_id
WHERE TIMESTAMPDIFF(SECOND, f2.lastchange, '{$last_login}' ) < 0;

Did you try using

SELECT DISTINCT

This is a tricky one do do in your head, but, try this:

SELECT  --Select your user details, and your direct friends details
    u1.user_id UserId,
    u1.user_name UserName,
    u2.user_id FriendsUserId,
    u2.user_name FriendsUserName
FROM users AS u1
JOIN friendship AS f1
    ON u1.user_id = {$user_id}
    && f1.friend_pending = 0
JOIN users AS u2
    ON f1.friend_id = u2.user_id
JOIN friendship AS f2
    ON f1.friend_id = f2.userid
    && f2.friend_pending = 0
WHERE TIMESTAMPDIFF(SECOND, f2.lastchange, '{$last_login}' ) < 0;
UNION 
SELECT  --Select your user details, and your friends friends details
    u3.user_id UserId,
    u3.user_name UserName,
    u4.user_id FriendsUserId,
    u4.user_name FriendsUserName
FROM users AS u3
JOIN friendship AS f3
    ON u3.user_id = {$user_id}
    && f3.friend_pending = 0
JOIN friendship AS f4
    ON f3.friend_id = f4.user_id
    && f4.friend_pending = 0 
JOIN users AS u4
    ON f3.friend_id = u4.user_id
WHERE TIMESTAMPDIFF(SECOND, f4.lastchange, '{$last_login}' ) < 0;

Assuming the results are as I think they are, the UNION should filter out the dupes as u1.user_id should always be your user_id..

Try

SELECT     
    u1.user_id AS friend_id,     
    u1.user_name SA friend_name,     
    u2.user_id AS friends_friend_id,     
    u2.user_name AS friends_friend_name 
FROM users AS u1 
JOIN friendship AS f1     
    ON u1.user_id = f1.friend_id     
     && f1.user_id = {$user_id}     
     && f1.friend_pending = 0 
JOIN friendship AS f2     
    ON u1.user_id = f2.user_id     
     && f1.user_id <> f2.friend_id 
     && f2.friend_pending = 0 
JOIN users AS u2     
     ON f2.friend_id = u2.user_id 
WHERE TIMESTAMPDIFF(SECOND, f2.lastchange, '{$last_login}' ) < 0;