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

Question

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>

}

Answer 1

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
)

• note that entity=GroupEntity::class is, in this case, optional but suggested as this is needed if the @Relation's output type is not the table (as it can/would be if the hierarchy was greater than just the 2 levels).

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