多对多关系，以确定用户是否喜欢一个帖子

问题描述：

我有一个包含所有帖子的表格.我还有一个表，当用户喜欢带有外键 user_id 和 post_id 的帖子时，会在其中添加一行.

I have a table that contains all of the posts. I also have a table where a row is added when a user likes a post with foreign keys user_id and post_id.

我想检索所有帖子的列表，以及特定用户是否喜欢该帖子.使用外连接，我最终会收到一些帖子两次.用户 1 一次，用户 2 一次.如果我使用 WHERE 来过滤 likes.user_id = 1 并且 likes.user_id 为 NULL，我不会得到只有其他用户喜欢的帖子.

I want to retrieve a list of ALL of the posts and whether or not a specific user has liked that post. Using an outer join I end up getting some posts twice. Once for user 1 and once for user 2. If I use a WHERE to filter for likes.user_id = 1 AND likes.user_id is NULL I don't get the posts that are only liked by other users.

理想情况下，我会使用单个查询来完成此操作.SQL 不是我的强项，所以我什至不确定是否需要子查询或连接是否足够.

Ideally I would do this with a single query. SQL isn't my strength, so I'm not even really sure if a sub query is needed or if a join is sufficient.

很抱歉这么含糊，但我认为这是一个足够常见的查询，应该有一定的意义.

Apologies for being this vague but I think this is a common enough query that it should make some sense.

我用我提到的两个查询创建了一个 DB Fiddle.https://www.db-fiddle.com/f/oFM2zWsR9WFKTPJA16U1Tz/4

I have created a DB Fiddle with the two queries that I mentioned. https://www.db-fiddle.com/f/oFM2zWsR9WFKTPJA16U1Tz/4

更新:昨晚想通了.这就是我最终的结果:

UPDATE: Figured it out last night. This is what I ended up with:

SELECT
posts.id AS post_id,
posts.title AS post_title,
CASE
WHEN EXISTS (
  SELECT *
  FROM likes
  WHERE posts.id = likes.post_id
  AND likes.user_id = 1
) THEN TRUE
  ELSE FALSE END
  AS liked
FROM posts;

虽然我能够解决它，但也感谢@wildplasser 的回答.

Although I was able to resolve it, thanks to @wildplasser for his answer as well.

答

数据(我需要稍微修改一下，因为不应该分配给连续剧):

Data (I needed to change it a bit, because one should not assign to serials):

 CREATE TABLE posts (
  id serial,
  title varchar
);

CREATE TABLE users (
  id serial,
  name varchar
);

CREATE TABLE likes (
  id serial,
  user_id int,
  post_id int
);

INSERT INTO posts (title) VALUES ('First Post');
INSERT INTO posts (title) VALUES ('Second Post');
INSERT INTO posts (title) VALUES ('Third Post');

INSERT INTO users (name) VALUES ('Obama');
INSERT INTO users (name) VALUES ('Trump');

INSERT INTO likes (user_id, post_id) VALUES (1, 1);
INSERT INTO likes (user_id, post_id) VALUES (2, 1);
INSERT INTO likes (user_id, post_id) VALUES (2, 2);

-- I want to retrieve a list of ALL of the posts and whether or not a specific user has liked that post
SELECT id, title
        , EXISTS(
                --EXISTS() yields a boolean value
                SELECT *
                FROM likes lk
                JOIN users u ON u.id = lk.user_id AND lk.post_id=p.id
                WHERE u.name ='Obama'
                ) AS liked_by_Obama
FROM posts p
        ;

结果:

 id |    title    | liked_by_obama 
----+-------------+----------------
  1 | First Post  | t
  2 | Second Post | f
  3 | Third Post  | f
(3 rows)

多对多关系，以确定用户是否喜欢一个帖子

相关推荐