php - How to apply privacy settings on posts the appear in a news feed with MySQL? -

what trying achieve complex privacy routine, have following tables.

posts

posts_privacy

privacy_global

followerlist

we have news feed sort of thing on our site, gets 10 latests posts people user following, there 3 levels of privacy, first privacy_global user can set privacy options on posts , 0 , 1 , 2 "only me", "everyone", "followers only" respectively, there posts_privacy user can choose list people wants show or hide post before posting it, setting overrides privacy_global settings , posts_privacy instead if set.

now problem that, when apply privacy in news feed using php ask posts this.

select `post`.*,   `users`.`fullname`,   `users`.`username`,   `posts_privacy`.`hide`,   `posts_privacy`.`show`, `post`   left join `posts_privacy` on `post`.`id`=`posts_privacy`.`postid`   inner join `users` on `post`.`userid` = `users`.`id` (`post`.`userid` in (1,2,3,4,5,6)  , 12 not in (   select `hide` `posts_privacy` `postid`=`post`.`id`)   or `show`= 12 )   group `post`.`id`  order `time` desc limit 10" 

i cant figure out how apply other conditions, before using query , returning serialized arrays of people post needs hidden or shown to, unserialized , proccessed in php , removed post array before being sent browser, created hole in news feed, instead of loading 10 posts 6 posts appear becasue php removed 4 had privacy before made browser, goal here privacy conditions in sql before posts leave database, dont have issue counts of posts in newsfeed.

i tried looking mysql functions , procedures told cant process queries or return tables.

any advice how approach issue?

edit: if helps, system trying make simlar on facebook, user can set post specfic privacy, has global settings overridded when done.

---

structure of tables:

followerslist:

id primary key bigint(20)
userid bigint(20) << id of user clicks follow button
targetid bigint(20) << id of user gets followed

post:

id primary key bigint(20)
content text
title varchar(100)
time datetime
userid bigint(20)

posts_privacy:

id primary key bigint(20)
postid bigint(20)
hide bigint(20)
show bigint(20)

the following 1 way results after. need careful testing though, i've run out of time go further it. based on perspective of viewer - user requesting posts have permission view. it's not perfect, please treat build.

some of fields query returns not needed in final version, handy testing:

set @viewerid := 5; select   p.id post_id,   owner.id owner_id,   pg.privacy privacy_global,   pp.hide hide_from,   pp.show show_to,   f.userid viewer,   f.targetid following posts p join users owner on owner.id = p.userid join privacy_global pg on pg.postid = p.id left join posts_privacy pp on pp.postid = p.id left join followerlist f on f.userid = @viewerid ((pg.privacy = 0 , pp.show = @viewerid) or (pg.privacy = 1) or (pg.privacy = 2 , f.targetid = p.userid)) , ((pp.hide != @viewerid or pp.hide null) , (p.userid != @viewerid)) group p.id limit 10; 

during testing, might find useful break query down. following chunk lists post id, post owner, global privacy setting , hide / show user ids:

select   p.id post_id,   owner.id owner_id,   pg.privacy privacy_global,   pp.hide hide_from,   pp.show show_to posts p join users owner on owner.id = p.userid join privacy_global pg on pg.postid = p.id left join posts_privacy pp on pp.postid = p.id 

add viewer user id, , rows shown each post owner of post being followed viewer:

set @viewerid := 1; select   p.id post_id,   owner.id owner_id,   pg.privacy privacy_global,   pp.hide hide_from,   pp.show show_to,   f.userid viewer,   f.targetid following posts p join users owner on owner.id = p.userid join privacy_global pg on pg.postid = p.id left join posts_privacy pp on pp.postid = p.id left join followerlist f on f.userid = @viewerid; 

the where clauses go on narrow down these results based on per-post , global privacy settings.