I am developing an android application and am using the new Architecture Components in Android OS: LiveData, ViewModel and Room. I have a small problem with the Room implementation about creating a @Relation which returns the result from a JOIN query (many-to-many relationship).
My DB structure looks like this:
@Entity
public class Student{
@PrimaryKey
private int id;
private String name;
private String email;
}
@Entity
public class Group{
@PrimaryKey
private int id;
private String name;
}
@Entity(foreignKeys = {
@ForeignKey(entity = Student.class,
parentColumns = "id",
childColumns = "student_id"),
@ForeignKey(entity = Group.class,
parentColumns = "id",
childColumns = "group_id")
})
public class StudentGroup{
private int studentId;
private int groupId;
}
How I can get all Groups only for a specific student, something like this?
public class StudentWithGroups{
@Relation(parentColumn = "id", entityColumn = "rule_id", entity =
StudentGroup.class)
private List<Group> groups;
}
I already checked questions like How can I represent a many to many relation with Android Room? and Android Persistence room: "Cannot figure out how to read this field from a cursor"
With the introduction to Junction in room you can handle many-to-many relationship with ease.
Modify primary key of student and group tables as:
@Entity
public class Student{
@PrimaryKey
private int sId;
private String name;
private String email;
}
@Entity
public class Group{
@PrimaryKey
private int gId;
private String name;
}
@Entity(foreignKeys = {
@ForeignKey(
entity = Student.class,
parentColumns = "sId",
childColumns = "studentId"
),
@ForeignKey(
entity = Group.class,
parentColumns = "gId",
childColumns = "groupId"
)
})
public class StudentGroup{
private int studentId;
private int groupId;
}
You can get all groups of specific student as:
public class StudentWithGroups{
@Embedded
Student student;
@Relation(
parentColumn = "sId",
entity = Group.class,
entityColumn = "gId",
associateBy = @Junction(
value = StudentGroup.class,
parentColumn = "studentId",
entityColumn = "groupId"
)
)
List<Group> groups;
}
Now you can query database for the result as:
@Dao
public interface StudentDao {
@Query("SELECT * FROM Student")
List<StudentWithGroups> getGroupsOfStudent();
}
How I can get all Groups only for specific student, something like that?
In this sample code, I have:
@Query("SELECT categories.* FROM categories\n"+
"INNER JOIN customer_category_join ON categories.id=customer_category_join.categoryId\n"+
"WHERE customer_category_join.customerId=:customerId")
List<Category> categoriesForCustomer(String customerId);
Translating that into your entities results in something like:
@Query("SELECT Group.* FROM Group\n"+
"INNER JOIN StudentGroup ON Group.id=StudentGroup.groupId\n"+
"WHERE StudentGroup.studentId=:studentId")
List<Group> groupsForStudent(String studentId);
In general, with Room, work out the SQL ignoring Room, then use that SQL in your DAO.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With