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>
}
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
)
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 @Insert
s 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