php - 选择不同表的两列之间的差/减

Question

我正在尝试计算一条消息是否已看到/未看到以及已读/未读。

在data_chats_parties表中，有两个字段，last_seen和last_read

在 data_chats_messages 表中，有一个字段 created 表示消息的创建时间。

我需要从 created 中的时间戳中减去 last_seen 和 last_read 中的时间戳。

当对同一个表的列执行数学运算时，查询工作正常，但当我使用不同的表时，它返回一个空结果。

这是我现有的查询:

  SELECT
        data_chats.id AS chat,
        data_chats_parties.*,
       (SELECT COUNT(data_chats_parties.id) FROM data_chats_parties
        WHERE data_chats_parties.chat = data_chats.id) AS total_parties,
        data_chats_messages.created AS last_message_created,
        data_chats_messages.author AS last_message_author,
        data_chats_messages.author_type AS last_message_author_type,
        data_chats_messages.message AS last_message,

        (data_chats_messages.created - data_chats_parties.last_seen) AS unseen,
        (data_chats_messages.created - data_chats_parties.last_read) AS unread

      FROM data_chats
        INNER JOIN data_chats_parties ON data_chats_parties.chat=data_chats.id
        LEFT JOIN data_chats_messages ON data_chats_messages.chat=data_chats.id AND data_chats_messages.active=1
      WHERE
        data_chats.active=1 AND
        data_chats_parties.member=1 AND
        data_chats_parties.status >= 1
      GROUP BY data_chats_parties.chat
      ORDER BY last_message_created DESC

假设此查询按计划进行，如果 unread 或 unseen 为 > 0，则它是看不见或未读的。

理想情况下，我可以将 unread 和 unseen 设置为 1 或 0 而不是改变值，所以排序会更容易，但如果这不可能，我原来的方法就足够了。

Answer 1

尝试使用 IF ，像这样:

SELECT
    data_chats.id AS chat,
    data_chats_parties.*,
   (SELECT COUNT(data_chats_parties.id) FROM data_chats_parties
    WHERE data_chats_parties.chat = data_chats.id) AS total_parties,
    data_chats_messages.created AS last_message_created,
    data_chats_messages.author AS last_message_author,
    data_chats_messages.author_type AS last_message_author_type,
    data_chats_messages.message AS last_message,

    IF(data_chats_messages.created > data_chats_parties.last_seen,1,0) AS unseen,
    IF(data_chats_messages.created > data_chats_parties.last_read,1,0) AS unread

  FROM data_chats
    INNER JOIN data_chats_parties ON data_chats_parties.chat=data_chats.id
    LEFT JOIN data_chats_messages ON data_chats_messages.chat=data_chats.id AND data_chats_messages.active=1
  WHERE
    data_chats.active=1 AND
    data_chats_parties.member=1 AND
    data_chats_parties.status >= 1
  GROUP BY data_chats_parties.chat
  ORDER BY last_message_created DESC