How to represent nested one to many relationship in android room?

Question

Here are sample POJO:

@Entity
public class User {
  @PrimaryKey
  public String id;
}

@Entity
public class Pet {
  @PrimaryKey
  public String id;
  public String userId;
  public String petName;
}

@Entity
public class Category {
  @PrimaryKey
  public String id;
  public String petId;
  public String categoryName;
}

Here is my UsersWithPets class to fetch all the users along with pets.

public class UserWithPets {
  @Embedded
  public User user;
  @Relation(parentColumn = "id", entityColumn = "userId", entity =   Pet.class)
  public List<Pet> pets;
}

My user Dao

@Dao
public interface UserDao {
  @Query("SELECT * FROM User")
  List<UserWithPets> loadUsersWithPets();
}

and userdb

@Database(entities = {User.class, Pet.class, Category.class}, version = 1)
@TypeConverters(DateConverter.class)
public abstract class UsersDb extends RoomDatabase {

  private static UsersDb INSTANCE;

  public abstract UserDao userDao();

  public static UsersDb getInstance(Context context) {
      if (INSTANCE == null) {
          synchronized (UsersDb.class) {
              if (INSTANCE == null) {
                  INSTANCE = Room.databaseBuilder(context.getApplicationContext(), UsersDb.class,
                          "User.db")
                          .build();
              }
          }
      }
      return INSTANCE;
  }
}

The "UserWithPets" embeds user object and relates with its associated list of "Pet", but how do I fetch list of "Category" if my "Pet" POJO can have 1-many relationship with "Category".

Also the user DAO only returns all the "User" along with its "List<Pet>" by mapping the id between user.id and pet.userId, if my "Pet" POJO can have many "Category"(List of Category), how do I create my DAO and abstract database class such that querying for a particular "User" id will return me the "User" object along with its "List of Pets" and individual "Pet" containing a "List of Category" and querying for all will return all the "List of Users" wherein each User contains a "List of Pet" and each Pet contains a "List of Category"

Answer 1

You need to map your query to a POJO/Java Bean class like PetWithCategories:

public class UserWithPets {
  @Embedded
  public User user;
  @Relation(parentColumn = "id", entityColumn = "userId", entity = Pet.class)
  public List<PetWithCategories> pets;
}

public class PetWithCategories {
  @Embedded
  public Pet pet;
  @Relation(parentColumn = "id", entityColumn = "petId")
  public List<Category> categories;
}