时间戳

首页 > 解决方案 > Android Jetpack Room 一对多查询哪里

android-room - Android Jetpack Room 一对多查询哪里

问题描述

我有两张联系人和消息表

  1. 联系人
@Entity
data class Contacts(

    @PrimaryKey
    var id: String,

    var user: String,
    var friend: String,

    var recentMessage: String?,     
    var recentDate: Date,           
    var createDate: Date            
)

2.消息

@Entity
data class Messages(

    @PrimaryKey
    var id: String,
    var contactsId: String,

    var user: String,
    var friend: String,
    var text: String = "",

    var createDate: Date,
)

定义了一对一的查询实体


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

我的道查询方法效果很好，但是只能查询到最老的。如果查询最新的怎么办？

    @Transaction
    @Query("SELECT * FROM Contacts where user = :user order by recentDate desc")
    fun contactsMessageList(vararg user: String): Flow<List<ContactsMessages>>

我可以轻松地将其更改为一对多。如果是一对多，如何排序多的一边？如果我控制的人数越多呢？

标签： android-room

解决方案

我可以轻松地将其更改为一对多。如果是一对多，如何排序多的一边？如果我控制的人数越多呢？

@Relation 的问题在于，如果您让 Room 构建结果，它会通过获取父对象（嵌入式对象）然后通过它构建的查询获取所有子对象（@Relation 对象）来实现。无法控制。

解决此问题的一种方法是获取父母，然后分别获取独生子女。例如，这可能是通过利用以下 Dao 的：-

@Query("SELECT * FROM contacts WHERE user=:user")
abstract fun getContactsByUser(vararg user:String): List<Contacts>
  • 获取相应的联系人

和 ：-

@Query("SELECT * FROM messages WHERE contactsId=:contactsId ORDER BY createDate ASC LIMIT 1")
abstract fun orderedMessage(contactsId: String): Messages
  • 获取单个联系人的相应消息

然后使用一个函数分别使用这两个查询，例如：-

fun getOrderedContactsMessages(vararg user: String): List<ContactsMessages> {
    var rv = ArrayList<ContactsMessages>()
    for(u: String in user) {
        for (c: Contacts in getContactsByUser(u))
            rv.add(ContactsMessages(c,orderedMessage(c.id)))
    }
    return rv
}
  • 应该注意的是，上面的代码不是在接口中，而是在抽象类中编码，因此abstract fun ....如果包含在接口中，则需要抽象的函数。

工作示例

使用上述（注意，为了简洁和方便，在主线程上运行，并且字符串已用于日期）与：-

    dao.insert(Contacts("FredID","Fred","Fred's friend","recent message","2021-10-01","2021-01-01"))
    dao.insert(Contacts("MaryID","Mary","Mary's friend","recent message","2021-09-30","2021-01-01"))
    dao.insert(Contacts("JaneID","Jane","Jane's friend","recent message","2021-09-29","2021-01-01"))

    dao.insert(Messages("MSG99","FredID","Fred","This is message 99","2021-10-01","2021-10-01"))
    dao.insert(Messages("MSG98","FredID","Fred","This is message 98","2021-09-01","2021-09-01"))
    dao.insert(Messages("MSG97","FredID","Fred","This is message 97","2021-08-01","2021-08-01"))

    dao.insert(Messages("MSG89","MaryID","Mary","This is message 89","2021-09-30","2021-09-30"))
    dao.insert(Messages("MSG88","MaryID","Mary","This is message 88","2021-09-01","2021-09-01"))
    dao.insert(Messages("MSG87","MaryID","Mary","This is message 87","2021-08-01","2021-08-01"))

    dao.insert(Messages("MSG79","JaneID","Jane","This is message 79","2021-09-29","2021-09-29"))
    dao.insert(Messages("MSG78","JaneID","Jane","This is message 78","2021-09-01","2021-09-01"))
    dao.insert(Messages("MSG77","JaneID","Jane","This is message 77","2021-08-01","2021-08-01"))

    for(cm: ContactsMessages in dao.getOrderedContactsMessages("Fred","Mary")) {
        Log.d("MYINFO","ID is ${cm.contacts.id} recent message is ${cm.contacts.recentMessage} ordered message is ${cm.messages!!.text}")
    }

然后写入日志的结果是：-

D/MYINFO: ID is FredID recent message is recent message ordered message is 2021-08-01
D/MYINFO: ID is MaryID recent message is recent message ordered message is 2021-08-01

如果orderedMessage更改为 DESC 而不是 ASC 则：-

D/MYINFO: ID is FredID recent message is recent message ordered message is 2021-10-01
D/MYINFO: ID is MaryID recent message is recent message ordered message is 2021-09-30
  • 效率方面，有办法通过单个查询来做到这一点，但 SQL 会更复杂，它会使用嵌入消息的不同 POJO，然后需要消除类似命名的列的歧义。

推荐阅读