Skip to content
Advertisement

How to query and filter relation entity in room database

For example:

User:

@Entity(tableName = "user")
data class UserEntity(
    @PrimaryKey
    @ColumnInfo(name = "id") val id: String,
    @ColumnInfo(name = "username") val username: String,
    @ColumnInfo(name = "name") val name: String,

Post:

@Entity(
    tableName = "post",
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["user_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["user_id"])]
)
data class PostEntity(
    @PrimaryKey
    @ColumnInfo(name = "id") var id: String, 
    @ColumnInfo(name = "user_id") var userId: String,
    @ColumnInfo(name = "body") val body: String,
    @ColumnInfo(name = "like") val like: Int,
    @ColumnType(name = "type") val type: String,
)

Data

data class Data(
    @Embedded
    val user: UserEntity,
    @Relation(parentColumn = "id", entityColumn = "user_id")
    val post: List<PostEntity> = emptyList(),
)

if i use SELECT * FROM user i got the desire data(a user and all posts), but how can i filter the post for a specific type, like WHERE post.type = 'sth' is that possible?

Advertisement

Answer

but how can i filter the post for a specific type,

It depends on exactly what you want to filter. You may want Data objects that match the filter but with all the posts (irrespactive of thetype), in which case you could use:-

@Transaction
@Query("SELECT * FROM user JOIN post ON user.id = user_id WHERE post.type = :type")
abstract fun getAllDataFiltered(type: String): List<Data>
  • where you would use something like var mylist = yourdao.getAllDataFiltered("sth")

However, as the id columns of post and user are both named id then the ambiguity interferes (the user id becomes the post id and thus no underlying post objects are extracted).

If you change PostEntity to be :-

@Entity(
    tableName = "post",
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["user_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["user_id"])]
)
data class PostEntity(
    @PrimaryKey
    @ColumnInfo(name = "postid") var id: String, //<<<<<<<<<< CHANGED
    @ColumnInfo(name = "user_id") var userId: String,
    @ColumnInfo(name = "body") val body: String,
    @ColumnInfo(name = "like") val like: Int,
    @ColumnInfo(name = "type") val type: String
)

Then the ambiguity is removed and the Data objects returned include ALL of the posts for the respective Users that have a post type of sth.

If you only want Data objects to be returned that only have the filtered posts, then you have to bypass Room’s technique of returning FULL/COMPLETE related objects.

If you make the @Dao class an abstract class rather than an interface then you with an @Query such as :-

@Query("SELECT * FROM post WHERE user_id=:userid AND type=:type")
abstract fun getPostsPerUserFiltered(userid: String, type: String): List<PostEntity>

along with a function such as :-

fun getFullyFiltered(type: String): List<Data> {
    var rv: ArrayList<Data> = arrayListOf()
    for(d: Data in getAllDataFiltered(type)) {
        rv.add(Data(d.user,post = getPostsPerUserFiltered(d.user.id,type)))
    }
    return rv
}

This filters the Data objects return but then discards the entire list of PostEntity (i.e. every post irrespective of the filter) and then applies the filtered posts.

If you wanted all Users but with only posts that match (and therefore potentially an empty list of posts) then you could have a function such as :-

fun getAllFilteredData(type: String): List<Data> {
    var rv: ArrayList<Data> = arrayListOf()
    for(u: UserEntity in getUsers()) {
        rv.add(Data(user = u, getPostsPerUserFiltered(u.id,type)))
    }
    return rv
}

i.e. no filtering is applied to the users but only to the posts.

Using the above then (noting the use use of the changed column name (postid instead of id)) then consider the following (a pretty standard @Database class has been used):-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    val TAG: String = "DBINFO"
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        dao.insert(UserEntity(id = "User1","User001","Mary"))
        dao.insert(UserEntity(id = "User2",username = "User002",name = "Sue"))
        dao.insert(UserEntity(id = "User3",username = "User003",name = "Tom"))

        dao.insert(PostEntity(id = "post1",userId = "User1",body = "post1 blah",type = "xxx",like = 0))
        dao.insert(PostEntity(id ="post2", userId = "User2",body ="post2 blah", type = "sth",like = 1))
        dao.insert(PostEntity(id = "post3", userId = "User1", body = "post3 blah", type = "sth", like = 3))

        /*
            No filtering applied
        */
        for(d: Data in dao.getAllData()) {
            logData(d,"ALL")
        }
        /*
            Return FULL Data objects (i.e. with ALL posts) but only those that
            have a post or posts that match the filter
        */
        for (d: Data in dao.getAllDataFiltered("sth")) {
            logData(d,"JOIN")
        }
        /*
            Return partial Data objects, but only for those that
            have a post that matches the type
         */
        for (d: Data in dao.getFullyFiltered("sth")) {
            logData(d,"FULL")
        }

        /*
            Return all partial Data Objects but with partial posts.

         */
        for (d: Data in dao.getAllFilteredData("sth")) {
            logData(d,"POST")
        }
    }

    private fun logData(data: Data,tagSuffix: String) {
        Log.d(TAG + tagSuffix,"Data for user ${data.user.id}, Name is ${data.user.name} etc")
        for (p: PostEntity in data.post) {
            Log.d(TAG + tagSuffix,"t Post is ${p.id} Type is ${p.type} body is:-ntt${p.body}")
        }
    }
}

The the results output to the log are:-

No filtering at all:-

2021-09-17 11:22:00.722 D/DBINFOALL: Data for user User1, Name is Mary etc
2021-09-17 11:22:00.722 D/DBINFOALL:     Post is post1 Type is xxx body is:-
            post1 blah
2021-09-17 11:22:00.723 D/DBINFOALL:     Post is post3 Type is sth body is:-
            post3 blah
2021-09-17 11:22:00.723 D/DBINFOALL: Data for user User2, Name is Sue etc
2021-09-17 11:22:00.723 D/DBINFOALL:     Post is post2 Type is sth body is:-
            post2 blah
2021-09-17 11:22:00.723 D/DBINFOALL: Data for user User3, Name is Tom etc

Only the User’s are filtered as Room gets all the posts for the users :-

2021-09-17 11:22:00.728 D/DBINFOJOIN: Data for user User2, Name is Sue etc
2021-09-17 11:22:00.728 D/DBINFOJOIN:    Post is post2 Type is sth body is:-
            post2 blah
2021-09-17 11:22:00.728 D/DBINFOJOIN: Data for user User1, Name is Mary etc
2021-09-17 11:22:00.728 D/DBINFOJOIN:    Post is post1 Type is xxx body is:-
            post1 blah
2021-09-17 11:22:00.728 D/DBINFOJOIN:    Post is post3 Type is sth body is:-
            post3 blah

Fully filtered :-

2021-09-17 11:22:00.738 D/DBINFOFULL: Data for user User2, Name is Sue etc
2021-09-17 11:22:00.738 D/DBINFOFULL:    Post is post2 Type is sth body is:-
            post2 blah
2021-09-17 11:22:00.738 D/DBINFOFULL: Data for user User1, Name is Mary etc
2021-09-17 11:22:00.738 D/DBINFOFULL:    Post is post3 Type is sth body is:-
            post3 blah

All users but with filtered posts

2021-09-17 11:22:00.744 D/DBINFOPOST: Data for user User1, Name is Mary etc
2021-09-17 11:22:00.744 D/DBINFOPOST:    Post is post3 Type is sth body is:-
            post3 blah
2021-09-17 11:22:00.744 D/DBINFOPOST: Data for user User2, Name is Sue etc
2021-09-17 11:22:00.744 D/DBINFOPOST:    Post is post2 Type is sth body is:-
            post2 blah
2021-09-17 11:22:00.744 D/DBINFOPOST: Data for user User3, Name is Tom etc

If the PostEntity column is changed back to being id then the results are

:-

2021-09-17 11:27:00.661 D/DBINFOALL: Data for user User1, Name is Mary etc
2021-09-17 11:27:00.661 D/DBINFOALL:     Post is post1 Type is xxx body is:-
            post1 blah
2021-09-17 11:27:00.661 D/DBINFOALL:     Post is post3 Type is sth body is:-
            post3 blah
2021-09-17 11:27:00.661 D/DBINFOALL: Data for user User2, Name is Sue etc
2021-09-17 11:27:00.662 D/DBINFOALL:     Post is post2 Type is sth body is:-
            post2 blah
2021-09-17 11:27:00.662 D/DBINFOALL: Data for user User3, Name is Tom etc
2021-09-17 11:27:00.664 D/DBINFOJOIN: Data for user post2, Name is Sue etc
2021-09-17 11:27:00.664 D/DBINFOJOIN: Data for user post3, Name is Mary etc
2021-09-17 11:27:00.672 D/DBINFOFULL: Data for user post2, Name is Sue etc
2021-09-17 11:27:00.672 D/DBINFOFULL: Data for user post3, Name is Mary etc
2021-09-17 11:27:00.676 D/DBINFOPOST: Data for user User1, Name is Mary etc
2021-09-17 11:27:00.676 D/DBINFOPOST:    Post is post3 Type is sth body is:-
            post3 blah
2021-09-17 11:27:00.676 D/DBINFOPOST: Data for user User2, Name is Sue etc
2021-09-17 11:27:00.676 D/DBINFOPOST:    Post is post2 Type is sth body is:-
            post2 blah
2021-09-17 11:27:00.677 D/DBINFOPOST: Data for user User3, Name is Tom etc
  • i.e. Note how the user is the post’s id and hence no underlying posts.
  • you could use @Embedded(prefix = "a_suitable_prefix"). However, you then have to alter the column names of the User (the prefixed table) in the query using AS, far simpler to use a non-ambiguous column name.
  • the 4th, returning all users but with only the filtered posts is not affected as it doesn’t use the Data POJO which is where the ambiguity results in the user id being the post id.
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement