Inner Join Help

[derekshull]

I am trying to get all the "posts" from a users friends list on their news feed.  I'm trying to do so by getting all those posts where the user is the initiator_user_id and get all the posts from the friend_user_id then I want to get all the posts from initiator_user_id where the user's id is the friend_user_id.

This is what I have that's messing up:
 

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n1 ON (n1.initiator_user_id=f.user_id) INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id) WHERE (n1.friend_user_id=:userid) OR (n2.initiator_user_id=:userid) ORDER BY datetime DESC LIMIT 8'); 
$wallsql->bindParam(':userid', $_SESSION['uid']);
$wallsql->execute();

 

 

Previously this works but it only gets the posts where the user is the initiator_user_id:

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n2 ON n2.friend_user_id=f.user_id WHERE n2.initiator_user_id=:userid ORDER BY datetime DESC LIMIT 8'); Any help would be much appreciated!

 

[Barand]

Can you post your table structures, some sample data and what you expect to be returned from that data?

[derekshull]

wp_bp_friends table:

id     initiator_user_id     friend_user_id     is_confirmed     is_limited     date_created

11            24                            22                       1                   0          2013-12-11 11:17:26




Activity table:

ID     user_id                type                       component     filetype     group_id        content                     datetime

15        22             accountcreated                 NULL           NULL            0                NULL                2013-12-11 11:17:26

15        22                   status                          NULL           NULL            0           Hello World!          2013-12-11 11:17:26

15        24            accountcreated                  NULL           NULL             0               NULL                2013-12-11 11:17:26

15        24                   status                          NULL           NULL            0             Whats up            2013-12-11 11:17:26

So with this SQL Statement:

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n2 ON n2.friend_user_id=f.user_id WHERE n2.initiator_user_id=:userid ORDER BY datetime DESC LIMIT 8');

Userid 24 can see userid 22's "posts" such as when 22's account was created and his status update.  What it NEEDS to do is let userid 22 see userid 24's updates as well.  Right now it's one sided.  I thought that this would accomplish what I'm wanting where both users and see each others post updates:
 

$wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n1 ON (n1.initiator_user_id=f.user_id) INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id) WHERE (n1.friend_user_id=:userid) OR (n2.initiator_user_id=:userid) ORDER BY datetime DESC LIMIT 8'); 
$wallsql->bindParam(':userid', $_SESSION['uid']);
$wallsql->execute();

But it doesn't seem to be working.

[jazzman1]

Then, try something like (not tested):

$wallsql= $conn->prepare('SELECT * FROM activity f 
INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id OR n2.initiator_user_id=f.user_id)
WHERE n2.initiator_user_id = :userid 
ORDER BY f.datetime DESC LIMIT 8'); 
$wallsql->bindParam(':userid', $_SESSION['uid'],PDO::PARAM_INT);
$wallsql->execute();

PS: Perhaps, Barand will give you a better idea tomorrow morning

[derekshull]

StackOverflow for the win:
 

SELECT * 
FROM activity f 
INNER JOIN wp_bp_friends n2 
ON n2.friend_user_id=f.user_id 
WHERE n2.initiator_user_id=:userid 
UNION ALL
SELECT * 
FROM activity f 
INNER JOIN wp_bp_friends n2 
ON n2.initiator_user_id=f.user_id 
WHERE n2.friend_user_id=:userid 
ORDER BY datetime DESC LIMIT 8