tags:

views:

63

answers:

4

Hey Sorry the title is not very clear. This is a follow up to my earlier question where one of the members helped me with a query.

I have a following friends Table

Friend
friend_id - primary key
user_id
user_id_friend
status

The way the table is populated is - when I send a friend request to John - my userID appears in user_id and Johns userID appears in user_id_friend.

Now another scenario is say Mike sends me a friend request - in this case mike's userID will appear in user_id and my userID will appear in user_id_friend

So to find all my friends - I need to run a query to find where my userID appears in both user_id column as well as user_id_friend column

What I am trying to do now is - when I search for user say John - I want all users Johns listed on my site to show up along with the status of whether they are my friend or not and if they are not - then show a "Add Friend" button.

Based on the previous post - I got this query which does part of the job - My example user_id is 1:

SELECT u.user_id, f.status
FROM user u
LEFT OUTER JOIN friend f ON f.user_id = u.user_id and f.user_id_friend = 1
where u.name like '%'

So this only shows users with whom I am friends where they have sent me request ie my userID appears in user_id_friend.
Although I am friends with others (where my userID appears in user_id column) - this query will return that as null

To get those I need another query like this

SELECT u.user_id, f.status
FROM user u
LEFT OUTER JOIN friend f ON f.user_id_friend = u.user_id and f.user_id = 1
where u.name like '%'

So how do I combine these queries to return 1 set of users and what my friendship status with them is. I hope my question is clear

Thanks

A: 

The simplest way to combine the two queries is by performing a union:

SELECT u.user_id, f.status
FROM user u
LEFT OUTER JOIN friend f ON f.user_id = u.user_id and f.user_id_friend = 1
where u.name like '%'
UNION
SELECT u.user_id, f.status
FROM user u
LEFT OUTER JOIN friend f ON f.user_id_friend = u.user_id and f.user_id = 1
where u.name like '%'

will produce the results that you want

Anatoly Fayngelerin
Well actually it doesn't In my sample data there are only 9 users. Both queries run independently produce 9 rows with partially correct data - but when we combine them with union - they produce 15 rows and they dont take care of the issue<br/>For Example if user_id 7 is my friend - the first query will return a status of 1 and the second query will return a status of null and if the union query will return 2 rows 1 with a status of 1 and the other with status of null
Gublooo
Sorry, the status is irrelevant. I think if you took that out of your query your results would be correct. Union distinctifies the resulting rows
Anatoly Fayngelerin
hmm not sure - if we take out the status - then it will list down all the users - how would we know who is a friend and who is not
Gublooo
+1  A: 

You need to join to your friend table twice:

select u.user_id, f1.status, f2.status
from user u left outer join friend f1 on f1.user_id = u.user_id and f1.user_friend_id = 1
            left outer join friend f2 on f2.user_friend_id = u.user_id and f2.user_id = 1
where u.name like '%'

The first status should show people who have you as a friend, the second status should show people who are your friends. Not sure how this is going to help you find who are friends of a friend, though...

TMN
Thanks TMN - well the good news is I dont need to know the friends of friends right now :) - so from your query if both status 1 and status 2 are null - that means the user is not my friend and if anyone of them has a value of 1 - it means they are my friends - that will work - thanks a lot
Gublooo
A: 

Hi Gubloo,

Were able to find any simple solution for the above query.I'm looking for the same kind of solution.

Please your help is needed

spothi
A: 

Hey spothi...

This is what I'm currently using a - union of 2 queries - they are written in Zend framework but pretty straight forward to see the sql:

$select1 = $this->select()             
        ->from(array('f'=>'friend'), array('user_id_friend as friends_id'))
                ->where('user_id='.(int)$user_id)              
        ->where('status = 1');

$select2 = $this->select()             
           ->from(array('f'=>'friend'), array('user_id as friends_id'))        
           ->where('user_id_friend='.(int)$user_id)            
           ->where('status = 1');       

$select = $this->getAdapter()->select()->union(array( '('.$select1.')', '('.$select2.')'));

$stmt = $select->query();
return $stmt->fetchAll();

Hope this helps

Gublooo