Please bear with me, i'm new to architecture components and android in general. My question is similar to this question but unfortunately the accepted answer doesn't seem to work. I have an example one to many relation like in this answer. My example database has two tables USERS and PETS as shown in the following images:
Let's say I want to get a list of users containing a list of their pets grouped by user id only with pets younger than 5. The result should look like this (pseudo code):
{uId: 2, [Pet3, Pet4]; uId: 4, [Pet6, Pet7];}
Another requirement is that the Dao needs to return the list as a LiveData object because I'm using MVVM architecture and want it to be Lifecycle aware and observable.
With these requirements, the UserDao would look like this:
@Dao
interface UserDao {
@Insert
void insert(User user);
@Transaction
@Query("SELECT USERS.uId, PETS.pId , PETS.userId, PETS.age " +
"FROM USERS INNER JOIN PETS ON PETS.userId = USERS.uId " +
"WHERE PETS.age < 5 " +
"GROUP BY USERS.uId")
LiveData<List<UserWithPets>> getUserPets();
}
User Entity:
@Entity
public class User {
@PrimaryKey
public int id; // User id
}
Pet Entity:
@Entity
public class Pet {
@PrimaryKey
public int id; // Pet id
public int userId; // User id
public int age;
}
The problem is now: how should i design the UserWithPets that room understands it and maps the cursor the way i want? Here is what i tried so far:
Approach 1:
The most convenient way in my opinion would be using a Relation, like in the POJO below.
UserWithPets POJO:
public class UserWithPets {
@Embedded
public User user;
@Relation(parentColumn = "id", entityColumn = "userId", entity = Pet.class)
public List<Pet> pets;
}
Unfortunately, the functionality to assign a condition to a relation is not yet implemented by google. So we always get a full list of pets for every user that owns a pet younger than 5. Hopefully this will be possible soon, since the feature request is already assigned here and here.
Statement from google from this feature request: "we are planning to implement some query rewriting logic to fix these, not for 2.1 but hopefully in 2.2 where we'll focus more on relations."
Approach 2:
Another option would be Embedding both, User and Pet like:
public class UserWithPets {
@Embedded
public User user;
@Embedded
public Pet pet;
}
This doesn't work either, because now we only get 1 pet per user.
Approach 3:
this answer suggests to just create a merged class that extends from user like:
public class UserWithPets extends User {
@Embedded(prefix = "PETS_")
List<Pet> pets = new ArrayList<>();
}
I tried in many ways, with contructor and without, but i can't get it to work. it always gives errors like "Entities and Pojos must have a usable public constructor. You can have an empty constructor or a constructor whose parameters match the fields (by name and type). - java.util.List" or The query returns some columns ... which are not used by UserWithPets. So any advice is welcome here.
Approach 4:
Just make two queries and stitch the results together. How would i do that using LiveData? Where should the joining operation be done? I can't do it in the Activity, that's not the point of an MVVM pattern. And not in the repository or viewmodel, since LiveData is immutable. Or is there another way?
What would be a working solution to get a result with the above requirements?
