php - Only show results that match a conditional -

i'm attempting bit of mysql magic, , i've run wall.

essentially, code pulls relevant posts user's main "newsfeed" page. however, isn't pulling posts friends; it's pulling posts within same group in system, should (obviously) not display posts people have blocked them. understand join process, i'm having trouble figuring out right syntax latter part.

this current query:

select stories.id stories join users on stories.posterid=users.id join relationships on relationships.user1=stories.posterid users.schoolid='$school' , (relationships.rel <> '3' relationships.user2=stories.posterid) 

the "rel" field flag user-to-user "relationship"; in case, 3 means they've been blocked, though it's possible there won't relationship entry between users @ all, obvious reasons. there way go doing this?

yes, it's possible return specified result set. efficient way return type of result using anti-join pattern.

what seems missing predicate identifies user pulling newsfeed for.

without that, user (a) blocks user (b) going block stories user b other users well... no 1 able see stories user b. seems block row in relationship table meant single user 'block' stories single user.

select s.id   stories s   join users u     on u.id = s.posterid   left   join (          select r.user2            relationships r           r.rel = '3'             , r.user1 = 'me'        ) b     on b.user2 = u.id  b.user2 null    , u.schoolid='$school' 

let's break down bit, first, eliminating inline view aliased b.

this query should return stories posted users given schoolid.

select s.id   stories s   join users u     on u.id = s.posterid  u.schoolid='$school' 

now, want "block" stories users user (let's call him 'me') has chosen block.

i'm going make assumption how "blocking" works. i'm going assume if user1='me' has blocked 'user2', means no stories 'user2' should presented 'me'. represented in relationship table row of

(user1='me','user2',3) 

if that's right, means this query:

select r.user2   relationships r  r.rel = '3'    , r.user1 = 'me' 

will return list of of users have been blocked user 'me'.

the "trick" left join of result result first query, , discard rows find match. called "anti-join" pattern. note it's is null predicate eliminates matching rows.

---

followup:

q: if wanted avoid displaying "stories" both blocker , blocked, there simple modification can make...

a: if "block" row in relationship table looks this:

(user1='me',user2,3) 

we took mean user 'me' had chosen not see stories user2.

if want same row interpreted mean user 'me' choosing user2 not allowed see stories 'me', that's check need do, it's not difficult.

if go query looked users blocked 'me', (where 'me' doesn't want see stories user2)

select r.user2   relationships r  r.rel = '3'   , r.user1 = 'me' 

we can swap positions of user1 , user2 in query, this:

select r.user1   relationships r  r.rel = '3'   , r.user2 = 'me' 

and list of users have chosen block 'me'.

to exclude stories users, add query (as inline view) original query, , same anti-join pattern: "matching rows", , exclude rows found match.

in query below, new inline view assigned alias of d (for no particular reason, other 'd' looks mirror image of 'b')

select s.id   stories s   join users u     on u.id = s.posterid   left   join (          select r.user2            relationships r           r.rel = '3'             , r.user1 = 'me'        ) b     on b.user2 = u.id   left   join (          select r.user1            relationships r           r.rel = '3'             , r.user2 = 'me'        ) d     on d.user1 = u.id  b.user2 null    , d.user1 null    , u.schoolid='$school' 

note: haven't tested that, seems return specified resultset, if understanding of requirement sound.

give whirl, , let me know how big of smoke ball makes.