查询有条件地为每个不同的id返回一行

I am making a Reddit clone and I'm having trouble querying my list of posts, given a logged in user, that shows whether or not logged in user upvoted the post for every post. I made a small example to make things simpler.

我正在制作一个Reddit克隆版，我在查询我的帖子列表时遇到了麻烦，如果我有一个登录的用户，它会显示是否登录的用户对每个帖子都进行了向上投票。我做了一个小例子使事情更简单。

I am trying to return only one row per distinct post_id, but prioritize the upvoted column to be t > f > null.

我试图为每个不同的post_id返回一行，但将向上投票的列优先级设置为t > f > null。

For this example data:

对于这个示例数据:

> select * from post;
 id
----
  1
  2
  3

> select * from users;
 id
----
  1
  2

> select * from upvoted;
 user_id | post_id
---------+---------
       1 |       1
       2 |       1

If I am given user_id = 1 I want my query to return:

如果给定user_id = 1，我希望查询返回:

 postid | user_upvoted
--------+--------------
      1 | t
      2 | f
      3 | f

Since user1 upvoted post1, upvoted is t. Since user1 did not upvote post2, upvoted is f. Same for post3.

由于user1向上投post1，向上投t。由于user1没有向上投post2，向上投f。

Schema

CREATE TABLE IF NOT EXISTS post (
    id              bigserial,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS users (
    id              serial,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS upvoted (
    user_id         integer
        REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    post_id         bigint
        REFERENCES post(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (user_id, post_id)
);

What I tried so far

SELECT post.id as postid,
    CASE WHEN user_id=1 THEN true ELSE false END as user_upvoted
    FROM post LEFT OUTER JOIN upvoted
    ON post_id = post.id;

Which gives me:

给我:

 postid | user_upvoted
--------+--------------
      1 | t
      1 | f
      2 | f
      3 | f

Due to the join, there are two "duplicate" rows that result from the query. I want to priority the row with t > f > null. So I want to keep the 1 | t row.

由于连接，查询产生了两个“重复”行。我要优先考虑t > f > null的行。所以我想保持1 |t行。

Full script with schema+data.

完整脚本模式+数据。

3 个解决方案

#1

You should be able to do this with distinct on:

你应该能够做到这一点与明显的on:

SELECT distinct on (p.id) p.id as postid,
       (CASE WHEN user_id = 1 THEN true ELSE false END) as upvoted
FROM post p LEFT OUTER JOIN
     upvoted u
     ON u.post_id = p.id
ORDER BY p.id, upvoted desc;

#2

Since the combination (user_id, post_id) is defined unique in upvoted (PRIMARY KEY), this can be much simpler:

由于组合(user_id、post_id)在upvote(主键)中定义为惟一的，因此可以更简单:

SELECT p.id AS post_id, u.post_id IS NOT NULL AS user_upvoted
FROM   post p
LEFT   JOIN upvoted u ON u.post_id = p.id
                     AND u.user_id = 1;

Simply add user_id = 1 to the join condition. Makes perfect use of the index and should be simplest and fastest.

只需将user_id = 1添加到联接条件。充分利用索引，应该是最简单、最快的。

You also mention NULL, but there are only two distinct states in the result: true / false.

您还提到了NULL，但是结果中只有两个不同的状态:true / false。

Alternative approach

On second thought, you might be complicating a very basic task. If you are only interested in posts the current user upvoted, use this simple query instead:

仔细想想，你可能会让一项非常基本的任务变得复杂。如果你只对当前用户上传的文章感兴趣，可以使用这个简单的查询:

SELECT post_id FROM upvoted WHERE user_id = 1;

All other posts are not upvoted by the given user. It would seem we don't have to list those explicitly.

所有其他的帖子都不会被给定的用户投票通过。看起来我们不需要明确地列出这些。

SQL Fiddle.

SQL小提琴。

#3

The exists() operator yields a boolean value:

exist()操作符产生布尔值:

SELECT p.id
   , EXISTS (SELECT * FROM upvoted x
              WHERE x.post_id = p.id
                AND x.user_id = 1) AS it_was_upvoted_by_user1
FROM post p
        ;