How to observe livedata of nested relation pojo android room

Issue

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

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.

• e.g. something along the lines of SELECT *,(SELECT count(read_status) FROM messages WHERE groups_group_id = g.group_id AND read_status) AS unreadCounter FROM groups AS g WHERE group_id=:group_id.
• However, to incorporate this would be relatively complex.
• Note the column read_status has been made up and will probably not reflect what you have.

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
)

• Obviously these will probably not reflect you actual classes.

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
    }
}

• with a function to retrieve the unread messages.

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
    }
}

• with an additional function that will retrieve the unread message count for all groups in the Site.

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>>
    
}

• Note that for convenience and brevity LiveData has not been used

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.

Answered By - MikeT