Get data with count from different tables-CodePudding

I'm using room database and there are two entities named File and Folder

data class File{
   ....
   val folderId: Long? = null,
   ....
}

data class Folder{
   val id:Long,
   val name:String
}

So, I want to get a flow of list of object Flow<List<FolderDetailWithCount>>

data class FolderDetailWithCount{
    val id:Long?,
    val name:String,
    val count:Long
}

which means in which folder(with its name) how many files are stored. (folderId of a file is the id of folder in which it is located)

So, I don't know how to do this in room and what should be the query for getting this information.

CodePudding user response：

You can achieve what you need with this custom query:

@Query("SELECT fo.id, fo.name, (SELECT COUNT(*) FROM FileTable fi WHERE fi.folderId == fo.id) as count FROM FolderTable fo")
suspend fun getFolderDetailWithCountListFlow(): Flow<List< FolderDetailWithCount>>

Change FileTable with your file table name, and FolderTable with your folder table name