选择用户未发表评论的博客帖子

Question

一篇文章有很多评论。评论(content，author_id)由用户创作。

如何检索给定用户(author_id = 1)未发表评论的博客帖子？

谢谢。

Answer 1

SELECT p.*
    FROM post p
    WHERE NOT EXISTS(SELECT 1
                         FROM comment c
                         WHERE c.post_id = p.post_id
                             AND c.author_id = 1)

您也可以使用LEFT JOIN来完成此操作：

SELECT p.*
    FROM post p
        LEFT JOIN comment c
            ON p.post_id = c.post_id
                AND c.author_id = 1
    WHERE c.post_id IS NULL

Answer 2

在常规的SQL中，如下所示：

SELECT post_id
FROM posts p
WHERE NOT EXISTS (
    SELECT 1
    FROM comments c
    WHERE p.post_id = c.post_id
      AND c.author_id = 1
)

我不确定如何使用Ruby on Rails / ActiveRecord语法来做到这一点。