Skip to content
Advertisement

Androd Room Embedded Relation ignores where condition

I am an SQL rookie and don’t see why my statement behaves the way it does. It is used in an Android Room DAO and returns unwanted results.

I have this sample data in two tables: enter image description here

I have this statement:

the results are received by a simple POJO:

Problem: I dont understand the results:

enter image description here

Why is Item 5 returned? It should be excluded by the where clause. Is there an error in my statement?

MikeT built a sample app that contains the same bug: enter image description here

(Edit: I dropped the bool converter by now, Room seems to parse boolean without it) Or could this be caused by my dead simple type converter for booleans? The ones and zeros are actually placeholders for booleans:

Thank you for reading!

EDIT: Many thanks to @Angela. Her test suggests that the sql statement is fine and that behaviour is caused by the peculiarities of sqlite, or by the embedded relation in the receiver pojo. Does anyone have advice on this?

EDIT: MikeT explains why what I want to do is seemingly not possible with room relations – at least as long no one prooves different. In his answer, he provides an alternative.

Advertisement

Answer

I believe that it’s either due to your dead simple converter or perhaps how the Items Entity is defined (I’ve used boolean for isexcluded and it appears to work fine without the converter).

That is using my version of the Items Entity :-

along with a Category DAO of :-

along with

and with :-

Then the result is (part one the underlying data (oh so I can’t spell third :))):-

and then the two sets of results :-

Additional

I believe that your issue is not with the query but with the CatViewItemList in that you are filling the list of Items with all Items related to the category irrespective/after of the query (aka I think, embedding the list of items into the category). This did confuse me as you only really want the specific related Item (I just grabbed 1st in my code ) e.g. cvwil.ItemList.get(0).isIsexcluded() and luck was that 4 comes before 5 so the isexcluded appears as false.

I believe that you should perhaps approach this from the Items, joining the Category e.g. something like

I’be updated the code on gitHub to do this (note that I’ve changed the Category table to have a unique column name of categoryid rather than id.)

results are now (note added another Category and 2 more items reversed order i.e. first Item (id 7) has isexcluded as true, then id 8 has false) :-

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement