获取特定用户之间的消息子集 [英] Get a subset of messages between specific users

问题描述

在我的应用中，用户

CREATE TABLE users (
    id bigserial PRIMARY KEY,
    username varchar(50) NOT NULL
);

可以发送消息

CREATE TABLE messages (
    id bigserial PRIMARY KEY,
    from_id bigint NOT NULL REFERENCES users ON DELETE RESTRICT,
    body text NOT NULL CHECK (body <> ''),
    created_at timestamp(0) NOT NULL DEFAULT LOCALTIMESTAMP(0)
);

许多收件人

CREATE TABLE message_recipients (
    message_id bigint NOT NULL REFERENCES messages ON DELETE CASCADE,
    user_id bigint NOT NULL REFERENCES users ON DELETE RESTRICT,
    PRIMARY KEY (message_id, user_id)
);

如何 SELECT 之间的所有消息至少有特定的用户子集？

How do I SELECT all messages between at least a specific subset of users?

即，如果总共有四个用户：{1、2、3、4}，而我指定的用户子集为{ 1、2、3}，然后如何获取用户1、2和3之间的所有消息，包括用户1、2、3和4之间的所有消息，不包括仅1和2之间的任何消息？ 2或1& 3或2& 3？

I.e., if there are four users total: {1, 2, 3, 4}, and the subset of users I specify is {1, 2, 3}, then how do I get all messages between users 1, 2, and 3, including all those between users 1, 2, 3, and 4, excluding any messages between only 1 & 2, or 1 & 3, or 2 & 3?

注意：我在下面提供了一个答案，但是什么是更有效的解决方案？

NOTE: I provided an answer below, but what's a more efficient solution?

推荐答案

一种替代方法是创建一个表或临时表，其中包含用户ID的特定子集（例如，可以在用户定义的函数中完成此操作）。然后可以使用以下查询：

An alternative would be to create a table or temporary table that holds the specific subset of user IDs (e.g. this could be done within a user defined function). The following query could then be used:

WITH messages_not_meeting_requirement AS
(SELECT message_id FROM
 (SELECT m.id AS message_id,
         subq.user_id
  FROM subset s
  CROSS JOIN messages m
  LEFT JOIN
  (SELECT r.message_id, r.user_id
   FROM messages AS m
   JOIN message_recipients AS r
   ON r.message_id = m.id
   UNION ALL
   SELECT m.id AS message_id, m.from_id AS userid
   FROM messages AS m) subq
  ON subq.user_id = s.user_id
  AND subq.message_id = m.id) subq2
 WHERE subq2.user_id IS NULL)
SELECT *
FROM messages AS m
WHERE m.id NOT IN (SELECT message_id FROM messages_not_meeting_requirement);

在此处查看演示： http://rextester.com/PLQI90029

这篇关于获取特定用户之间的消息子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！