mysql - WHERE 子句需要一个附加子句 - 我认为

Question

我下面的 WHERE 子句假设获取所有状态更新并根据阻止表检查发布它们的用户。

只要用户未被登录用户阻止，就显示状态。

WHERE 子句当前要求如果应显示状态，则该 block 不应处于事件状态，这是正确的。

问题是，目前，如果任何用户对某人进行了主动阻止，结果将不会显示为主动说是。

示例:

带3个用户；
汤姆、比尔和约翰。

正在显示所有状态更新。
如果 Tom 阻止了 Bill，Tom 将无法再看到 Bill 的更新，并且 Bill 看不到 Tom。 -(这也是应该的。)

但现在由于 Tom 对 Bill 进行了有效阻止，John 也看不到他的更新。

我的 WHERE 子句不是很正确吗？

//Get status results
        $sql = "SELECT 

        tbl_status.id as statID, 
        tbl_status.from_user as statFROM, 
        tbl_status.status as statSTATUS, 
        tbl_status.deleted as statDEL, 
        tbl_status.date as statDATE,

        tbl_users.id as usrID, 
        tbl_users.name as usrNAME,
        tbl_users.location as usrLOCATION,
        tbl_users.postcode as usrPOSTCODE,

        tbl_blocking.id as blockID,
        tbl_blocking.user as blockUSER,
        tbl_blocking.blocking as blockBLOCKING,
        tbl_blocking.date as blockDATE,
        tbl_blocking.active as blockACTIVE

        FROM tbl_status 

        INNER JOIN tbl_users ON tbl_status.from_user = tbl_users.id
        LEFT JOIN tbl_blocking ON tbl_users.id = tbl_blocking.user

        WHERE 
        tbl_status.deleted = '0'
        AND (tbl_blocking.user IS NULL
        OR (tbl_blocking.active = '0'
        AND tbl_blocking.user != :who
        AND tbl_blocking.blocking != :who))

        ORDER BY 
        tbl_status.date desc

        LIMIT 200

        ";

Answer 1

通过解释...

 SELECT s.id        statID
      , s.from_user statFROM
      , s.status    statSTATUS
      , s.deleted   statDEL
      , s.date      statDATE

      , u.id        usrID
      , u.name      usrNAME
      , u.location  usrLOCATION
      , u.postcode  usrPOSTCODE

      , b.id        blockID
      , b.user      blockUSER
      , b.blocking  blockBLOCKING
      , b.date      blockDATE
      , b.active    blockACTIVE

   FROM tbl_status s

   JOIN tbl_users u
     ON u.id = s.from_user 

   LEFT 
   JOIN tbl_blocking b
     ON b.user = u.id
     OR (   b.active = 0      -- this might not be quite right!!!
        AND b.user != :who
        AND b.blocking != :who
        )

  WHERE s.deleted = 0
    AND b.user IS NULL

  ORDER 
     BY s.date DESC

  LIMIT 200