androidandroid-roommany-to-one

How to query many-to-one using @Relation in Android Room


If a user can only be in one group, then user:group is a many-to-one relation.

@Entity(tableName="user")
data class UserEntity(
  @PrimaryKey(autoGenerate = true)
  val uid: Int,
  val username: String,
  val groupId: Int
  ...
)

@Entity(tableName="group")
data class GroupEntity(
  @PrimaryKey(autoGenerate = true)
  val gid: Int,
  val name: String,
)

I can query using POJO and left join like this:

data class UserWithGroup(
  @Embedded
  val user: UserEntity,
  @Embedded
  val group: GroupEntity,
)

@Dao
interface UserDao {

    @Transaction
    @Query("SELECT * FROM `user` u LEFT JOIN `group` g ON u.group_id = g.id")
    fun getAllUserWithGroup(): List<UserWithGroup>

}

Is it posible to use @Relation in UserWithGroup to get user with group? It seem most of examples from internet are get group with a list of user, how can I get user with associated group with @Relation like following codes?

data class UserWithGroup(
  @Embedded
  val user: UserEntity,
  @Relation(
     parentColumn = "gid",
     entityColumn = "groupId",
  )
  val group: GroupEntity
)

@Dao
interface UserDao {

    @Transaction
    @Query("SELECT * FROM `user`")
    fun getAllUserWithGroup(): List<UserWithGroup>

}

Solution

  • I believe that your issue is that you are coding as if the Group is the parent to the User instead of coding using the User as the parent to the Group. So:-

    data class UserWithGroup(
        @Embedded
        val user: UserEntity,
        @Relation(
            entity = GroupEntity::class,
            parentColumn = "groupId",
            entityColumn = "gid",
        )
        val group: GroupEntity
    )
    

    i.e. The @Embedded is always the Parent of the @Relation (the child) irrespective of the schema.


    Demo


    Using the above UserWithGroup class and your code (with @Inserts added to UserDao) and with a suitable (for testing) @Database annotated TheDatabase abstract class; then:-

        db = TheDatabase.getInstance(this)
        dao = db.getUserDao()
    
        val g1id = dao.insert(GroupEntity(0,"G1"))
        val g2id = dao.insert(GroupEntity(0,"G2"))
        dao.insert(UserEntity(0,"U2",g1id.toInt()))
        dao.insert(UserEntity(0,"U3",g1id.toInt()))
        dao.insert(UserEntity(0,"U4",g2id.toInt()))
        dao.insert(UserEntity(0,"U5",g2id.toInt()))
    
    
        for (uwg in dao.getAllUserWithGroup()) {
            Log.d("DBINFO","User is ${uwg.user.username} ID uid is ${uwg.user.uid} groupId is ${uwg.user.groupId} Group is ${uwg.group.name} gid is ${uwg.group.gid}")
        }
    

    Results in the Log including:-

    2024-04-20 06:56:31.246 D/DBINFO: User is U2 ID uid is 1 groupId is 1 Group is G1 gid is 1
    2024-04-20 06:56:31.246 D/DBINFO: User is U3 ID uid is 2 groupId is 1 Group is G1 gid is 1
    2024-04-20 06:56:31.246 D/DBINFO: User is U4 ID uid is 3 groupId is 2 Group is G2 gid is 2
    2024-04-20 06:56:31.246 D/DBINFO: User is U5 ID uid is 4 groupId is 2 Group is G2 gid is 2