Need MySQL select query assistance

Question

I currently have a web app where users can share posts and connect with other users. I store each post in a table named "posts" and all of the relationships between users in a table called "relations"

The structure of "posts" is:

+-------------+-----------+---------+------------+----------------+
| post_id     | user_id   | text    | date       | privacy        | 
+-------------+-----------+---------+------------+----------------+
| 1           | 4         |  Hello  | 1/13/2014  | 2              |      
+-------------+-----------+---------+------------+----------------+

Privacy can either be 1 or 2

The structure of "relations" is:

+-------------+-----------+------------+------------+
|rel_id       | sender    | recipient  | status     |
+-------------+-----------+------------+------------+
| 1           | 17        |  4         | 1          |      
+-------------+-----------+------------+------------+

Status can either be 1 or 2

Now, I want to have a "News Feed" like page where the user can see all of the posts from the people they are either friends with (status= 2) or following (status= 1). But I am having trouble with the query. I know how to do simple select queries, but I don't think that is possible with this.

So, I would like to select all of the posts from the "posts" table where the 'user_id' is the same as 'recipient' in the "relations" table where also the sender equals '17' (I am going to use a variable). Now on top of that, if the status of that row from "relations" is '1' and the 'privacy' from the "posts" row is '2', then skip that post.

How would I write this query?

Answer 1

Use joins and where clauses, as follows:

SELECT *
  FROM posts p
  JOIN relations r ON p.user_id = r.recipient
 WHERE (r.status = 1 OR r.status = 2)
    AND (r.status != 1 OR p.privacy != 2);

For succinctness, it helps to alias the tables (eg "posts p") so that you can subsequently refer to fields from each of them specifically (eg "p.privacy").

This will join the tables, including any where relations.status is 1 or 2, yet skipping any where both relations.status is 1 and posts.privacy is 2.

Answer 2

Use joins

SELECT * FROM `posts` 
join `relations` on `recipient` = `user_id` 
WHERE `status` = 2