2

Let's say I have two entities, question and answer which have a one-to-many relation, for each question there could be 0..n answers. They are defined as:

@Entity(tableName="questions")
data class Question (
  @PrimaryKey
  val id: Long,
  val questionText: String
)

@Entity(tableName="answers")
data class Answer (
  @PrimaryKey
  val id: Long,
  val questionId: Long,
  val userId: Long,
  val answerText: String
)

data class QuestWithAns(
  @Embeded
  val question: Question,
  @Relation(parentColumn = "id", entityColumn = "questionId", entity = Answer::class)
  val answer: Answer?
)

I'd like to query the database and obtain a list of QuestWithAns objects with all the questions and the answers of only one userId or null if this user hasn't answered the question yet. I tried to implement this query in a DAO object as follows:

@Dao
abstract class QuestionDAO {

  @Query("SELECT * FROM questions AS quest LEFT OUTER JOIN (SELECT * FROM answers WHERE userid=:userId) AS ans ON quest.id = ans.questionId")
  abstract fun getQuestWithAnsByUser(userId: Long): LiveData<List<QuestWithAns>>
}

But on the questions this user hasn't answered, room is returning the last answer from any other user instead of null.

Can anyone help me identify what I'm doing wrong? Is there any way to implement this query with @Relation?

oitantksi
  • 21
  • 1
  • 2
  • I hit the same issue after following someone's answer that appeared to be a solution. It's as if the relation completely ignores the query order and instead runs apart from it. – Shadow Aug 12 '20 at 02:34

0 Answers0