Android Jetpack Room关联查询排序、条数限制及最新数据获取问题

Room 默认使用@Relation做关联查询时，子表未指定排序规则会按主键升序返回数据，因此你拿到的是最早插入的消息。要返回最新的消息可直接修改查询语句，通过LEFT JOIN加分组排序实现：

@Transaction
@Query("""
    SELECT c.*, m.* FROM Contacts c
    LEFT JOIN Messages m ON c.id = m.contactsId
    WHERE c.user = :user
    GROUP BY c.id
    ORDER BY c.recentDate DESC, m.createDate DESC
""")
fun contactsMessageList(vararg user: String): Flow<List<ContactsMessages>>

上述SQL按联系人ID分组，每组取消息创建时间倒序的第一条，即为该联系人最新的关联消息。

第一步：修改关联实体为一对多结构 #

data class ContactsMessages(
    @Embedded val contacts: Contacts,
    @Relation(parentColumn = "id", entityColumn = "contactsId")
    val messages: List<Messages>
)

子表排序方案 #

如果你仅需要对子表消息排序，无需限制条数，可通过自定义查询实现子表按创建时间倒序排列：

@Transaction
@Query("""
    SELECT * FROM Contacts WHERE user = :user ORDER BY recentDate DESC;
    SELECT * FROM Messages 
    WHERE contactsId IN (SELECT id FROM Contacts WHERE user = :user)
    ORDER BY contactsId ASC, createDate DESC
""")
fun contactsWithSortedMessages(vararg user: String): Flow<List<ContactsMessages>>

Room 会自动将两个查询的结果按关联关系组装，返回的每个联系人下的消息列表默认按创建时间倒序排列。

子表排序+条数限制方案 #

如果需要每个联系人仅返回最新的N条消息（示例中为10条），可使用SQLite窗口函数实现分组条数限制：

@Query("""
    WITH ranked_messages AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY contactsId ORDER BY createDate DESC) AS rank
        FROM Messages
        WHERE contactsId IN (SELECT id FROM Contacts WHERE user = :user)
    )
    SELECT c.*, rm.* FROM Contacts c
    LEFT JOIN ranked_messages rm ON c.id = rm.contactsId
    WHERE c.user = :user AND rm.rank <= 10
    ORDER BY c.recentDate DESC, rm.createDate DESC
""")
fun contactsWithTop10Messages(vararg user: String): Flow<List<ContactsMessages>>

修改语句中rm.rank <= 10的数值即可调整每个联系人返回的最大消息条数。

内容的提问来源于stack exchange，提问作者gaohomway