query schema to get posts from users that followed by someone

Question

i want to implement following system in my application , something like twitter type of following system,

I have a table posts where all the posts by users are stored, the structure of this table is as follows:

id | user_id | title | description | image_addr | date

and this is my table_users structure :

id | email | name | password | reg_date

and this is my follow_table structure :

user_id | following_user_id

in follow table i will store data of which user follow who , for example if user_id 1 is following userid 10 then i will store this information in this table

i don't know exactly that my MySQL database structure is OK or not to get best optimization for queries , but i tried to make it on a good way , if you have any suggestion i will be happy to hear it

But now i this is my main question , how i should write the query to get posts of users that user is following ,

for example i follow 10 people , and they all have some posts in posts table , i want to write a query command to return the user posts that i followed ... i will be very very very thankful if you can help me ... thanks

Answer 1

This will return all post of all followed user by you

select * 
from posts 
where user_id in(
select distinct following_user_id 
from follow_table 
where user_id=myid)

here myid should be selected userid

Answer 2

how i should write the query to get posts of users that user is following

All you need to do is join the tables in the following way:

SELECT a.post
 FROM posts a
  JOIN users b
    ON (a.id = b.id) -- join key
  JOIN follow_users c
    ON (a.user_id = c.follow_user_id) -- join key
WHERE a.id   = <required id to filter the rows> -- filter criteria

Make sure you create appropriate indexes for the join keys and filter criteria.

Answer 3

In posts column, is ID an identity column or it equals the ID in table_users? If it is the same as the id in table_users, then use this query SELECT B.Name as PosterName,A.Description as Post,X.Name as FollowerName FROM posts A INNER JOIN TABLE_Users B ON A.ID = B.ID INNER JOIN follow_table C ON C.user_id = A.user_ID INNER JOIN Table_Users X on X.ID = A.following_user_id
^ This will return the poster name, the post itself, and the follower name. If this isn't what you need, then please clarify what you need a little bit clearer.