让我们看一个例子:我有一个表单,它有几个部分,每个部分都有问题。在旁边,我有与问题对应的答案,并且当查询时,我想过滤掉另一列:
在DAO部分,我希望能够检索它们所有的内容。
以下是我想要通过此查询填充的POJO:
在另一个应用程序中,查询将会是:
然后在生成的代码中,会有以下伪代码:
基本上,它创建了两个查询:一个用于主对象,另一个用于关系。第二个查询是自动创建的,我们无法控制它。
要回到我的问题,在我想要关系但也要过滤子列的情况下,我陷入了困境:
在第一个查询中,我不能引用
Room是否支持这种操作,还是我必须自己创建子查询?
额外问题:为什么他们不在单个查询中连接表格而是创建两个查询?这是出于性能考虑吗?
编辑以澄清我的期望:
因此,我有以下实体:
@Entity(tableName = "sections")
public class Section {
@PrimaryKey
public long id;
public String title;
}
@Entity(tableName = "questions")
public class Question {
@PrimaryKey
public long id;
public String title;
public long sectionId;
}
@Entity(tableName = "answers")
public class Answer {
@PrimaryKey
public long id;
public long questionId;
public int otherColumn;
}
在DAO部分,我希望能够检索它们所有的内容。
以下是我想要通过此查询填充的POJO:
class SectionWithQuestions {
@Embedded
public Section section;
@Relation(parentColumn = "id", entityColumn = "sectionId", entity = Question.class)
public List<QuestionWithAnswer> questions;
public static class QuestionWithAnswer {
@Embedded
public Question question;
@Relation(parentColumn = "id", entityColumn = "questionId", entity = Answer.class)
List<Answer> answers;
}
}
在另一个应用程序中,查询将会是:
SELECT s.*, q.*, a.*
FROM sections s
LEFT JOIN questions q ON q.sectionId = s.id
LEFT JOIN answers a ON a.questionId = q.id
WHERE s.id = :sectionId and a.otherColumn = :otherColumn
然而在 Room 中,我发现如果你想要一个对象及其关系(例如示例中的用户和宠物),你只需选择该对象,关系将在第二个查询中查询。代码如下:
@Query("SELECT * FROM sections WHERE id = :sectionId")
然后在生成的代码中,会有以下伪代码:
sql = "SELECT * FROM sections WHERE id = :sectionId" // what's inside @Query
cursor = query(sql)
int indexColumn1 = cursor.getColumnIndex(col1)
int indexColumn2
... etc
while (cursor.moveToNext) {
masterObject = new object()
masterObject.property1 = cursor.get(indexColumn1)
... etc
__fetchRelationshipXXXAsYYY(masterObject.relations) // fetch the child objects
}
这个 __fetch XXX as YYY
方法如下:
sql = "SELECT field1, field2, ... FROM a WHERE foreignId IN (...)"
similar algo as previously: fetch column indices, and loop through the cursor
基本上,它创建了两个查询:一个用于主对象,另一个用于关系。第二个查询是自动创建的,我们无法控制它。
要回到我的问题,在我想要关系但也要过滤子列的情况下,我陷入了困境:
在第一个查询中,我不能引用
otherColumn
列,因为它不存在;在@Relation
中也不行,因为这个注释的唯一属性是连接列和实体定义。Room是否支持这种操作,还是我必须自己创建子查询?
额外问题:为什么他们不在单个查询中连接表格而是创建两个查询?这是出于性能考虑吗?
编辑以澄清我的期望:
@Query("SELECT s.*, q.*, a.* " +
"FROM sections s " +
"LEFT JOIN questions q ON q.sectionId = s.id " +
"LEFT JOIN answers a ON a.questionId = q.id " +
"WHERE s.id = :sectionId and a.otherColumn = :additionalIntegerFilter")
SectionWithQuestionsAndAnswers fetchFullSectionData(long sectionId);
static class SectionWithQuestionsAndAnswers {
@Embedded Section section;
@Relation(parentColumn = "id", entityColumn = "sectionId", entity = Question.class)
List<QuestionWithAnswers> questions;
}
static class QuestionWithAnswers {
@Embedded Question question;
@Relation(parentColumn = "id", entityColumn = "questionId", entity = Answer.class)
Answer answer; // I already know that @Relation expects List<> or Set<> which is
// not useful if I know I have zero or one relation (ensured
// through unique keys)
}
这是我想象中由Room生成的伪代码:
function fetchFullSectionData(long sectionId, long additionalIntegerFilter) {
query = prepare(sql); // from @Query
query.bindLong("sectionId", sectionId);
query.bindLong("additionalIntegerFilter", additionalIntegerFilter);
cursor = query.execute();
Section section = null;
long prevQuestionId = 0;
Question question = null;
while (cursor.hasNext()) {
if (section == null) {
section = new Section();
section.questions = new ArrayList<>();
section.field1 = cursor.get(col1); // etc for all fields
}
if (prevQuestionId != cursor.get(questionIdColId)) {
if (question != null) {
section.questions.add(question);
}
question = new Question();
question.fiedl1 = cursor.get(col1); // etc for all fields
prevQuestionId = question.id;
}
if (cursor.get(answerIdColId) != null) { // has answer
Answer answer = new Answer();
answer.field1 = cursor.get(col1); // etc for all fields
question.answer = answer;
}
}
if (section !=null && question != null) {
section.questions.add(question);
}
return section;
}
这是一个查询语句,能够获取到我所有的对象。
answer.otherColumn
。也许这是一个功能请求。我已经编辑了我的问题,并提供了更多关于我的期望的细节。 - Benoit Duffez