Android Persistence room: "Cannot figure out how to read this field from a cursor"

Question

I'm trying to create a relation between two database tables using the new Android Persistence Room Library. I looked at the documentation and tried to implement the example found at https://developer.android.com/reference/android/arch/persistence/room/Relation.html:

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

 @Entity
 public class Pet {
     @PrimaryKey
     int id;
     int userId;
     String name;

 }

 @Dao
 public interface UserDao {
     @Query("SELECT * from User")
     public List<User> loadUser();
 }

 @Dao
 public interface PetDao {
     @Query("SELECT * from Pet")
     public List<Pet> loadUserAndPets();
 }


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

 @Dao
 public interface UserPetDao {
     @Query("SELECT * from User")
     public List<UserAllPets> loadUserAndPets();
 }

I get the following error

    ...error: Cannot figure out how to read this field from a cursor.

in relation to:

 private java.util.List<?> pets;

I would like to point out that I found some things in their docs really confusing. For example the lack of @PrimaryKey and also the fact that the User class is missing the @Entity annotation, although it's supposed to be an entity (as fas as I see it). Did anybody run into the same problem? Thanks a lot in advance

Solution

Document is really confusing. Try with just below classes:

1) User Entity:

@Entity
public class User {
    @PrimaryKey
    public int id; // User id
}

2) Pet Entity:

@Entity
public class Pet {
    @PrimaryKey
    public int id;     // Pet id
    public int userId; // User id
    public String name;
}

3) UserWithPets POJO:

// Note: No annotation required at this class definition.
public class UserWithPets {
   @Embedded
   public User user;

   @Relation(parentColumn = "id", entityColumn = "userId", entity = Pet.class)
   public List<Pet> pets; // or use simply 'List pets;'


   /* Alternatively you can use projection to fetch a specific column (i.e. only name of the pets) from related Pet table. You can uncomment and try below;

   @Relation(parentColumn = "id", entityColumn = "userId", entity = Pet.class, projection = "name")
   public List<String> pets; 
   */
}

• parentColumn refers to Embedded User table's id column,
• entityColumn refers to Pet table's userId (User - Pet relation) column,
• entity refers to table(Pet) which has relation with User table.

4) UserDao Dao:

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

Now try loadUsersWithPets(), which returns the users with their list of pets.

Edit: See my other answer for many ot many relation.

Comments

[Yoda066]

Hello. Lets assume pets can have multiple owners. (classic m:n relation) In that case, I would like to have mapping table containing id of pet and id of owner in each row. I was under the impression that Room handles this automatically from defined @Relations. But it seems now that Relations tags are used only for joins and selections. So to accomplish this mapping I would need to create another Entity class right?

[Devrim]

For many to many relation, you need another Entity/Table, such as UserAndPet, which holds user and pet relation. I've updated my answer for many to many relation.

[Andrea Soro]

@DevrimTuncer thanks man, it worked. I guess my mistake was that I made an extra DAO for UserWithPets. Have a good day

[Andrea Soro]

@DevrimTuncer I tried to rewrite the same example in Kolin and I get: e: error: Cannot figure out how to read this field from a cursor. e: private java.util.List<Pet> pets;

[Devrim]

@AndreaSoro could you post it as a new question and share your code?

[Andrea Soro]

@DevrimTuncer sure

[Andrea Soro]

@DevrimTuncer stackoverflow.com/questions/44522799/…

[Akshay Chordiya]

@DevrimTuncer @AndreaSoro how do we insert UserWithPet? The docs say Room requires insert to be either Entity or collection/array of it so we can't directly insert UserWithPet.

[Titto Jose]

@DevrimTuncer I'am getting the compile time error "Error: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)." when there is List type variable in c UserWithPets class.

[user2234]

@DevrimTuncer Thanks Your answer is much clear than the one on android Developers link. I do have one question, say there is one - one relationship between two classes and others are one to many, how we would handle this ? I understand that for one to many we will have similar to your solution, but say the Pet has only one Doctor, then how would the PetWithDoctor class look like ? As the Relation should always be a list, but here it would be a single object Doctor?

[Devrim]

@Sneha I think, you don’t need a PetWithDoctor pojo for one to many relation. You can add doctorId to your Pet class which refers to a single doctor for a pet. You don’t need any pet reference at Doctor class since you want one doctor refers many pets.

[romaneso]

@AkshayChordiya I have come across the same error- BUT: if you use LiveData to update your UI, you just have to write two different DAO's (so in that case for Pet and User), then just simply insert the pet in the pet-dao, insert the user in the user-dao, and then get the PetWithUser- Entities from the User- Dao. The Pets will show up too.

[Diskdrive]

So you need a UserWithPets style class basically for every single relationship? I understand lazy loading is dangerous on a mobile device but isn't this overkill? They couldn't just put a @NoLazyLoading attribute, kind of like Entity Framework not lazy loading by default if your method property isn't virtual?

[shurrok]

Great answer! Although one thing bothers me. Why not put the relation field into the User Entity istself? I mean, why do we need that separate pojo class with embedded entity?

[ban-geoengineering]

In step 4, how does the SELECT * FROM User query allow you to return List<UserWithPets> (rather than just List<User>)? Is it because, in step 3, we embedded User within UserWithPets?