php - 使用无限滚动时如何获取最后加载的 ID，按列组合两个日期列排序？

Question

我有消息表，我在其中存储消息创建日期和消息计划日期（以防稍后发送给收件人）

示例MySQL表数据如下所示：

messages
------------------------------------------------------
id |message     |created_at   |scheduled_at |posted_by
------------------------------------------------------
1  |Lorem ipsum |2019-12-23   |2019-12-26   |1
2  |Lorem ipsum |2019-12-23   |NULL         |1
3  |Lorem ipsum |2019-12-24   |2019-12-25   |1
4  |Lorem ipsum |2019-12-24   |NULL         |1
5  |Lorem ipsum |2019-12-25   |NULL         |1

message_recipients
---------------------
message_id  |user_id
---------------------
1           |2
1           |3
1           |4
2           |2
2           |3
2           |2
3           |2
4           |2
5           |2
5           |5

users
------------------
id |name
------------------
1  |Lorem ipsum A
2  |Lorem ipsum B
3  |Lorem ipsum C
4  |Lorem ipsum D
5  |Lorem ipsum E

我正在实现无限滚动。在页面滚动到达页面末尾时，我从消息表中加载更多消息，组合created_at和schedule_at（小于当前时间戳）列，并以 DESC 顺序对该列进行排序以获得最新记录。

SELECT m.*, 
    (CASE
        WHEN m.scheduled_at IS NOT NULL THEN m.scheduled_at 
        ELSE m.created_at
    END) AS message_date
FROM messages m
LEFT JOIN message_recipients mr ON m.id = mr.message_id 
LEFT JOIN users u ON u.id = mr.user_id
WHERE (u.id = 1) AND (m.scheduled_at IS NULL OR m.scheduled_at < CURRENT_TIMESTAMP)
ORDER BY message_date DESC
LIMIT 10

问题是，我不能通过最后获取的消息 ID 来获取大于最后获取的消息 ID 的下一条记录，因为由于排序是 created_at 和 schedule_at 列的组合列，所以不能按时间顺序排列。

虽然OFFSET在查询中存在性能问题，并且也无法确保在将新行添加到数据库时不会重复记录，或者在从数据库中删除时不会跳过行。

有没有其他方法可以通过查询或其他方式解决问题？

否则，将预定消息分隔到另一个表中并在预定日期使用 cron-job 插入主表（以避免从两个表中获取所有数据以合并和按日期排序）？

MariaDB: 10.1.38
PHP: 7.2.15
DoctrineExtensions: 2.4.26
jQuery: v3.3.1
AngularJS: 1.7.5

标签： phpmysqlinfinite-scroll