Android Room DAO get one field with crossRef

Question

I have two enities

@Entity
data class Student(
    @PrimaryKey(autoGenerate = false)
    val studentId: Int,
    val studentName: String
)

and

@Entity
data class Subject(
    @PrimaryKey(autoGenerate = false)
    val subjectId: Int,
    val subjectName: String
)

and crossRef entity for them

@Entity(primaryKeys = ["studentId", "subjectId"])
data class StudentSubjectCrossRef(
    val studentId: Int,
    val subjectId: Int
)

I want to get a list of subject names, which a student with specific name studies.
I can get an object with a list of subjects assotiated with him.

But how to write DAO to get a list of Strings with the names of the subjects only?

Answer 1

You can try the following code:

@Dao
interface YourDao {
    @Query("SELECT su.subjectName FROM Subject su INNER JOIN StudentSubjectCrossRef ss ON ss.subjectId = su.subjectId INNER JOIN Student st ON ss.studentId = st.studentId WHERE st.studentName = :studentName")
    suspend fun getSubjectNamesByStudentName(studentName: String): List<String>
}

Answer 2

I not sure if I understand your question.

If you need a list of String with name of subjects for each student you need a data class with a embedded user and a relation subject with projection

data class StudentWithSubjects(
     @Embedded val student: Student,
     @Relation(
           parentColumn = "studentId",
           entity = Subject::class,
           entityColumn = "subjectId",
           projection = ["subjectName"]
     )
     val subjectNames: List<String>
)

And in your Dao class you only would need:

@Transaction
@Query("SELECT * FROM Student")
fun getStudentAndSubjects(): List<StudentWithSubjects>

Room will do the rest.