所以我有 4 个表:users、posts、private、threads。在此示例中，lizzy 在不同的线程中创建了 2 个私有(private)帖子:

“约会”帖子供用户 2、5、6 和她自己查看该线程中正确的帖子数。

“Break Ups”帖子仅供用户 2 和她自己查看该线程中正确的帖子数。

根据查看线程的用户显示正确的计数是我遇到的问题。在这里，我们关注的是 lizzy、她的线程和帖子数:

users                     (These aren't part of table. Just shows the counts we should display with our query depending on the user_id)
user_id |  user_name      //thread #: post_count-post_count_if_not_authorized = count to show 
--------------------
   1    |    tony         //thread 2: 3-1= 2 posts. thread 3: 2-1= 1 post.
   2    |    steph        //thread 2: 3-0= 3 posts. thread 3: 2-0= 2 posts.
   3    |    lizzy        //thread 2: 3 posts. thread 3: 2 posts.
   4    |    adam         //thread 2: 3-1= 2 posts. thread 3: 2-1= 1 post.
   5    |    lara         //thread 2: 3-0= 3 posts. thread 3: 2-1= 1 post.
   6    |    alexa        //thread 2: 3-0= 3 posts. thread 3: 2-1= 1 post.


posts
post_id   thread_id   user_id   post_name   private (0 is public, 1 is private to authorized users)
-----------------------------------------------------
   1         1           1       Coding        0
   2         2           3       Dating        1
   3         2           3       Show Me       0
   4         2           3       See Me        0 
   5         3           3       Break Ups     1
   6         3           3       True Love     0

private
private_id   post_id   authorized_user_id
-----------------------------------------------
    1           2               2
    2           2               5
    3           2               6
    4           5               2

threads
thread_id  user_id  post_count
------------------------------------
    1         1         1
    2         3         3  | When outputted in php, we should subtract the correct COUNT
    3         3         2  | from this depending on the user viewing the thread like above.

所以基本上，我们有一个线程总数，其中包含该线程中的所有帖子。但是，如果我们使用 mysql 查询将其提取出来，所有用户都会看到 lizzy 的每个线程的所有 post_count，而相反，只有 lizzy 和她授权查看线程上某些帖子的任何用户应该看到正确的可见非私有(private)计数他们。将计数提取为一行 (post_count_if_not_authorized) 的最有效方法是什么，以便我们可以从 post_count 中减去它以仅向每个用户显示他们的正确计数？

像下面这样的东西是我所追求的(当然不是按原样工作):

SELECT DISTINCT t.thread_id, t.post_count, t.COUNT(*)
FROM threads as t
JOIN posts as p on p.user_id = t.user_id
LEFT JOIN private pv on pv.post_id = p.post_id
WHERE t.user_id='3'
    AND (p.private = 0) OR (pv.authorized_user_id = {$logged_in_id} and p.private = 1)

更新:

(本例中 WHERE 子句中的 t.user_id='3' 用于 lizzy，如果 $logged_in_id 应根据用户给出正确的计数，如上面用户表中的计数)

这是一个fiddle .

如果 tony ($logged_in_id=1) 正在查看 lizzy (user_id=3) 启动的线程，输出应该如下所示:

thread_id    post_count_final (this should have the correct count for this user($logged_in_id) to see, all posts by lizzy - private posts by lizzy that this user is not authorized to see)
    2            2
    3            1

如果 steph ($logged_in_id=2) 正在查看 lizzy (user_id=3) 启动的线程:

thread_id    post_count_final (this should have the correct count for this user($logged_in_id) to see, all posts by lizzy - private posts by lizzy that this user is not authorized to see)
    2            3
    3            2

(注意:用户表旁边的右上部分显示了这些数字是如何得出的。)

最佳答案

SELECT 
t.thread_id, t.post_count, 
COUNT(IF(ISNULL(pv.private_id) AND p.private='1' AND p.user_id<>'1', null, 1)) 
FROM threads as t
JOIN posts as p on p.thread_id = t.thread_id 
JOIN users as u on u.user_id = p.user_id 
LEFT JOIN private as pv on pv.post_id = p.post_id AND pv.authorized_user_id='1' 
JOIN users as auth on auth.user_id = '1'
WHERE p.user_id='3' AND t.user_id='3'
GROUP BY t.thread_id;

即使 lizzy(已登录)正在查看 lizzy，这也应该有效。添加 COUNT(*) 将返回与 t.post_count 相同的值。您可以完全消除线程表的使用并在查询中一起进行计数，尽管查询会更重。

关于MySQL - 私有(private)帖子显示正确计数，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14694774/