mysql - 如何从 MySQL 表中查询复杂的 boolean 条件

Question

我有下表用于个人消息:

id*    sender*    recipient* del_by_rec del_by_send
-+----------+-------------+-----------+-----------+
 2     tom        beth            0          1
 5     tom        josh            0          1
 5     tom        chris           1          1
 7     ashley     burt            1          0
 8     jenna      tom             1          0
 8     jenna      ashley          0          0
 8     jenna      jenna           1          0
10     ashley     burt            0          0

在哪里

• id为消息id，
• sender 和recipient 是用户登录，
• sender 是唯一的消息 id
• 一条消息id可以有多个收件人
• del_by_rec 如果消息已被收件人从他的收件箱中删除并且
• del_by_send 如果消息已被发件人从他的发件箱中删除，则为 1。
• id-sender-recipient为主键，
• id 引用包含消息的另一个表中的主键 id。

一旦当前用户决定这样做，我需要确定从表中是否可以安全删除消息:

来自收件人的收件箱:

1. 如果发件人已经从他的发件箱中删除了邮件 (del_by_send = 1)；和
2. 如果此用户是唯一尚未从他的收件箱中删除此邮件的收件人(del_by_rec = 0 对于此用户，del_by_rec = 1 对于所有其他收件人.)

或来自发件人的发件箱:

1. 如果所有收件人都已将此邮件从他们的收件箱中删除(对于此邮件的所有收件人，del_by_rec = 1)。

否则，当前用户将通过将其相应的 del_by_rec 或 del_by_send 标志设置为 1 来将此消息标记为删除。

有没有办法有效地查询此条件

1. 查看当前消息的当前用户；或
2. 当前用户批量删除多条消息(我在考虑这种情况下要返回多行结果)。

返回 boolean 值/整数会很棒。

看在我的份上，我无法通过这个查询(假设用户是 ashley 并且她想从她的收件箱中删除消息 8):

SELECT (
    (SUM(del_by_send) > 0) # sender has deleted (at least 1)
    && ((SUM(del_by_rec) + 1) >= COUNT(id)) # only 1 recipient has not deleted yet
    && ((SELECT del_by_rec FROM msg_meta WHERE recipient = 'ashley' AND id = 8) = 0) # and that recipient is this user
)
FROM msg_meta
WHERE id = 8
GROUP BY id

1. 这似乎可以完成工作，但是否有点矫枉过正？
2. 这对多个消息 id 失败以避免昂贵的 foreach。我为收件人 burt 尝试了 WHERE id IN (7,10) 但我无法将其完全用于子查询..

帮助。谢谢大家..

Answer 1

我的建议是使用子查询在消息级别获取您想要的信息，然后对其应用逻辑。以下查询接近:

select id, (case when sum_del_by_send > 0 and (num_rec - sum_del_by_rec) <= 1
                 then 'Y'
                 else 'N'
            end) as IsSafeToDelete
from (select id,
             sum(del_by_send) as sum_del_by_send,
             sum(del_by_rec) as sum_del_by_rec,
             count(*) as num_rec
      from msg_meta
      group by id
     ) m

这不考虑当前收件人。此变体执行以下操作:

select id, (case when sum_del_by_send > 0 and (num_others - sum_del_by_others) = 1
                 then 'Y'
                 else 'N'
            end) as IsSafeToDelete
from (select id,
             sum(del_by_send) as sum_del_by_send,
             sum(case when recipient <> @RECIPIENT then del_by_rec end) as sum_del_by_others,
             sum(case when recipient <> @RECIPIENT then 1 else 0 end) as num_others,
             count(*) as num_rec
      from msg_meta
      group by id
     ) m

这一次处理所有消息。要处理特定的消息 ID，只需将子查询中的“group by id”替换为:

where id = @ID

(@RECIPIENT 和@ID 是您要为其自定义查询的值。)