我下面的WHERE子句假设是获取所有状态更新，并根据阻止表检查发布它们的用户。
只要该用户没有被登录用户阻止，就可以显示状态。
WHERE子句当前要求如果显示状态，则该块不应处于活动状态，这是正确的。
问题是，当前，如果任何用户对某人进行了活动阻止，则结果将不会显示为活动，并说是。
例：
接纳3位使用者；汤姆，比尔和约翰。
正在显示所有状态更新。
如果Tom阻止Bill，Tom将不再能看到Bill的更新，而Bill也不会看到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
";
最佳答案
作为解释...
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