我是一个 SQL 菜鸟，不明白为什么我的语句会这样。它在 Android Room DAO 中使用并返回不需要的结果。
I have this sample data in two tables:
我有这样的声明：
@Transaction
@Query("Select Distinct Category.* " +
"from Category " +
"inner join Items on Category.ID = Items.Category " +
"where IsExcluded = 0 " +
"order by lower( Category.Name ) asc")
LiveData<List<CatViewWithItemList>> getCatViewWithItemListGlobal();
结果由一个简单的 POJO 接收：
public class CatViewWithItemList {
@Embedded
public Cat myCat;
@Relation(parentColumn = "ID",
entityColumn = "Category") public List<ItemS> ItemList;
Problem:我不明白结果：
为什么第 5 项被退回？它应该被 where 子句排除。我的说法有错误吗？
MikeT built a sample app that contains the same bug:
（编辑：我现在已经放弃了布尔转换器，Room 似乎可以在没有它的情况下解析布尔值）或者这可能是由我的布尔值简单类型转换器引起的？一和零实际上是布尔值的占位符：
@TypeConverter
public Boolean fromInt(int value) {
return value == 0 ? false : true;
}
@TypeConverter
public int toInt(Boolean bValue) {
if (bValue == false) {
return 0;
} else {
return 1;
}
}
感谢您的阅读！
编辑： 非常感谢@Angela。她test http://sqlfiddle.com/#!9/5fcf6b/1表明 sql 语句没有问题，并且该行为是由 sqlite 的特殊性或接收器 pojo 中的嵌入关系引起的。有人对此有建议吗？
编辑： MikeT 解释了为什么我想要做的事情在房间关系中似乎是不可能的——至少只要没有人证明不同。在他的回答中，他提供了一种替代方案。
我相信这要么是由于您的简单转换器，要么是由于项目实体的定义方式（我用过boolean for 被排除并且在没有转换器的情况下它似乎工作正常）.
这是使用我的项目实体版本：-
@Entity(foreignKeys = @ForeignKey(entity = Category.class,parentColumns = "id", childColumns = "category", onDelete = CASCADE))
public class Items {
@PrimaryKey(autoGenerate = true)
private long id;
private String category;
private boolean isexcluded;
public void setCategory(String category) {
this.category = category;
}
public void setId(long id) {
this.id = id;
}
public void setIsexcluded(boolean isexcluded) {
this.isexcluded = isexcluded;
}
public String getCategory() {
return this.category;
}
public long getId() {
return this.id;
}
public boolean isIsexcluded() {
return this.isexcluded;
}
}
以及 DAO 类别：-
@Dao
interface CategoryDAO {
@Query("SELECT * FROM Category")
List<Category> getAllcategories();
@Query("SELECT DISTINCT Category.* " +
"FROM CATEGORY " +
"INNER JOIN ITEMS ON category.id = Items.category " +
"WHERE isexcluded = 0 " +
"ORDER BY lower(category.name)")
List<Category> getSpeacial();
@Query("SELECT DISTINCT Category.* " +
"FROM CATEGORY " +
"INNER JOIN ITEMS ON category.id = Items.category " +
"WHERE isexcluded = 0 " +
"ORDER BY lower(category.name)")
List<CatViewWithItemList> getSpeacial2();
@Insert
public long[] insertCategory(Category... Category);
}
随着
new Thread(new Runnable() {
@Override
public void run() {
//mRoomDB.categoryDao().insertCategory(initialCategories());
//mRoomDB.itemsDAO().insertItem(initialItems());
List<Category> categoryList = mRoomDB.categoryDao().getAllcategories();
List<Items> itemsList = mRoomDB.itemsDAO().getAllItems();
for (Category c: categoryList) {
Log.d("CATEGORY","Category is " + c.getName() + " refrence is " + c.getId());
}
for (Items i: itemsList) {
Log.d("ITEM","Item is " + i.getId() + " Category reference is " + i.getCategory() + " Is Excluded is " + Boolean.toString(i.isIsexcluded()));
}
List<Category> categoryList2 = mRoomDB.categoryDao().getSpeacial();
for (Category c: categoryList2) {
Log.d("CATEGORY2","Category is " + c.getName() + " reference is " + c.getId());
}
List<CatViewWithItemList> catViewWithItemLists = mRoomDB.categoryDao().getSpeacial2();
for (CatViewWithItemList cvwil: catViewWithItemLists) {
Log.d("CATVIEWITEM","Category = " + cvwil.myCat.getId() + " ID = " + cvwil.ItemList.get(0).getId() + " IsExcluded = " + Boolean.toString(cvwil.ItemList.get(0).isIsexcluded()));
}
}
}).start();
与 ：-
public class CatViewWithItemList {
@Embedded
public Category myCat;
@Relation(parentColumn = "id",
entityColumn = "category")
public List<Items> ItemList;
}
然后结果是（第一部分基础数据（哦，所以我不能拼写第三部分:)））：-
2018-12-19 21:47:05.376 2109-2125/? D/CATEGORY: Category is firstname refrence is a
2018-12-19 21:47:05.377 2109-2125/? D/CATEGORY: Category is secondname refrence is b
2018-12-19 21:47:05.377 2109-2125/? D/CATEGORY: Category is thridname refrence is c
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 1 Category reference is a Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 2 Category reference is c Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 3 Category reference is null Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 4 Category reference is b Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 5 Category reference is b Is Excluded is true
2018-12-19 21:47:05.378 2109-2125/? D/ITEM: Item is 6 Category reference is null Is Excluded is true
然后是两组结果：-
2018-12-19 21:47:05.379 2109-2125/? D/CATEGORY2: Category is firstname reference is a
2018-12-19 21:47:05.380 2109-2125/? D/CATEGORY2: Category is secondname reference is b
2018-12-19 21:47:05.380 2109-2125/? D/CATEGORY2: Category is thridname reference is c
2018-12-19 21:47:05.382 2109-2125/? D/CATVIEWITEM: Category = a ID = 1 IsExcluded = false
2018-12-19 21:47:05.382 2109-2125/? D/CATVIEWITEM: Category = b ID = 4 IsExcluded = false
2018-12-19 21:47:05.382 2109-2125/? D/CATVIEWITEM: Category = c ID = 2 IsExcluded = false
我相信您的问题不在于查询，而在于猫查看项目列表因为您正在用与类别相关的所有项目填充项目列表，无论查询/之后如何（又名我认为，将项目列表嵌入到类别中）。这确实让我感到困惑，因为你真的只想要特定的相关项目（我刚刚在代码中抓住了第一个），例如cvwil.ItemList.get(0).isIsexcluded()幸运的是 4 出现在 5 之前，因此 isexclusion 显示为 false。
我相信您也许应该从项目中解决这个问题，加入类别，例如就像是
SELECT *
FROM Items
JOIN Category ON Items.category = category.id
WHERE Items.isexcluded = 0
ORDER BY category.name ASC;
我将更新代码gitHub https://github.com/Mike-j-t/SO53839431ROOMRELATIONSHIP为此（请注意，我已将类别表更改为具有唯一的列名称类别ID而不是 id。）
现在的结果是（注意添加了另一个类别和另外 2 个项目，顺序相反，即第一个项目（id 7）被排除为 true，然后 id 8 为 false）：-
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is firstname Category ID is a
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is secondname Category ID is b
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is thirdname Category ID is c
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is fourthname Category ID is d
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 1 Category reference is a Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 2 Category reference is c Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 3 Category reference is null Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 4 Category reference is b Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 5 Category reference is b Is Excluded is true
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 6 Category reference is null Is Excluded is true
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 7 Category reference is d Is Excluded is true
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 8 Category reference is d Is Excluded is false
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =1 Category reference is a Is Excluded is false Referenced Category is a Referenced Category name is firstname
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =2 Category reference is c Is Excluded is false Referenced Category is c Referenced Category name is thirdname
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =4 Category reference is b Is Excluded is false Referenced Category is b Referenced Category name is secondname
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =8 Category reference is d Is Excluded is false Referenced Category is d Referenced Category name is fourthname