mysql - Hibernate 查询获取 2 个用户之间的现有对话

Question

我有两个表来表示对话，我认为这是一个非常标准的格式。

CREATE TABLE conversation (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) DEFAULT '',
  last_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id)
) ENGINE = InnoDB;

CREATE TABLE conversation_user (
  user_id INT NOT NULL,
  conversation_id INT NOT NULL,
  conversation_is_visible BOOLEAN NOT NULL DEFAULT TRUE,
  unread BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (user_id, conversation_id),
  CONSTRAINT fk_conversation_user_user_id FOREIGN KEY (user_id) REFERENCES user (id),
  CONSTRAINT fk_conversation_user_conversation_id FOREIGN KEY (conversation_id) REFERENCES conversation (id)
) ENGINE = InnoDB;

我希望能够检查 2 个用户之间是否存在对话(如果已经存在，我不关心为 2 个以上的用户启动新对话)。

我似乎无法编写查询来选择仅包含 2 个给定用户的对话，仅此而已。然后我想将查询转换为 hibernate 查询。

例如，如果我有以下数据:

select * from conversation;
+----+------------------------+---------------------+
| id | name                   | last_modified       |
+----+------------------------+---------------------+
|  1 | test conversation 1    | 2017-02-25 15:39:01 |
|  2 | test conversation 2    | 2017-02-26 16:29:14 |
+----+------------------------+---------------------+

select * from conversation_user;
+---------+-----------------+-------------------------+--------+
| user_id | conversation_id | conversation_is_visible | unread |
+---------+-----------------+-------------------------+--------+
|       1 |               1 |                       1 |      0 |
|       1 |               2 |                       1 |      0 |
|       2 |               1 |                       1 |      1 |
|       2 |               2 |                       1 |      0 |
|       3 |               2 |                       1 |      0 |
+---------+-----------------+-------------------------+--------+

当搜索用户 1 和用户 2 之间的对话时，查询应该只显示对话 1，因为对话 2 中也包含用户 3。

我尝试了以下查询，但它显示了两个对话，因此不确定如何将其限制为仅包含这些用户的对话...

SELECT * FROM conversation 
LEFT JOIN conversation_user ON conversation.id = conversation_user.conversation_id 
WHERE user_id IN (1,2);

我可以在 hibernate 中使用此查询，然后检查对话中的对话用户集，以确保对话仅包含 2 个给定用户，但想知道是否有更有效的方法

Answer 1

试试这个

http://sqlfiddle.com/#!9/587e4/5

SELECT t1.user_id,
       t1.conversation_id,
       t1.conversation_is_visible,
       t1.unread
FROM conversation_user t1
LEFT OUTER JOIN
(SELECT user_id,conversation_id
   FROM conversation_user WHERE user_id IN (1,2)
) t2 ON t1.user_id=t2.user_id
AND t1.conversation_id=t2.conversation_id
WHERE t2.conversation_id IS NOT NULL;

如果您只想要conversation_id，请在select子句中使用distinct t1.conversation_id。