How to Represent a "Many to Many" Relation with Android Room When Column Names Are Same

How to access each column on many-to-many relationships in room database android

Then what is the correct way to access each column on every table using room database?

You need to follow the hierarchy.

That is you need, at the lower level, an OrderItemWithItem that has @Embedded OrderItem and @Relation Item. Then your OrderWithItem should have @Embedded for the Order and @Relation for the OrderItemWithItem.

But this give me inconcistence result

I believe that you do not want a many-many relationship as is introduced by associating. That is an order will have a unique set of orderitems rather than an order having many orderitems which may also be used by other orders.

So I believe that you want something like :-

data class OrderItemWithItem(
    @Embedded
    val orderItemModel: OrderItemModel,
    @Relation(entity = ItemModel::class, parentColumn = "item_id", entityColumn = "id")
    val itemList: List<ItemModel>
)

• the lower hierarchy that will have a list (although you probably only want 1) of items.

With :-

data class OrderWithItems(

    @Embedded
    val order: OrderModel,
    @Relation(entity = OrderItemModel::class, parentColumn = "id", entityColumn = "order_id")
    val orderWithItems: List<OrderItemWithItem>

)

---

Working Example/Demo

Using your code and the above along with :-

@Dao
abstract class AllDao {

    @Insert
    abstract fun insert(orderModel: OrderModel): Long
    @Insert
    abstract fun insert(orderItemModel: OrderItemModel): Long
    @Insert
    abstract fun insert(itemModel: ItemModel): Long

    @Transaction
    @Query("SELECT * FROM order_table")
    abstract fun getAllFullOrders(): List<OrderWithItems>

}

Then using :-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    val item1 = "Item1"
    val item2 = "Item2"
    val item3 = "Item3"

    val order1 = "Order1"
    val order2 = "Order2"
    val order3 = "Order3"

    dao.insert(ItemModel(item1,"Item1Name","10",100))
    dao.insert(ItemModel(item2,"Item2Name","20",200))
    dao.insert(ItemModel(item3,"Item3Name","30",300))

    dao.insert(OrderModel(order1,"0","?","?"))
    dao.insert(OrderModel(order2,"0","?","?"))
    dao.insert(OrderModel(order3,"0","?","?"))

    dao.insert(OrderItemModel(order1,item1,2,"100","200"))
    dao.insert(OrderItemModel(order2, item1,5,"100","500"))
    dao.insert(OrderItemModel(order2,item2,3,"200","600"))
    dao.insert(OrderItemModel(order3,item1,7,"100","700"))
    dao.insert(OrderItemModel(order3,item2,4,"200","800"))
    dao.insert(OrderItemModel(order3,item3,3,"300","900"))

    for(owi in dao.getAllFullOrders()) {
        Log.d("DBINFO","Order is ${owi.order.orderId} etc")
        for (oiwi in owi.orderWithItems) {
            Log.d("DBINFO","\t ItemId is ${oiwi.orderItemModel.itemId} Quantity is ${oiwi.orderItemModel.qty} Total is ${oiwi.orderItemModel.totalPrice}")
            for(i in oiwi.itemList) {
                Log.d("DBINFO","\t\tItem is ${i.itemName} name is ${i.itemName} price is ${i.itemPrice} in stock is ${i.itemStock}")
            }
        }
    }

Results in the Log including :-

D/DBINFO: Order is Order1 etc
D/DBINFO:    ItemId is Item1 Quantity is 2 Total is 200
D/DBINFO:       Item is Item1Name name is Item1Name price is 10 in stock is 100

D/DBINFO: Order is Order2 etc
D/DBINFO:    ItemId is Item1 Quantity is 5 Total is 500
D/DBINFO:       Item is Item1Name name is Item1Name price is 10 in stock is 100
D/DBINFO:    ItemId is Item2 Quantity is 3 Total is 600
D/DBINFO:       Item is Item2Name name is Item2Name price is 20 in stock is 200

D/DBINFO: Order is Order3 etc
D/DBINFO:    ItemId is Item1 Quantity is 7 Total is 700
D/DBINFO:       Item is Item1Name name is Item1Name price is 10 in stock is 100
D/DBINFO:    ItemId is Item2 Quantity is 4 Total is 800
D/DBINFO:       Item is Item2Name name is Item2Name price is 20 in stock is 200
D/DBINFO:    ItemId is Item3 Quantity is 3 Total is 900
D/DBINFO:       Item is Item3Name name is Item3Name price is 30 in stock is 300

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

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 :-

1000,1
1000,3
1000,5

1001,2
1001,4

1002,1
1002,2
1002,3
1002,4
1002,5

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

Room @Relation annotation with a One To Many relationship

To use @Relation the parent table must be available so that the parent column can be found.

So you would need something along the lines of :-

data class Chore(
    @Embedded
    var choreEntity: ChoreEntity, //<<<<<
    var name: String,
    //This is the line that doesn't work
    @Relation(parentColumn = "id", entityColumn = "userId", entity = UserEntity::class)
    var contributingUsers: List<User>,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

In short @Relation results in a sub query being invoked that retrieves ALL of the children of the parent (which MUST exist/be known) thus an @Embed of that parent is required. Note that these are for use by the convenience methods, which are a little restrictive in nature.

However, as you have an intermediate table (mapping/associative/reference .... table) then you need to tell Room about this by using the associateBy parameter to define the Junction

• there is no need for such a table for one-to-many relationships, such tables can be used but are really for many-many relationships.

@Relation will build the underlying query to access the children accordingly.

If you want the result of your query then a Chore object could just be:-

data class Chore(
    var name: String,
    var contributingUsers,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

BUT a row would exist for every combination that is for every User that is related to a Chore there would be a row i.e. the result is the cartesian product.

If you wanted to build a Chore with a list of it's child Users then you would have to

• a) either process the entire result building the resultant List or
• b) just extract the respective Chore and then run a query per extracted Chore that returns the List.

Working Examples

Based upon UserEntity being :-

@Entity( tableName = TableNames.CHORE)
data class ChoreEntity(
    @PrimaryKey
    val id: Long?=null,
    val name: String,
    val drawableRes: Int
)

and UserEntity being :-

@Entity(tableName = TableNames.USER)
data class UserEntity(
    @PrimaryKey
    val id: Long? = null,
    val name: String,
    val imageRes: Int
    /* etc */
)

and ChoreToUser being:-

@Entity(
    tableName = TableNames.CHORE_TO_USER,
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("userId"),
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE,
        ),
        ForeignKey(
            entity = ChoreEntity::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("choreId"),
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE,
        )
    ],
    // indices = [Index("choreId"), Index("userId")], // Replaced by required primary key
    primaryKeys = ["choreId","userId"]
)
data class ChoreToUser(
    var userId: Long,
    var choreId: Long,
    @ColumnInfo(index = true)
    var canFulfill: Boolean,
)

• Note that the index has been replaced with @Primary key (Room requires a Primary Key). They are effectively the same. Additionally @ColumnInfo has been used to also create an index on the userId column (more efficient).

Example 1 - Cartesian Product from you Query

So using the sort of equivalent of your Chore class there is Chore1 :-

data class Chore1(
    var name: String,
    var userName: String, // ADDED for demo
    //This is the line that doesn't work
    //@Relation(parentColumn = "id", entityColumn = "userId", entity = UserEntity::class)
    //var contributingUsers: List<User>,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

• @Relation cannot be used without an @Embedded so commented out.

The dao function used with the above was:-

@Query("SELECT Chores.name, drawableRes," +
        "Users.name as userName, Users.imageRes as userPhoto, " +
        " 10 /*(COUNT(ChoreLogs.id) = 1)*/ as done " + // not using Chorelogs table so fake output
        "FROM Chores " +
        "LEFT JOIN ChoreToUsers ON ChoreToUsers.choreId = Chores.id " +
        "LEFT JOIN Users ON ChoreToUsers.userId = Users.id " +
        "/* LEFT JOIN ChoreLogs ON ChoreLogs.choreToUserId = ChoreToUsers.id */") // SQL commented out for Brevity
fun getChoreTiles(): List<Chore1> /* Cartesian Product */

• Note for convenience/brevity the ChoreLogs JOIN has been excluded

• See results for EX01

Example 2 - Using the option a)

Here Chore2 has been used as the resultant class, it being:-

data class Chore2(
    var name: String,
    //This is the line that doesn't work
    //@Relation(parentColumn = "id", entityColumn = "userId", entity = UserEntity::class)
    var contributingUsers: List<User>,
    @DrawableRes var drawableRes: Int,
    var done: Boolean
)

• As can be seen the contributingUsers is a List

This used in conjunction with 2 queries and a function that uses the queries, these being:-

@Query("SELECT * FROM Chores")
fun getAllChores(): List<ChoreEntity>
@Query("SELECT * FROM  ChoreToUsers JOIN Users ON ChoreToUsers.userId = Users.id WHERE ChoreToUsers.choreId=:choreId")
fun getRelatedUsersForAChore(choreId: Long): List<UserEntity>
@Transaction
@Query("")
fun getRelatedUsersPerChoreAsList(): List<Chore2> {
    var rv = arrayListOf<Chore2>()
    for (ct in getAllChores()) {
        var ul = arrayListOf<User>()
        for (ue in getRelatedUsersForAChore(ct.id!!)) {
            ul.add(User(ue.name,ue.imageRes))
        }
        rv.add(Chore2(ct.name,ul.toList(),ct.drawableRes,false))
    }
    return rv
}

• again no need for an @Relation as the queries do all that is required.

• See results for EX02

Example 3 - using option b) BUT via Room

i.e. using @Embedded, with @Relation AND as there is the intermediate associative table associateBy and the Junction.

• i.e. letting Room build the sub query(ies)

In this case the equivalent class is Chore3 :-

data class Chore3(
    @Embedded
    val chore: ChoreEntity,
    @Relation(
        entity = UserEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = ChoreToUser::class, parentColumn = "choreId", entityColumn = "userId"
        )
    )
    val contributingUsers: List<UserEntity>
)

The Dao function being:-

@Transaction
@Query("SELECT * FROM Chores")
fun getAllChore3s(): List<Chore3>

• See results for EX03

Testing/Demonstrating the 3 Examples

The following code was included in an activity (run on the main thread for convenience/brevity):-

const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val u1Id = dao.insert(UserEntity(name = "User1", imageRes = 1000))
        val u2Id = dao.insert(UserEntity(name = "User2", imageRes = 2000))
        val u3Id = dao.insert(UserEntity(name = "user3", imageRes = 3000))
        val u4Id = dao.insert(UserEntity(name = "user4", imageRes = 4000))

        val c1Id = dao.insert(ChoreEntity(name = "Chore1", drawableRes = 10000))
        val c2Id = dao.insert(ChoreEntity(name = "Chore2",drawableRes = 20000))
        val c3Id = dao.insert(ChoreEntity(name = "Chore3",drawableRes = 30000))
        val c4Id = dao.insert(ChoreEntity(name = "Chore4",drawableRes = 40000))
        val c5Id = dao.insert(ChoreEntity(name = "Chore5",drawableRes = 50000))
        val c6Id = dao.insert(ChoreEntity(name = "Chore6",drawableRes = 60000))

        /* Mapping */

        dao.insert(ChoreToUser(u1Id,c1Id,false))
        dao.insert(ChoreToUser(u1Id,c2Id,true))
        dao.insert(ChoreToUser(u1Id,c3Id,false))
        dao.insert(ChoreToUser(u1Id,c4Id,false))

        dao.insert(ChoreToUser(u2Id,c5Id,true))
        dao.insert(ChoreToUser(u2Id,c6Id,true))

        dao.insert(ChoreToUser(u3Id,c1Id,false))
        dao.insert(ChoreToUser(u3Id,c2Id,false))
        dao.insert(ChoreToUser(u3Id,c3Id,false))
        dao.insert(ChoreToUser(u3Id,c4Id,false))
        dao.insert(ChoreToUser(u3Id,c5Id,false))
        dao.insert(ChoreToUser(u3Id,c6Id,false))

        /* EX01 - Cartesain result */
        for (ct in dao.getChoreTiles()) {
            Log.d(TAG+"_EX01","Chore is ${ct.name} + User is ${ct.userName}")
        }

        /* EX02 - using SQl with JOINS */
        for (ct in dao.getRelatedUsersPerChoreAsList()) {
            Log.d(TAG+"EX02","Chore is ${ct.name}, image is ${ct.drawableRes}, there are ${ct.contributingUsers.size}  contributing Users:-" )
            for (u in ct.contributingUsers) {
                Log.d(TAG+"EX02","\tUser is ${u.userName}, photo is ${u.userPhoto}")
            }
        }

        /* EX03 = using @Embedded/@Relation and associateBy/Junction */
        for (c3 in dao.getAllChore3s()) {
            Log.d(TAG+"EX03","Chore is ${c3.chore.name}, image is ${c3.chore.drawableRes}, there are ${c3.contributingUsers.size} contributing users:-")
            for (u in c3.contributingUsers) {
                Log.d(TAG+"EX03","\tUser is ${u.name}, photo is ${u.imageRes}")
            }
        }
    }
}

• The majority of the code is just loading the data which ends up being:-

and

• Note that above data takes advantage of the many-many allowable by an associative table.

Results (aka output included in the log, split per example)

DBINFO_EX01: Chore is Chore1 + User is User1
DBINFO_EX01: Chore is Chore1 + User is user3
DBINFO_EX01: Chore is Chore2 + User is User1
DBINFO_EX01: Chore is Chore2 + User is user3
DBINFO_EX01: Chore is Chore3 + User is User1
DBINFO_EX01: Chore is Chore3 + User is user3
DBINFO_EX01: Chore is Chore4 + User is User1
DBINFO_EX01: Chore is Chore4 + User is user3
DBINFO_EX01: Chore is Chore5 + User is User2
DBINFO_EX01: Chore is Chore5 + User is user3
DBINFO_EX01: Chore is Chore6 + User is User2
DBINFO_EX01: Chore is Chore6 + User is user3

DBINFOEX02: Chore is Chore1, image is 10000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore2, image is 20000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore3, image is 30000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore4, image is 40000, there are 2  contributing Users:-
DBINFOEX02:     User is User1, photo is 1000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore5, image is 50000, there are 2  contributing Users:-
DBINFOEX02:     User is User2, photo is 2000
DBINFOEX02:     User is user3, photo is 3000
DBINFOEX02: Chore is Chore6, image is 60000, there are 2  contributing Users:-
DBINFOEX02:     User is User2, photo is 2000
DBINFOEX02:     User is user3, photo is 3000

DBINFOEX03: Chore is Chore1, image is 10000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore2, image is 20000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore3, image is 30000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore4, image is 40000, there are 2 contributing users:-
DBINFOEX03:     User is User1, photo is 1000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore5, image is 50000, there are 2 contributing users:-
DBINFOEX03:     User is User2, photo is 2000
DBINFOEX03:     User is user3, photo is 3000
DBINFOEX03: Chore is Chore6, image is 60000, there are 2 contributing users:-
DBINFOEX03:     User is User2, photo is 2000
DBINFOEX03:     User is user3, photo is 3000

• as can be seen EX02 and EX03 produce the same output.

Android room one to many relationship

Your issue/problem hasn't been clearly stated. However, the following is a working example that demonstrates two ways of extracting the relationships based upon your schema.

• Note the use of Class as a class is fraught with potential issues and it is not recommended at all. However, the following does use the Class and to circumvent some issues may not fully reflect your schema.

Example code

The School entity :-

@Entity(tableName = "_school")
class School {
    @PrimaryKey
    @ColumnInfo(name = "school_id")
    Long Schoolid;
    @NonNull
    @ColumnInfo(name = "school_name")
    String SchoolName;

    School(){}

    @Ignore
    School(String schoolName) {
        this.SchoolName = schoolName;
    }
}

The Class (an unwise choice of name) :-

@Entity(tableName = "_class")
class Class {
    @PrimaryKey
    @ColumnInfo(name = "class_id")
    Long ClassId;
    @NonNull
    @ColumnInfo(name = "class_name")
    String ClassName;

    Class(){}

    @Ignore
    Class(String className) {
        this.ClassName = className;
    }
}

The Student entity ( Foreign Key Constraints included):-

@Entity(
        tableName = "_student", foreignKeys = {
        @ForeignKey(
                entity = School.class,
                parentColumns = {"school_id"},
                childColumns = {"school_id"},
                onDelete = ForeignKey.CASCADE,
                onUpdate = ForeignKey.CASCADE
        ),
        @ForeignKey(
                entity = Class.class,
                parentColumns = {"class_id"},
                childColumns = {"class_id"},
                onDelete = ForeignKey.CASCADE,
                onUpdate = ForeignKey.CASCADE
        )
        }
)
class Student {
    @PrimaryKey
    @ColumnInfo(name = "student_id")
    Long StudentId;
    @ColumnInfo(name = "Student_name")
    String StudentName;
    @ColumnInfo(name = "school_id", index = true)
    Long SchoolId;
    @ColumnInfo(name = "class_id", index = true)
    Long ClassId;

    Student(){}

    @Ignore
    Student(String studentName, long schoolId, long classId) {
        this.StudentName = studentName;
        this.SchoolId = schoolId;
        this.ClassId = classId;
    }
}

POJO Method 1 - Class StudentAndSchoolAndClass - (DOES NOT USE @Relation)

class StudentAndSchoolAndClass {

    @Embedded
    Student student;
    String school_name;
    String class_name;
}

POJO Method 2 - Class StudentWithSchoolWithClass - (Uses @Relation's)

class StudentWithSchoolWithClass {

    @Embedded
    Student student;

    @Relation(entity = School.class,parentColumn = "school_id", entityColumn = "school_id")
    List<School> schoolList;
    @Relation(entity = Class.class,parentColumn = "class_id",entityColumn = "class_id")
    List<Class> classList;
}

The Dao Interface AllDao

@Dao
interface AllDao {

    @Insert
    Long insertSchool(School s);
    @Insert
    Long insertClass(Class c);
    @Insert
    Long insertStudent(Student s);
    @Query("SELECT * FROM _school")
    List<School> getAllSchools();
    @Query("SELECT * FROM _school WHERE school_id = :school_id ")
    School getSchoolById(Long school_id);
    @Query("SELECT * FROM _class")
    List<Class> getAllClasses();
    @Query("SELECT * FROM _class WHERE class_id = :class_id")
    Class getClassById(Long class_id);
    @Query("SELECT * FROM _student JOIN _school ON _school.school_id = _student.school_id JOIN _class ON _class.class_id = _student.class_id")
    List<StudentAndSchoolAndClass> getStudentAndSchoolAndClass();
    @Query("SELECT * FROM _student")
    List<StudentWithSchoolWithClass> getStudentWithSchoolWithClass();

}

• Note The two last queries use the respective POJO and especially that
• the PJO with @Relations has the relationships defined via the JOIN's

The @Database class MyDatabase

@Database(entities = {School.class,Class.class,Student.class},version = 1)
abstract class MyDatabase extends RoomDatabase {
    abstract AllDao allDao();
}

Lastly an Activity MainActivity that loads some data into the database and then extracts some of the data using the 2 @Queries and the respective POJO class.

---

Related Topics