Home > database >  Android Room query with condition for nested object
Android Room query with condition for nested object

Time：07-29

So I have a simple example like this:

data class PostData(
  val postId: Long,
  val content: String,
  val group: GroupData //A group data where this post belongs to
)

data class GroupData(
  val groupId: Long,
  val name: String,
  val admins: List<Admin>
)

data class Admin(
    val userId: Long,
    val name: String,
    val avatar: String
)

and the Room structure should be like this:

@Entity(tableName = "PostDataLocal")
data class PostDataLocal(
    @PrimaryKey val postId: Long,
    val content: String,
)

@Entity(tableName = "GroupDataLocal")
data class GroupDataLocal(
    val postId: Long,
    @PrimaryKey val groupId: Long,
    val name: String,
)

@Entity(tableName = "AdminDataLocal")
data class AdminDataLocal(
    val groupId: Long,
    @PrimaryKey val userId: Long,
    val name: String,
    val avatar: String,
)

And for the relationship between them:

data class GroupAndItsAdminRelations(
    @Embedded val group: GroupDataLocal,
    @Relation(
        parentColumn = "groupId",
        entityColumn = "groupId"
    )
    val admins: List<AdminDataLocal>
)

data class NeededPostsOfAGroup(
    @Embedded val postData: PostDataLocal,

    @Relation(
        parentColumn = "postId",
        entityColumn = "postId"
    )
    val group: GroupAndItsAdminRelations
)

But to be honest, I don't know if I wrote NeededPostsOfAGroup correctly. Please correct me on these mistake.

My question is, instead of getting all the PostDataLocal in DAO file like this on some tutorials:

@Transaction
@Query("SELECT * FROM PostDataLocal")
fun getAllNeededPostsOfAGroup(): List<NeededPostsOfAGroup>

I would love to get only Post of a specific group (which is the nested GroupAndItsAdminRelations.group.groupId), like "WHERE groupId = :groupId". How do I make that query?

Thank you for your time.

CodePudding user response：

When you use @Relation and rely upon it then as you have observed it gets all children.

In short for each parent it runs an underlying query (hence the warning about using @Transaction). The underlying query is along the lines of SELECT * FROM the_child_table WHERE entity_column = the_current_parent_column i.e, ALL children of the current parent.

As such you need to override this conveneience approach.

You do this by having a function with a body, where the groupId can be specified.

e.g. something along the lines of (note untested, so it is in theory/in principle code and may contain errors)

@Query("SELECT * FROM PostDataLocal")
fun getAllPosts(): List<PostDataLocal>

@Query("SELECT * FROM GroupLocalData WHERE Group=:groupId AND postId=:postId")
fun getSelectedGroupsForAPost(groupId: Long, postId: Long): List<GroupAndItsAdminRelations>

@Transaction
@Query("")
fun getAllPostsWithGroupsAndAdminsForAGroup(groupId: Long): List<NeededPostsOfAGroup> {
    var rv: ArrayList<NeededPostsOfAGroup> = arrayListOf()
    for(post in getAllPosts()) {
        rv.add(NeededPostsOfAGroup(post,getSlectedGroupsForAPost(groupID,post.postId)
    }
    return rv
}

NOTE time pemitting I may edit this post with a working example (time doesn't permit at present)

Working Example

using a copy of your @Entity annotated classes and POJO classes

However, to cater for the Freebie List of groups a subtle change has been made to

as per :-

data class NeededPostsOfAGroup(
    @Embedded val postData: PostDataLocal,
    @Relation(
        entity = GroupAndItsAdminRelations::class,
        parentColumn = "postId",
        entityColumn = "postId"
    )
    val group: List<GroupAndItsAdminRelations> /* Made a List so can cope with multiple groups */
)

then with the following @Dao annotated interface:-

@Dao
interface AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(postDataLocal: PostDataLocal): Long

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(groupDataLocal: GroupDataLocal): Long

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(adminDataLocal: AdminDataLocal): Long
    
    /* Note only returns List<PostDataLocal> no need for children and grandchildren */
    @Query("SELECT * FROM postDataLocal")
    fun getAllPosts(): List<PostDataLocal>

    /* For getting just One Group as per question as I understand it*/
    @Query("SELECT * FROM groupDataLocal WHERE groupid=:groupId AND postid=:postId")
    fun getSelectedGroupForAPost(groupId: Long, postId: Long): List<GroupAndItsAdminRelations>
    
    /* Freebie can get for a List of Groups - subtle but perhaps very useful variation */
    @Query("SELECT * FROM groupDataLocal WHERE groupid IN(:groupIdList) AND postId=:postId")
    fun getSelectedGroupsForAPost(groupIdList: List<Long>, postId: Long): List<GroupAndItsAdminRelations>

    
    /* For just getting the 1 group */
    @Transaction
    @Query("")
    fun getAllPostsWithSelectedGroupAndAdminsForAGroup(groupId: Long): List<NeededPostsOfAGroup> {
        val rv: ArrayList<NeededPostsOfAGroup> = arrayListOf()
        for (post in getAllPosts()) {
            rv.add(NeededPostsOfAGroup(post,getSelectedGroupForAPost(groupId,post.postId!!)))
        }
        return rv.toList()
    }

    /* Freebie fr getting a list of groups */
    @Transaction
    @Query("")
    fun getAllPostsWithSelectedGroupsAndAdminForAListOfGroups(groupIdList: List<Long>): List<NeededPostsOfAGroup> {
        val rv: ArrayList<NeededPostsOfAGroup> = arrayListOf()
        for(post in getAllPosts()) {
            rv.add(NeededPostsOfAGroup(post,getSelectedGroupsForAPost(groupIdList,post.postId!!)))
        }
        return rv.toList()
    }
}

and then with the following code in an activity to demonstrate :-

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 p1Id = dao.insert(PostDataLocal(content = "Post001"))
        val p2Id = dao.insert(PostDataLocal(content = "Post002"))
        val p3Id = dao.insert(PostDataLocal(content = "Post003"))

        val g1Id = dao.insert(GroupDataLocal(postId = p1Id, name = "Group001 (Post001)"))
        val g2Id = dao.insert(GroupDataLocal(postId = p1Id, name = "Group002 (Post001)"))
        val g3Id = dao.insert(GroupDataLocal(postId = p1Id, name = "Group003 (Post001)"))

        val g4Id = dao.insert(GroupDataLocal(postId = p2Id, name = "Group004 (Post002)"))
        val g5Id = dao.insert(GroupDataLocal(postId = p2Id, name = "Group005 (Post002)"))

        val g6Id = dao.insert(GroupDataLocal(postId = p3Id, name = "Group006 (Post003)"))

        dao.insert(AdminDataLocal(groupId = g1Id, name = "Admin001", avatar = "admin001.gif"))
        dao.insert(AdminDataLocal(groupId = g1Id, name = "Admin002", avatar = "admin002.gif"))
        dao.insert(AdminDataLocal(groupId = g1Id, name = "Admin003", avatar = "admin003.gif"))

        dao.insert(AdminDataLocal(groupId = g2Id, name = "Admin004", avatar = "admin004.gif"))
        dao.insert(AdminDataLocal(groupId = g2Id, name = "Admin005", avatar = "admin005.gif"))

        dao.insert(AdminDataLocal(groupId = g3Id, name = "Admin006", avatar = "admin006.gif"))

        dao.insert(AdminDataLocal(groupId = g4Id, name = "Admin007", avatar = "admin007.gif"))
        dao.insert(AdminDataLocal(groupId = g5Id, name = "Admin008", avatar = "admin008.gif"))
        dao.insert(AdminDataLocal(groupId = g6Id, name = "Admin009", avatar = "admin009.gif"))
        dao.insert(AdminDataLocal(groupId = g6Id, name = "Admin010", avatar = "admin010.gif"))



        logNeededPosts(dao.getAllPostsWithSelectedGroupAndAdminsForAGroup(g1Id),"EX01")
        logNeededPosts(dao.getAllPostsWithSelectedGroupsAndAdminForAListOfGroups(listOf(g1Id,g2Id,g4Id,g5Id)),"EX02")

    }

    fun logNeededPosts(neededPostsList: List<NeededPostsOfAGroup>, suffix: String) {

        val sb = StringBuilder()
        for (npg in neededPostsList) {
            sb.clear()
            sb.append("Post ID is ${npg.postData.postId} Content is ${npg.postData.content}, it has ${npg.group.size} groups. They are:-")
            for(g in npg.group) {
                sb.append("\n\t")
                sb.append("Group ID is ${g.group.groupId} Name is ${g.group.name}, it belongs to Post ${g.group.postId} and has ${g.admins.size} admins. They are:-")
                for (a in g.admins) {
                    sb.append("\n\t\t")
                    sb.append("Admin UserID is ${a.userId} Name is ${a.name} Avatar is ${a.avatar}, it belongs to Group ${a.groupId}")
                }
            }
            Log.d("DBINFO_$suffix",sb.toString())
        }
    }
}

The resultant output written to the log is :-

2022-07-29 10:36:23.053 D/DBINFO_EX01: Post ID is 1 Content is Post001, it has 1 groups. They are:-
        Group ID is 1 Name is Group001 (Post001), it belongs to Post 1 and has 3 admins. They are:-
            Admin UserID is 1 Name is Admin001 Avatar is admin001.gif, it belongs to Group 1
            Admin UserID is 2 Name is Admin002 Avatar is admin002.gif, it belongs to Group 1
            Admin UserID is 3 Name is Admin003 Avatar is admin003.gif, it belongs to Group 1
2022-07-29 10:36:23.053 D/DBINFO_EX01: Post ID is 2 Content is Post002, it has 0 groups. They are:-
2022-07-29 10:36:23.053 D/DBINFO_EX01: Post ID is 3 Content is Post003, it has 0 groups. They are:-

And for the Freebie handling of a List of Groups :-

2022-07-29 10:36:23.083 D/DBINFO_EX02: Post ID is 1 Content is Post001, it has 2 groups. They are:-
        Group ID is 1 Name is Group001 (Post001), it belongs to Post 1 and has 3 admins. They are:-
            Admin UserID is 1 Name is Admin001 Avatar is admin001.gif, it belongs to Group 1
            Admin UserID is 2 Name is Admin002 Avatar is admin002.gif, it belongs to Group 1
            Admin UserID is 3 Name is Admin003 Avatar is admin003.gif, it belongs to Group 1
        Group ID is 2 Name is Group002 (Post001), it belongs to Post 1 and has 2 admins. They are:-
            Admin UserID is 4 Name is Admin004 Avatar is admin004.gif, it belongs to Group 2
            Admin UserID is 5 Name is Admin005 Avatar is admin005.gif, it belongs to Group 2
2022-07-29 10:36:23.084 D/DBINFO_EX02: Post ID is 2 Content is Post002, it has 2 groups. They are:-
        Group ID is 4 Name is Group004 (Post002), it belongs to Post 2 and has 1 admins. They are:-
            Admin UserID is 7 Name is Admin007 Avatar is admin007.gif, it belongs to Group 4
        Group ID is 5 Name is Group005 (Post002), it belongs to Post 2 and has 1 admins. They are:-
            Admin UserID is 8 Name is Admin008 Avatar is admin008.gif, it belongs to Group 5
2022-07-29 10:36:23.084 D/DBINFO_EX02: Post ID is 3 Content is Post003, it has 0 groups. They are:-

An additional improvement

If you were not interested in the Post's that only include the selected group(s) then you would modify the initial query invoked to SELECT only those Posts e.g.

@Query("SELECT DISTINCT postDataLocal.* FROM postDataLocal JOIN groupDataLocal ON groupDataLocal.postId = postDataLocal.postId WHERE groupDataLocal.groupId IN (:groupIdList)")
fun getPostsWithSpecifiedGroups(groupIdList: List<Long>): List<PostDataLocal>
  • postDataLocal.* selecting only the required output columns to build the PostDataLocal objects,
  • DISTINCT to only retrieve 1 PostDataLocal even if it has mutltiple groups (the 2nd query expands upon the groups), if not distinct you would retrieve multiple identical groups id a PostDataLocal contained more than 1 group.

Along with a modified function with a body :-

@Transaction
@Query("")
fun getOnlyPostsWithSelectedGroupsWithAdminForAListOfGroups(groupIdList: List<Long>): List<NeededPostsOfAGroup> {
    val rv: ArrayList<NeededPostsOfAGroup> = arrayListOf()
    for(post in getPostsWithSpecifiedGroups(groupIdList)) {
        rv.add(NeededPostsOfAGroup(post,getSelectedGroupsForAPost(groupIdList,post.postId!!)))
    }
    return rv.toList()
}

Then using

logNeededPosts(dao.getOnlyPostsWithSelectedGroupsWithAdminForAListOfGroups(listOf(g1Id)),"EX03")

results in just the 1 Post rather than all 3 Posts (2 without group) as per (in comparison to output EX01) :-

2022-07-29 11:14:12.286 3426-D/DBINFO_EX03: Post ID is 1 Content is Post001, it has 1 groups. They are:-
        Group ID is 1 Name is Group001 (Post001), it belongs to Post 1 and has 3 admins. They are:-
            Admin UserID is 1 Name is Admin001 Avatar is admin001.gif, it belongs to Group 1
            Admin UserID is 2 Name is Admin002 Avatar is admin002.gif, it belongs to Group 1
            Admin UserID is 3 Name is Admin003 Avatar is admin003.gif, it belongs to Group 1

Additional Re Comment

After a quick glance, I'm wondering if I can use one single query to get them all?

Yes a single query is possible but the result would be the cartesian product. That is every row output contains all the data (Post Group and Admin) so you then have to build objects from that data.

e.g. consider the following single query

SELECT * FROM postDataLocal JOIN groupDataLocal ON postDataLocal.postId = groupDataLocal.groupPostidMap JOIN adminDataLocal ON groupDataLocal.groupId = adminDataLocal.admingroupIdMap WHERE groupDataLocal.groupId IN(:groupIdList) ORDER BY postId, groupId
  • Note some column names have been changed (more on that later)

Using App Inspection with the previous data (albeit it with changed column names) then the result is :-

enter image description here

  • the 1,2,3 highlighted is the list of groupId's ( equivalent of g1Id, g2Id and g3Id)
  • The highlighted column names are those that have been changed. Changed because Room will fail the compile if it spots columns with the same name
    • there are other options to get around the issue (use AS to change the name or use the @Embedded's prefix parameter with AS to change the output names). These are probably more complex to handle than changing the names (in the @Entity annotated classes).
      • I try to always use unique column names and suggest doing that

Now to use the Query then you need a POJO that will accept the output. For example :-

data class PostGroupAdminRow(
    @Embedded
    var postData: PostDataLocal,
    @Embedded
    var group: GroupDataLocal,
    @Embedded
    var adminDataLocal: AdminDataLocal
)
  • Note columns names have been changed in GroupDataLocal and AdminDataLocal

The you could have a new @Dao function :-

@Query("SELECT * FROM postDataLocal JOIN groupDataLocal ON postDataLocal.postId = groupDataLocal.groupPostidMap JOIN adminDataLocal ON groupDataLocal.groupId = adminDataLocal.admingroupIdMap WHERE groupDataLocal.groupId IN(:groupIdList) ORDER BY postId, groupId")
fun getCartesianPostGroupAdmin(groupIdList: List<Long>): List<PostGroupAdminRow>

Using activity code :-

    for(pga in dao.getCartesianPostGroupAdmin(listOf(g1Id,g2Id,g3Id))) {
        Log.d("DBINFO_EX04","Post is ${pga.postData.content} Group is ${pga.group.groupName} Admin is ${pga.adminDataLocal.adminName}")
    }

results in :-

2022-07-29 14:40:22.303 D/DBINFO_EX04: Post is Post001 Group is Group001 (Post001) Admin is Admin001
2022-07-29 14:40:22.303 D/DBINFO_EX04: Post is Post001 Group is Group001 (Post001) Admin is Admin002
2022-07-29 14:40:22.303 D/DBINFO_EX04: Post is Post001 Group is Group001 (Post001) Admin is Admin003
2022-07-29 14:40:22.303 D/DBINFO_EX04: Post is Post001 Group is Group002 (Post001) Admin is Admin004
2022-07-29 14:40:22.303 D/DBINFO_EX04: Post is Post001 Group is Group002 (Post001) Admin is Admin005
2022-07-29 14:40:22.303 D/DBINFO_EX04: Post is Post001 Group is Group003 (Post001) Admin is Admin006

i.e. all the required data has been extracted but still cartesian product, not the NeededPosts objects. That's where you would then have to process the data and build the NeededPosts objects if you want them (hence why the ORDER BY clause in the SQL)

@Entity and POJO classes used for the above (single query) with comments to explain changes :-

@Entity/*(tableName = "PostDataLocal") not needed as table name is same as derived from class name */
data class PostDataLocal(
    @PrimaryKey val postId: Long?=null, /* changed to allow generation of id by default */
    val content: String
)

@Entity/*(tableName = "GroupDataLocal") not needed as table name is same as derived from class name */
data class GroupDataLocal(
    val groupPostIdMap: Long, /* NAME CHANGED */
    @PrimaryKey val groupId: Long?=null, /* changed to allow generation of id by default */
    val groupName: String /* NAME CHANGED */
)

@Entity/*(tableName = "AdminDataLocal") not needed as table name is same as derived from class name */
data class AdminDataLocal(
    val adminGroupIdMap: Long, /* NAME CHANGED */
    @PrimaryKey val userId: Long?=null, /* changed to allow generation of id by default */
    val adminName: String, /* NAME CHANGED */
    val avatar: String
)
data class GroupAndItsAdminRelations(
    @Embedded val group: GroupDataLocal,
    @Relation(
        entity = AdminDataLocal::class,
        parentColumn = "groupId",
        entityColumn = "adminGroupIdMap" /* CHANGED DUE TO NAME CHANGE */
    )
    val admins: List<AdminDataLocal>
)

data class NeededPostsOfAGroup(
    @Embedded val postData: PostDataLocal,
    @Relation(
        entity = GroupAndItsAdminRelations::class,
        parentColumn = "postId",
        entityColumn = "groupPostIdMap" /* CHANGED DUE TO NAME CHANGE */
    )
    val group: List<GroupAndItsAdminRelations> /* Made a List so can cope with multiple groups */
)

The final Step - NeededPostsOfAGroup from the Single Query

  • Note not fully tested but appears to be OK

A new function is introduced (added to the activity code but should probably be elsewhere), it being :-

private fun buildNeededPostOfAGroupFromPostGroupAdminRows(pgaList: List<PostGroupAdminRow>): List<NeededPostsOfAGroup> {
    val rv: ArrayList<NeededPostsOfAGroup> = arrayListOf()
    val invalidId = -666L
    var currentPostDataLocal = PostDataLocal(invalidId,"")
    var currentGroupDataLocal = GroupDataLocal(invalidId,invalidId,"")
    var currentGroupAndItsAdminRelations: ArrayList<GroupAndItsAdminRelations> = arrayListOf()
    var currentAdminDataLocalList: ArrayList<AdminDataLocal> = arrayListOf()

    for(pga in pgaList) {
        // is it a new Post?
        if (currentPostDataLocal.postId != pga.postData.postId) {
            if (currentPostDataLocal.postId != invalidId) {
                //* Add previous a data as a new NeededPostOfAgroup
                rv.add(
                    NeededPostsOfAGroup(
                        currentPostDataLocal,
                        currentGroupAndItsAdminRelations
                    )
                )
            }
            /* New currentPostDataLocal, currentGroupDataLocal and currentGroupAndItsAdminRelations*/
            currentPostDataLocal = PostDataLocal(pga.postData.postId,pga.postData.content)
            currentGroupDataLocal = GroupDataLocal(pga.group.groupPostIdMap,pga.group.groupId,pga.group.groupName)
            currentGroupAndItsAdminRelations = arrayListOf()
            currentAdminDataLocalList = arrayListOf()
        }
        if (currentGroupDataLocal.groupId != pga.group.groupId) {
            if (currentGroupDataLocal.groupId != invalidId) {
                currentGroupAndItsAdminRelations.add(
                        GroupAndItsAdminRelations(
                            currentGroupDataLocal,
                            currentAdminDataLocalList
                        )
                )
                currentAdminDataLocalList = arrayListOf()
            }
            currentGroupDataLocal = GroupDataLocal(pga.group.groupPostIdMap,pga.group.groupId,pga.group.groupName)
        }
        currentAdminDataLocalList.add(AdminDataLocal(pga.adminDataLocal.adminGroupIdMap,pga.adminDataLocal.userId,pga.adminDataLocal.adminName,pga.adminDataLocal.avatar))
    }
    if (currentGroupAndItsAdminRelations.size > 0) {
        currentGroupAndItsAdminRelations.add(GroupAndItsAdminRelations(currentGroupDataLocal,currentAdminDataLocalList))
        rv.add(
            NeededPostsOfAGroup(
                currentPostDataLocal,
                currentGroupAndItsAdminRelations
            )
        )
    }
    return rv.toList()
}

With the following code in the activity :-

logNeededPosts(buildNeededPostOfAGroupFromPostGroupAdminRows(dao.getCartesianPostGroupAdmin(listOf(g1Id,g2Id,g3Id))),"EX05")
  • using are example list of 1,2 and 3

then the result output to the log is :-

2022-07-29 16:14:44.319 D/DBINFO_EX05: Post ID is 1 Content is Post001, it has 3 groups. They are:-
        Group ID is 1 Name is Group001 (Post001), it belongs to Post 1 and has 3 admins. They are:-
            Admin UserID is 1 Name is Admin001 Avatar is admin001.gif, it belongs to Group 1
            Admin UserID is 2 Name is Admin002 Avatar is admin002.gif, it belongs to Group 1
            Admin UserID is 3 Name is Admin003 Avatar is admin003.gif, it belongs to Group 1
        Group ID is 2 Name is Group002 (Post001), it belongs to Post 1 and has 2 admins. They are:-
            Admin UserID is 4 Name is Admin004 Avatar is admin004.gif, it belongs to Group 2
            Admin UserID is 5 Name is Admin005 Avatar is admin005.gif, it belongs to Group 2
        Group ID is 3 Name is Group003 (Post001), it belongs to Post 1 and has 1 admins. They are:-
            Admin UserID is 6 Name is Admin006 Avatar is admin006.gif, it belongs to Group 3

Page link：https//www.codepudding.com/database/492778.html

Prev:Kotlin structured concurrency : blocking a call when a java library runs in a separate thread
Next:How to connect to Elasticsearch hosted on AWS EC2 from another machines
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding