Android ROOM database multi-table query has data disorder or duplication problem fix-kotlin

Android ROOM database multi-table query has data disorder or duplication problem fix-kotlin

Preface

Regarding multi-table query, I wrote an article before, Android Room database uses @Relation annotation for multi-table query-Kotlin , which is based on the official description, and has never had the opportunity to use it in the project, but recently I discovered its problem. When a mark is added to the relationship table, the data queried will be incorrect;

Description

Assuming a music app, first there is an account system, that is, there is a table of UserInfo, and then there is a table of Playlist GroupInfo, a table of SongInfo, and a table of relationship between playlists and songs.

/**
 * 用户信息
 * @Author: D10NG
 * @Time: 2021/6/3 3:28 下午
 */
@Entity
data class UserInfo(
    @PrimaryKey
    var userId: Long = 0,

    var name: String = ""
): Serializable

/**
 * 歌单
 * @Author: D10NG
 * @Time: 2021/6/3 3:32 下午
 */
@Entity(
    primaryKeys = ["groupId", "userId"]
)
data class GroupInfo(
    var groupId: Long = 0,

    var userId: Long = 0,

    var name: String = "",
): Serializable
/**
 * 歌曲
 * @Author: D10NG
 * @Time: 2021/6/3 3:35 下午
 */
@Entity(
    primaryKeys = ["songId", "userId"]
)
data class SongInfo(
    var songId: Long = 0,

    var userId: Long = 0,

    var name: String = ""
): Serializable

/**
 * 歌单与歌的关系
 * @Author: D10NG
 * @Time: 2021/6/3 3:44 下午
 */
@Entity(
    primaryKeys = ["userId", "groupId", "songId"],
    foreignKeys = [ForeignKey(entity = GroupInfo::class, parentColumns = ["groupId", "userId"], childColumns = ["groupId", "userId"], onDelete = ForeignKey.CASCADE)]
)
data class GroupWithSongRelation(
    var userId: Long = 0,
    var groupId: Long = 0,
    var songId: Long = 0
): Serializable

Then the database inserts relational data

// 用户0,歌单0,有歌曲0
db.getGroupWithSongDao().insert(GroupWithSongRelation(0, 0, 0))
// 用户0,歌单1,有歌曲0,1
db.getGroupWithSongDao().insert(GroupWithSongRelation(0, 1, 0))
db.getGroupWithSongDao().insert(GroupWithSongRelation(0, 1, 1))
// 用户0,歌单2,有歌曲0,1,2
db.getGroupWithSongDao().insert(GroupWithSongRelation(0, 2, 0))
db.getGroupWithSongDao().insert(GroupWithSongRelation(0, 2, 1))
db.getGroupWithSongDao().insert(GroupWithSongRelation(0, 2, 2))

// 用户1,歌单0,有歌曲1
db.getGroupWithSongDao().insert(GroupWithSongRelation(1, 0, 1))
// 用户1,歌单1,有歌曲1,2
db.getGroupWithSongDao().insert(GroupWithSongRelation(1, 1, 1))
db.getGroupWithSongDao().insert(GroupWithSongRelation(1, 1, 2))
// 用户1,歌单2,有歌曲1,2,3
db.getGroupWithSongDao().insert(GroupWithSongRelation(1, 2, 1))
db.getGroupWithSongDao().insert(GroupWithSongRelation(1, 2, 2))
db.getGroupWithSongDao().insert(GroupWithSongRelation(1, 2, 3))

Write query

    // 查询歌单带歌曲信息
    @Transaction
    @Query("SELECT * FROM groupinfo WHERE userId = (:userId) AND groupId = (:groupId)")
    suspend fun susQueryGroup(userId: Long, groupId: Long): GroupWithSongData?

Query User songs 0 1 single result is:

Insert picture description here


Obviously you can see not only the repeated data, also belong to a single user 1 song to sneak into the

solve

Directly use JOIN statement to query

    @Query("SELECT * FROM songinfo JOIN (SELECT * FROM groupwithsongrelation WHERE userId = :userId AND groupId = :groupId) groupwithsongrelation ON groupwithsongrelation.songId = SongInfo.songId WHERE SongInfo.userId=:userId")
    suspend fun susQueryGroupSong(userId: Long, groupId: Long): List<SongInfo>
First do a subquery in the JOIN statement to find out the key data first.
Then, you have to add a WHERE to the query result to eliminate duplicate data.

search result:

Insert picture description here

Github

Project DEMO:
https://github.com/D10NGYANG/RoomDemo

Finished