mysql - 隐藏聊天中比不同表格中的时间戳更早的消息

Question

我有以下聊天系统的数据库结构：

请看下面的小提琴

conversations  
+-----------------+
| COLUMN_NAME     |
+-----------------+
| conversation_id |
| name            |
| created_at      |
| updated_at      |
+-----------------+

conversation_participants
+-----------------+
| COLUMN_NAME     |
+-----------------+
| id              |
| user_id         |
| conversation_id |
| deleted_at      |
+-----------------+

messages
+-----------------+
| COLUMN_NAME     |
+-----------------+
| message_id      |
| user_id         |
| conversation_id |
| message         |
| created_at      |
| updated_at      |
+-----------------+

我还根据此建议获取最后 x 条消息https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/

现在我只需要获取比时间戳更新的deleted_at消息null。

由于我使用 Laravel 预加载，因此我很难调整查询以使其在没有特定conversation_id.

我需要在查询中包含最后 x 条消息。如果我只获取特定的对话，它也可以工作

SELECT
    *
FROM
    (
    SELECT
        `messages`.*,
        @rank := IF(
            @group = conversation_id,
            @rank +1,
            1
        ) AS rank_bae5f28cab9179ba9b470ec66ec93b7b,
        @group := conversation_id AS group_bae5f28cab9179ba9b470ec66ec93b7b
    FROM
        (
    SELECT
        @rank := 0,
        @group := 0
    ) AS vars,
    messages,
    (
    SELECT
        IFNULL(
            deleted_at,
            '0000-00-00 00:00:00'
        ) AS deleted_at
    FROM
        conversation_participants
    WHERE
        conversation_participants.conversation_id = 84 AND conversation_participants.user_id = 1
    LIMIT 1
) AS deleted_at
WHERE
    messages.created_at > deleted_at
ORDER BY
    `conversation_id` ASC,
    `created_at`
DESC
) AS messages
WHERE
    `rank_bae5f28cab9179ba9b470ec66ec93b7b` <= 15 AND `messages`.`conversation_id` IN (84)

然后我尝试在此子查询中使用连接来包含“deleted_at”时间戳

SELECT
        IFNULL(
            deleted_at,
            '0000-00-00 00:00:00'
        ) AS deleted_at
    FROM
        conversation_participants
    JOIN 
        messages
    ON
        conversation_participants.conversation_id = messages.conversation_id
    WHERE
        conversation_participants.user_id = 1
    LIMIT 1

但我总是收到价值'0000-00-00 00:00:00'。deleted

deleted_at那么如果值不是，我如何获取最新消息并过滤它们null？

编辑：

澄清一下：我必须使用这个查询

SELECT
    *
FROM
    (
    SELECT
        `messages`.*,
        @rank := IF(
            @group = conversation_id,
            @rank +1,
            1
        ) AS rank_bae5f28cab9179ba9b470ec66ec93b7b,
        @group := conversation_id AS group_bae5f28cab9179ba9b470ec66ec93b7b
    FROM
        (
    SELECT
        @rank := 0,
        @group := 0
    ) AS vars,
    messages

ORDER BY
    `conversation_id` ASC,
    `created_at`
DESC
) AS messages
WHERE
    `rank_bae5f28cab9179ba9b470ec66ec93b7b` <= 15

所以 Laravel 只获取给定数量的消息，在这个例子中：15。
我知道我可以做一个循环，获取时间戳并以这种方式过滤它，但这会导致 n+2 查询问题。

我需要一种方法来为当前登录的用户应用连接，其中时间戳用于过滤最后的消息。

编辑2：

这是一个示例小提琴https://www.db-fiddle.com/f/3W2E99RYDPyebBYxcM1xyo/4

标签： mysqllaraveljoin