android many-to-many android-room android-room-relation

Many To Many Relationship (IDs in complex data) Android Room

I want to achieve a many to many relationship with this tow data classes Member and Team since the team can have several members and the member can be in several teams

The Member Class have a reference to the teams IDs as a key in Map

data class Member(
    var id: String = "",
    var name: String = "",
    /** teamsPositionsMap   key -> teamId , value -> position */
    private var tPM: Map<String, String> = mapOf(),
)

And the Team Class reference to the members by a list of IDs AND want to have a list of Member Objects as result of the query

data class Team(
    var id: String = "",
    var name: String = "",
    var memIds: List<String> = listOf(),
    /** get it by query memId */
    var memberList: List<Member>? = null,
)

My Question is: How i can acive this relationship with Android Room With single query (if possible)

I thought about duplicating each Team to make each row have only one memId and so on for Member the iteration and flattening but i don't think that the best solution

Solution

The typical way of managing many-many relationships is to have an intermediate table to map the the relationships. Such a table will have two columns, each to identify the respective row of the relationship.

e.g. say you have members with id's 1,2,3 .... etc and teams 1000,1001,1002 and so on (1000+ used purely make it easy to differentiate for this explanation).

Then the mapping table my have rows like :-

So the Team identified by 1000 has members identified by 1,3 & 5, The Team identified by 1001 has members identified by 2 & 3 and 1002 has 1 through 5.

To implement this in Room you have the core Entities Member and Team with no consideration that they will be related so :-

@Entity
data class Member(
    @PrimaryKey
    var id: String = "",
    var name: String = "",
    /** teamsPositionsMap   key -> teamId , value -> position */
    //private var tPM: Map<String, String> = mapOf(),
)

and

@Entity
data class Team(
    @PrimaryKey
    var id: String = "",
    var name: String = ""
    //var memIds: List<String> = listOf(),
    /** get it by query memId */
    //var memberList: List<Member>? = null,
)

Note the commented out lines

You then have the intermediate mapping table (aka associative table, link table ....) :-

@Entity(
    primaryKeys = ["memberIdMap","teamIdMap"],
    indices = [Index(value = ["teamIdMap"], unique = false)],
    foreignKeys = [
        ForeignKey(
            entity = Member::class,
            parentColumns = ["id"],
            childColumns = ["memberIdMap"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Team::class, 
            parentColumns = ["id"], 
            childColumns = ["teamIdMap"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class MemberTeamMap(
    var memberIdMap: String,
    var teamIdMap: String
)

With Room a PRIMARY KEY is required, a composite primary key has been defined as a requisite of a PRIMARY KEY is that it hold UNIQUE values, so just either column as the primary key would disallow the many.
The index on the other column is not required BUT room issues a warning if it is omitted.
Foreign Keys aren't required BUT they do enforce referential integrity i.e. that the map does not contain orphans.

To actually get Members with their teams or teams with the members then you need a POJO (not a table) that has the parent (Member or Team) and the list/array (teams for a Member, and members for a Team).

To facilitate this you use Room annotations @Embedded for the parent and @Relation for the children.

So you could have :-

data class TeamWithMembers(
    @Embedded
    var team: Team,
    @Relation(
        entity = Member::class, parentColumn = "id", entityColumn = "id",
        associateBy = Junction(
            value = MemberTeamMap::class, parentColumn = "teamIdMap", entityColumn = "memberIdMap"
        )
    )
    var members: List<Member>
)

and/or :-

data class MemberWithTeams (
    @Embedded
    var member: Member,
    @Relation(
        entity = Team::class, parentColumn = "id", entityColumn = "id",
        associateBy = Junction(
            MemberTeamMap::class,parentColumn = "memberIdMap", entityColumn = "teamIdMap"
        )
    )
    var teams: List<Team>
)

The respective queries, just have to retrieve the parent, Room then extracts all of the children. So you could have the following coded in a/your Dao/s :-

@Insert
abstract fun insert(member: Member): Long
@Insert
abstract fun insert(team: Team): Long
@Insert
abstract fun insert(memberTeamMap: MemberTeamMap): Long

@Query("SELECT * FROM member")
@Transaction
abstract fun getAllMembersWithTeams(): List<MemberWithTeams>

@Query("SELECT * FROM team")
@Transaction
abstract fun getAllTeamsWithMember(): List<TeamWithMembers>

Putting the above into action to demonstrate, consider the following :-

    var tag = "TEAMDBINFO"
    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    // Add some members and teams
    dao.insert(Member(id = "M1",name = "Member1"))
    dao.insert(Member(id = "M2", name = "Member2"))
    dao.insert(Member(id = "M3", name = "Member3"))
    dao.insert(Member(id = "M4", name = "Member4"))
    dao.insert(Member(id = "M5", name = "Member5"))
    dao.insert(Team(id = "T1", name = "Team1"))
    dao.insert(Team(id = "T2", name = "Team2"))
    dao.insert(Team(id = "T3",name = "Team3"))
    dao.insert(Team(id = "T4",name = "Team4"))

    // do the mapping
    dao.insert(MemberTeamMap("M1","T1"))
    dao.insert(MemberTeamMap("M3","T1"))
    dao.insert(MemberTeamMap("M5","T1"))

    dao.insert(MemberTeamMap("M2","T2"))
    dao.insert(MemberTeamMap("M4","T2"))

    dao.insert(MemberTeamMap("M1","T3"))
    dao.insert(MemberTeamMap("M2","T3"))
    dao.insert(MemberTeamMap("M3","T3"))
    dao.insert(MemberTeamMap("M4","T3"))
    dao.insert(MemberTeamMap("M5","T3"))

    // Extract the Teams and their members :-

    for(twm: TeamWithMembers in dao.getAllTeamsWithMember()) {
        Log.d(tag,"Team is ${twm.team.name}")
        for(m: Member in twm.members) {
            Log.d(tag,"\tMember is ${m.name}")
        }
    }

If the above is run then the log would include :-

D/TEAMDBINFO: Team is Team1
D/TEAMDBINFO:   Member is Member1
D/TEAMDBINFO:   Member is Member3
D/TEAMDBINFO:   Member is Member5
D/TEAMDBINFO: Team is Team2
D/TEAMDBINFO:   Member is Member2
D/TEAMDBINFO:   Member is Member4
D/TEAMDBINFO: Team is Team3
D/TEAMDBINFO:   Member is Member1
D/TEAMDBINFO:   Member is Member2
D/TEAMDBINFO:   Member is Member3
D/TEAMDBINFO:   Member is Member4
D/TEAMDBINFO:   Member is Member5
D/TEAMDBINFO: Team is Team4