Room db具有多对多关系:在查询过滤后的数据时返回单个条目 [英] Room db with many to many relations : returns single entry on querying for filtered data

问题描述

我有3个实体Person，PersonTagRelation和Tag:人通过多对多关系与标签相关. 我还有一个dao，它具有查询数据库的多种功能. 大多数事情运行良好，但是我有一个查询，该查询想创建一个具有特定标签的过滤用户列表，该标签无法正常工作. 例如，如果有3个人A，B，C，相应的标签列表分别为[ai,ml]，[ai]，[flutter].那么我的查询应该返回 传递标签ai时[A,B].但是它仅返回[A]. 请签出以下代码，让我知道我哪里出了错:

I have 3 enitities Person, PersonTagRelation and Tag : Person is related to a tag via many to many relation. I also have a single dao for having multiple functions for querying db. Most of the things are working well , but i have this query that is suppose to create a filtered list of users having a particular tag , which doesn't sem to work. For eg, if there are 3 persons A,B,C with corresponding taglist as [ai,ml],[ai],[flutter] respectively. then my query is supposed to return [A,B] when passed the tag ai. But it is returning only [A]. Please checkout the following code and let me know where i went wrong:

Person.class

Person.class

@Entity()
public class Person {
    @PrimaryKey
    private long presonID;
    private String personName;
...
}

Tag.class

Tag.class

@Entity
public class Tag {

    @PrimaryKey
    private long tagID;
    private String tagValue;
    ...
}

PersonTagRelation.class

PersonTagRelation.class

@Entity(primaryKeys = {"personTagIDForPerson","personTagIDForTag"},
        foreignKeys = {
        @ForeignKey(entity = Person.class, parentColumns = "presonID", childColumns = "personTagIDForPerson"),
        @ForeignKey(entity = Tag.class, parentColumns = "tagID", childColumns = "personTagIDForTag"),
})
public class PersonTagRelation {
   private long personTagIDForPerson, personTagIDForTag;
   ...
   }

PersonDao.class

PersonDao.class

@Dao
public abstract class PersonDao {
    @Insert abstract void insertNewPerson(Person p);
    @Insert abstract void insertNewTag(Tag t);
    @Insert abstract void insertNewRelation(PersonTagRelation relation);

    @Query("SELECT * FROM Person ORDER BY presonID DESC")
    abstract List<Person> getAllPersons();

    @Query("SELECT tagID FROM Tag WHERE tagValue=:tag ORDER BY tagID DESC")
    abstract long getTagIdByTagName(String tag);

    @Query("SELECT * FROM Person" +
            " INNER JOIN PersonTagRelation ON Person.presonID=PersonTagRelation.personTagIDForPerson " +
            "WHERE PersonTagRelation.personTagIDForTag=:tagID")
    abstract List<Person> getAllPersonsForTagID(long tagID);

    @Query("SELECT * FROM Tag " +
            "INNER JOIN PersonTagRelation ON Tag.tagID=PersonTagRelation.personTagIDForTag " +
            "WHERE PersonTagRelation.personTagIDForPerson=:personID")
    abstract List<Tag> getAllTagsForPerson(long personID);


    @Transaction
    public void insertNewPersonTagData(UiModel model) {
        insertNewPerson(model.getPerson());
        for (Tag t : model.getTags()) {
            insertNewTag(t);
            insertNewRelation(new PersonTagRelation(model.getPerson().getPresonID(), t.getTagID()));
        }
    }
    @Transaction
    public List<UiModel> getFilteredModelList(String tag) {
        List<UiModel> resultlist = new ArrayList<>();
        long tagID = getTagIdByTagName(tag);
        List<Person> filteredPersonList = getAllPersonsForTagID(tagID);

        for (Person p : filteredPersonList) {
            List<Tag> associatedTags = getAllTagsForPerson(p.getPresonID());
            resultlist.add(new UiModel(p, associatedTags));
        }
        return resultlist;
    }

    @Transaction
    public List<UiModel> getAllAsModelList() {
        List<UiModel> modelList = new ArrayList<>();
        List<Person> allpersons = getAllPersons();
        for (Person p : allpersons) {
            List<Tag> associatedTags = getAllTagsForPerson(p.getPresonID());
            modelList.add(new UiModel(p, associatedTags));
        }
        return modelList;
    }

UiModel.class

UiModel.class

public class UiModel {
    private Person person= new Person(System.currentTimeMillis(),"");
    private List<Tag> tagList = new ArrayList<>();

    public UiModel() {
    }
}

图片1:getAllAsModelList()
的结果 图片2:通过JS时getFilteredModelList()的结果

Picture 1 : the results of getAllAsModelList()
picture 2 : the results of getFilteredModelList() when JS is passed

更新:
@MikeT提供的测试套件非常好，但是事实证明，数据库和查询功能是完全正确的.有关更多详细信息，请参见下面的答案.

Update:
The test suite provided by @MikeT is very good, but as it turns out the database and the query functions are totally correct. See my answer below for more details.

推荐答案

事实证明，这是我愚蠢的ui的过错.每次添加标签时，即使它已经存在，我都会生成一个新的ID .因此，如果我的数据库具有以下条目(从它的外观开始):

As it turns out , it was my silly ui at fault. I was generating a new id every time i add a tag, even if its already present. thus if my DB has the following entries(from what it looks like):

Person --- Tags
A      --- ai ,ml
B      --- ml
C      --- flutter

实际上就是这个:

id---person     id---tag        id_person-----------id_tag
101 -  A        11 - ai             101      --       11 
102 -  B        12 - ml             101      --       12
103 -  C        13 - ml //! wrong   102      --       13 //wrong!!  
                14 - flutter        103      --       14

我的ui在将其添加为新标签之前没有检查天气是否存在标签.这就是为什么getFilteredModelList()函数无法生成包含多个条目的列表的原因.所以我最终为此写了一个额外的检查代码.另外，该dao最终进行了以下更改(对所有三个内部插入函数进行了额外的onConflict检查):

My ui was not checking weather a tag existed before adding it as new tag. That's why the getFilteredModelList() function was not able to generate a list with more than 1 entry. so i ended up writing an extra checker code for that. Also, the dao ended up with the following mini change( an extra onConflict check for all three internal insertion functions):

@Dao
public abstract class PersonDao {
    private static final String TAG = "personDao>>";

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract void insertNewPerson(Person... p); 

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract void insertNewTag(Tag... t);          

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract void insertNewRelation(PersonTagRelation... relation);  

    ...
    }

这篇关于Room db具有多对多关系:在查询过滤后的数据时返回单个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！