I have set up tables and entries with room DB. And have trouble properly returning DB Objects according to their relation ships.
To understand it better. This is all part for a location app. For roomDB I have different Tables with one to Many relationships.
I have a table called "Area" (Like for example "New York","Los Angeles" etc.). I also have tables "Building", "Floor" and "User".
There is a one to many relationship between "Area" (parent) and "Building". There is also a one to many Relationship between "Building" (parent) and "Floor". And there are two more relationships. A one to Many Relationship between "Area"(parent) and "User", and a one to many Relationship between "Floor"(parent) and "User".
A User can be part of an Area and a Floor (The floor being part of a Building in an area). A User can also be part of only an Area but not part of a Floor. But a User can also be part of neither.
Its easy for me to get all Users that are not part of an area. I can also easily get All areas with all Buildings. Due to my Class "UsersWithBuildingsFloorsAndUsers". I can also easily get ALL Users of an Area wheter they are part of a Floor/Building combinaton or not. However I can not get All users from an Area that are not part of a Building/Floor because it will duplicate my entries.
I tried it with transaction and JOINS and I also tried it with Maps.
My issue is. That I have for example "Area1" and "Area2". Area1 has 3 users that are not in a building and Area2 has 2 Users.
I will get 5 Lists/maps returned with the statment "SELECT * FROM areas INNER JOIN users ON users.areaFK = areas.areaID WHERE users.floorFK = 0"
How can I properly get a soltion? Where I get Only two lists. Like a list of "AreasWithUsers" with only a List of Users that has a floorFk that is 0 ?
