Android Room @Relation many-to-many?

Question

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"

Answer 1

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();
}

Answer 2

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.