androidkotlinandroid-roomandroid-livedataandroid-room-relation

How to observe livedata of nested relation pojo android room

I have the following schema in my application (simplified for ease)

@PrimaryKey(serverId)
data class Server(
   val serverId: Long,
   val serverDescription: String
)

@PrimaryKey(siteServerId, siteSiteId)
data class Site(
   val siteServerId: Long,
   val siteSiteId: Int,
   val siteDescription: String
)

@PrimaryKey(groupServerId, groupGroupId)
data class Group(
   val groupServerId: Long,
   val groupSiteId: Int,
   val groupGroupId: Int,
   val groupDescription: String
)

@PrimaryKey(messageServerId, messageId)
data class Message(
   val messageServerId: Long,
   val messageId: String,
   val messageGroupId: Int,
   val messageBody: String,
   val messageReadStatus: Boolean
)

I want to show in the UI a list as the following example

Server Description 1 
   Site 1 -> UnReadMessages 8 
      Group 1 -> UnReadMessages 5 
      Group 2 -> UnReadMessages 3 
   Site 2 -> UnReadMessages 5 
      Group 3 -> unReadMessages 1 
      Group 4 -> unReadMessages 4 

Server Description 2 
   Site 1 -> UnReadMessages 4
      Group 1 -> UnReadMessages 1 
      Group 5 -> UnReadMessages 3

The Servers are the different Accounts that i have. There is a possibility that i could be in the same (SiteID, GroupID) from 2 Servers.

In order to achieve this i have the following pojos

data class SiteItem(
    @Embedded
    val site: Site,

    @Relation(entity = Group::class, entityColumn = "groups_site_id", parentColumn = "sites_site_id")
    val groupItems: List<GroupItem>
) {
    fun getSiteUnreadCount(): Int =
        groupItems.sumOf {
            it.getGroupUnreadCount()
        }
}

data class GroupItem(
    @Embedded
    var group: Group,

    @Relation(entity = Message::class, parentColumn = "groups_group_id", entityColumn = "messages_group_id")
    var messages: List<Message>
) {
    fun getGroupUnreadCount(): Int {
        return messages.filter { it.isIncome == 1 && it.isRead == false }
    }
}

So using the Room 2.4.2 i have the following query

@Query("""
        Select * from servers
        inner join sites on servers.server_id = sites.sites_server_id"""
    )
    fun getServerItems(): LiveData<Map<Server, List<SiteItem>>>

The expected result should have been something like that

RESULT = Map (
   key: Server(serverId: 1, serverDescription: "Server 1"),
   value: [
      SiteItem(
         site: Site(siteServerId: 1, siteSiteId: 1, siteDescr: "Site 1"),
         groupItems: [
         GroupItem(
             Group(groupServerId: 1, groupSiteId: 1, groupGroupId: 1, groupDesc: "Group 1"), List<Message> : [...messages...],
         GroupItem(
             Group(groupServerId: 1, groupSiteId: 1, groupGroupId: 2, groupDesc: "Group 2"), List<Message> : [...messages...]
      )],
      SiteItem(
         site: Site(siteServerId: 1, siteSiteId: 2, siteDescr: "Site 2"),
         groupItems: [
         GroupItem(
             Group(groupServerId: 1, groupSiteId: 2, groupGroupId: 3, groupDesc: "Group 3"), List<Message> : [...messages...],
         GroupItem(
             Group(groupServerId: 1, groupSiteId: 2, groupGroupId: 4, groupDesc: "Group 4"), List<Message> : [...messages...]
      )]
   ],


   key: Server(serverId: 2, serverDescription: "Server 2"),
   value: [
      SiteItem(
         site: Site(siteServerId: 2, siteSiteId: 1, siteDescr: "Site 1"),
         groupItems: [
         GroupItem(
             Group(groupServerId: 2, groupSiteId: 1, groupGroupId: 1, groupDesc: "Group 1"), List<Message> : [...messages...],
         GroupItem(
             Group(groupServerId: 2, groupSiteId: 1, groupGroupId: 5, groupDesc: "Group 5"), List<Message> : [...messages...]
      )]
   ]

However what i get can be shown in the following image Except the Server key and the SiteItem.sites, All the Groups are listed in the GroupItems list although the don't belong in the key.ServerId

Does anyone knows how can i solve that?

Solution

  • Original Question, to which this answer applies (as the question has been drastically changed):-

    How to observe LiveData of nested relation POJO android room The schema of my Database has 4 tables Server, Sites, Groups and Messages. Every Server has many Sites and every Site has many Groups.

    There is a POJO called GroupItem that holds the Group and the unreadMessages of the Group.

    data class GroupItem(
    @Embedded
    val group: Group,
    
    @ColumnInfo(name = "unreadCounter")
    val unreadCounter: Int
)

    The unReadCounter field can be found only with a join with the Messages table.

    I also have a POJO that holds a Site with its GroupItems

    data class SiteItem(
    @Embedded
    val site: Site,

    @Relation(entity = Group::class, entityColumn = "groups_site_id", parentColumn = "sites_site_id")
    val groupItem: List<GroupItem>
)

    So in my UI i want to display a list of a Server with all its SiteItems. So i thought of using Room 2.4.2 by observing the query

    @Query("""
        Select * from sites
        inner join servers on servers.server_id = sites.sites_server_id"""
    )
    fun getServerItems(): LiveData<Map<Server, List<SiteItem>>>

    When i try to build the project i get the error

    There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: unreadCounter)

    private final java.util.List<com.example.model.entities.pojos.GroupItem> groupItem = null

    Answer Provided

    I can understand the issue and why this is happening. Its because it cannot know how to fill up this field. I guess i have to do a double query or something in a transaction, but how can i do that since i want to return a LiveData of a Map to the UI? How can i solve this kind of a problem?

    As you have:-

    @ColumnInfo(name = "unreadCounter")
val unreadCounter: Int

    a) the @Columninfo annotation has no purpose so really you just have val unreadCounter: Int b) there will be no such column, so it would need to be generated.

    Assuming that, in addition to your description of the relationships, a Group has many messages which can be either read or unread the the following is an working example that gets the unread messages for a group and also for a site.

    It uses a function rather than a query to ascertain the unread count.

    First the @Entity annotated classes Server, Site, Group and Message for the 4 tables :-

    @Entity(tableName = "servers")
data class Server(
    @PrimaryKey
    var server_id: Long?=null,
    var server_name: String
)


@Entity(tableName ="sites")
data class Site(
    @PrimaryKey
    var site_id: Long?=null,
    var sites_server_id: Long,
    var site_name: String
)


@Entity(tableName = "groups")
data class Group(
    @PrimaryKey
    var group_id: Long?=null,
    var groups_site_id: Long,
    var group_name: String
)


@Entity(tableName = "messages")
data class Message(
    @PrimaryKey
    var message_id: Long?=null,
    var groups_group_id: Long,
    var message_name: String,
    var read_status: Boolean = false
)

    Now some POJO classes :-

    First GroupWithMessages :-

    data class GroupWithMessages(
    @Embedded
    var group: Group,
    @Relation(entity = Message::class, parentColumn = "group_id", entityColumn = "groups_group_id")
    var messages: List<Message>
) {

    fun getUnreadCounter(): Int {
        var rv: Int = 0
        for(m in messages) {
            if (!m.read_status) {
                rv++
            }
        }
        return rv
    }
}

    and second an adaptation of your SiteItem POJO :-

    data class SiteItem(
    @Embedded
    var site: Site,
    @Relation(entity = Group::class, parentColumn = "site_id", entityColumn = "groups_site_id")
    var groupList: List<GroupWithMessages>

) {
    fun getSiteUnreadCount(): Int {
        var rv: Int = 0
        for (g in groupList) {
            rv = rv + g.getUnreadCounter()
        }
        return rv
    }
}

    All of the dao functions in interface Alldao :-

    @Dao
interface AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(server: Server): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(site: Site): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(group: Group): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(message: Message): Long
    
    @Transaction
    @Query("SELECT * FROM sites INNER JOIN servers on servers.server_id = sites.sites_server_id")
    fun getServerItems(): Map<Server, List<SiteItem>>
    
}

    an @Database annotated class TheDatabase :-

    @Database(entities = [Server::class,Site::class,Group::class,Message::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

    Finally putting it all together in an activity (designed to run just the once) which inserts various data and then uses the getServerItems function to drive a loop that reports on the data:-

    const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val s1id = dao.insert(Server(server_name = "Server1"))
        val s2id = dao.insert(Server(server_name = "Server2"))

        val site1id = dao.insert(Site(sites_server_id = s1id, site_name = "Site1"))
        val site2Id = dao.insert(Site(sites_server_id = s1id, site_name = "Site2"))
        val site3Id = dao.insert(Site(sites_server_id = s2id, site_name = "Site3"))
        val site4Id = dao.insert(Site(sites_server_id = s2id, site_name = "Site4"))

        val g1id = dao.insert(Group(groups_site_id = site1id, group_name = "Group1"))
        val g2id = dao.insert(Group(groups_site_id = site1id, group_name = "Group2"))
        val g3id = dao.insert(Group(groups_site_id = site1id, group_name = "Group3"))

        val g4Id = dao.insert(Group(groups_site_id = site2Id, group_name = "Group4"))

        dao.insert(Message(groups_group_id = g1id, message_name = "M1"))
        dao.insert(Message(groups_group_id = g1id, message_name = "M2", read_status = true))
        dao.insert(Message(groups_group_id = g1id, message_name = "M3"))
        dao.insert(Message(groups_group_id = g1id, message_name = "M4"))

        for(si  in dao.getServerItems()) {
            Log.d(TAG,"Server is ${si.key.server_name} ID is ${si.key.server_id} it has ${si.value.size} sites.")
            for (s in si.value) {
                Log.d(TAG,"\tSite is ${s.site.site_name} there are ${s.groupList.size} Groups with ${s.getSiteUnreadCount()} unread messages.")
                for(g in s.groupList) {
                    Log.d(TAG,"\t\tGroup is ${g.group.group_name} there are ${g.messages.size} messages, ${g.getUnreadCounter()} unread")
                }
            }
        }
    }
}

    Running the above results in the log :-

    D/DBINFO: Server is Server1 ID is 1 it has 2 sites.
D/DBINFO:   Site is Site1 there are 3 Groups with 3 unread messages.
D/DBINFO:       Group is Group1 there are 4 messages, 3 unread
D/DBINFO:       Group is Group2 there are 0 messages, 0 unread
D/DBINFO:       Group is Group3 there are 0 messages, 0 unread
D/DBINFO:   Site is Site2 there are 1 Groups with 0 unread messages.
D/DBINFO:       Group is Group4 there are 0 messages, 0 unread
D/DBINFO: Server is Server2 ID is 2 it has 2 sites.
D/DBINFO:   Site is Site3 there are 0 Groups with 0 unread messages.
D/DBINFO:   Site is Site4 there are 0 Groups with 0 unread messages.

    i.e. of the 4 messages added (to Group1, which has Site1 as it's parent which has Server1 as it's parent) 3 are unread an 1 is read.

    Aditional

    Replicating you original (aka demonstrating the "complexity") here's some additional code.

    GroupItem and another version of of SiteItem i.e. SiteItemV2

    /* Additional */
data class SiteItemV2(
    @Embedded
    var site: Site,
    @Relation(entity = Group::class, parentColumn = "site_id", entityColumn = "groups_site_id")
    var groupList: List<GroupItem>,
    var unreadCounter: Int /* freebie */
)
data class GroupItem(
    @Embedded
    var group: Group,
    var unreadCounter: Int
)

    The associated @Dao functions :-

    /* get the group and group's unread message count */
@Query("SELECT *,(SELECT count(read_status) FROM messages WHERE messages.groups_group_id = groups.group_id AND NOT read_status) AS unreadCounter FROM groups WHERE groups_site_id=:siteid;")
fun getGroupWithReadCount(siteid: Long): List<GroupItem>


/* combine getServerItems and the getGroupWithReadCount to output Map<Server, List<SiteItemV2> */
@Transaction
@Query("")
fun getServerItemsV3(): Map<Server, List<SiteItemV2>> {
    var rv = mutableMapOf<Server,List<SiteItemV2>>()
    var currentGroupItemList = ArrayList<GroupItem>()
    var currentSiteItemList = ArrayList<SiteItemV2>()
    for(b in getServerItems()) {
        var currentUnreadCount = 0 /* Site level unread counter */
        for(si in b.value) {
            for (gi in getGroupWithReadCount(si.site.site_id!!)) {
                currentGroupItemList.add(GroupItem(gi.group,gi.unreadCounter))
                currentUnreadCount +=gi.unreadCounter
            }
            currentSiteItemList.add(SiteItemV2(si.site,currentGroupItemList.toList(),currentUnreadCount))
            currentUnreadCount = 0
            currentGroupItemList.clear()
        }
        rv[b.key] = currentSiteItemList.toList()
        currentSiteItemList.clear()
    }
    return rv
}

    and the activity code that uses the above:-

        /* Additional */
    for(si in dao.getServerItemsV3()) {
        Log.d(TAG+"EX03","Server is ${si.key.server_name} ID is ${si.key.server_id} is has ${si.value.size} sites.")
        for (s in si.value) {
            Log.d(TAG+"EX03","\tSite is ${s.site.site_name} ID is ${s.site.site_id}. The are ${s.unreadCounter} unread messages for the site accross ${s.groupList.size} groups.")
            for (g in s.groupList) {
                Log.d(
                    TAG + "EX03",
                    "\t\tGroup is ${g.group.group_name} unread messages = ${g.unreadCounter}"
                )
            }
        }
    }

    and the result :-

    D/DBINFOEX03: Server is Server1 ID is 1 is has 2 sites.
D/DBINFOEX03:   Site is Site1 ID is 1. The are 3 unread messages for the site accross 3 groups.
D/DBINFOEX03:       Group is Group1 unread messages = 3
D/DBINFOEX03:       Group is Group2 unread messages = 0
D/DBINFOEX03:       Group is Group3 unread messages = 0
D/DBINFOEX03:   Site is Site2 ID is 2. The are 0 unread messages for the site accross 1 groups.
D/DBINFOEX03:       Group is Group4 unread messages = 0
D/DBINFOEX03: Server is Server2 ID is 2 is has 2 sites.
D/DBINFOEX03:   Site is Site3 ID is 3. The are 0 unread messages for the site accross 0 groups.
D/DBINFOEX03:   Site is Site4 ID is 4. The are 0 unread messages for the site accross 0 groups.