如何在一个查询中获取以下用户内容和您自己的内容？

Question

I'm trying to make a social network; I have a table for users' posts. The table looks like:

id fromuid touid date ip content

fromuid is the user who posted the post, and touid is set to whoever you post to. So if you go on someone's page and make a post, touid is set to your UID. However, if you're home and don't post at any one touid will be set to your UID.

Now here's my problem: I'm trying to get the content of the users you're following. There's a table like so for followers:

id follower following date

Then, in my post's class, I get all the followers and put them into a predefined array like so:

        $getRelatedPostsQuery = new Database(); 
    $getFollowers = $getRelatedPostsQuery->query(array("SELECT * FROM follower WHERE follower = ?", array($this->uid)))->fetchAll(PDO::FETCH_ASSOC);
    foreach ($getFollowers as $key) {
        $followers[] = $key['following'];
    }

This is where I begin to struggle. I have this query here:

    $getRelatedPosts = new Database();
    $relatedPostsResult = $getRelatedPosts->query(array("SELECT * FROM posts WHERE fromuid IN ('". implode("', '", $followers) ."') ORDER BY id DESC", array()))->fetchAll(PDO::FETCH_ASSOC);

This works absolutely fine, but the problem is, if for example I follow a user called Jack, and a user I'm not following, say, Jill, posts on Jack's wall, I'll see what Jill posted. I obviously want to only see the content of the users I have followed. How would I best go about doing this? Also, I'm guessing this is probably an over-complicated way of doing this, yes? Any suggestions would be greatly appreciated.

Answer 1

If I'm understanding you correctly, you're wanting all the posts from only those people whom you're following, and not posts by other people to those that you're following.

If that's the case, a simple addition to your where clause should do the trick:

$relatedPostsResult = $getRelatedPosts->query(array("SELECT * FROM posts WHERE fromuid IN ('". implode("', '", $followers) ."') AND fromuid = touid ORDER BY id DESC", array()))->fetchAll(PDO::FETCH_ASSOC);

This will tell the query to only grab the posts where fromuid is the same as touid (posts where the person posted on their own "wall").

You also asked if there is a simpler way to do it, and there is if you combine the queries:

$getRelatedPosts = new Database();
$relatedPostsResult = $getRelatedPosts->query(
                                              array("SELECT * FROM posts WHERE fromuid IN(SELECT fromuid FROM follower WHERE follower = ?) AND fromuid = touid ORDER BY id DESC",
                                                      array($this->uid))
                                                    )->fetchAll(PDO::FETCH_ASSOC);

Or if you'd rather use a join:

$getRelatedPosts = new Database();
$relatedPostsResult = $getRelatedPosts->query(
                                              array("SELECT * FROM posts, follower WHERE follower.follower = posts.fromuid AND follower.follower = ? AND follower.fromuid = follower.touid ORDER BY posts.id DESC",
                                                      array($this->uid))
                                                    )->fetchAll(PDO::FETCH_ASSOC);